Wie nennt man die Menschen, die montags gut gelaunt sind? – Rentner.

Hallo Rene,

Eine Kollegin hat mir heute eine Frage gestellt zur Zählen – Funktion.

=VERKETTEN("Anzahl FOS: ";
ZÄHLENWENN(AF3:AF54;"*(FOS)*");
ZEICHEN(10);
"Anzahl Schüler: ";
ZÄHLENWENN(AF3:AF54;"*(SP)*"))

Das Zählen Wenn zählt nur wenn in der Zelle entweder (FOS) steht oder (SP).

Jede Zelle wird dabei nur einmal gezählt.

Kennst Du eine Funktion, dass wenn in der Zelle mehrmals (FOS) steht die Zelle auch mehrfach gezählt wird.

####

Hallo Christian,

wenn du aus der Liste die „FOS“ (oder: (FOS)) entfernst, dann ist die Anzahl der Zeichen vorher – Anzahl der Zeichen, die nach Entfernen übrigbleiben geteilt durch 3 (beziehungsweise geteilt durch 5) die Anzahl der Vorkommnisse der Texte FOS:

=(LÄNGE(TEXTKETTE(AF3:AF53))-LÄNGE(WECHSELN(TEXTKETTE(AF3:AF53);"FOS";"")))/3

Die letzte Formel sieht so aus:

=VERKETTEN("Anzahl FOS: ";
(LÄNGE(TEXTKETTE(AF3:AF53))-LÄNGE(WECHSELN(TEXTKETTE(AF3:AF53);"FOS";"")))/3;
ZEICHEN(10);
"Anzahl SP: ";
(LÄNGE(TEXTKETTE(AF3:AF53))-LÄNGE(WECHSELN(TEXTKETTE(AF3:AF53);"SP";"")))/2)

Ich überlege, ob ich

TEXTKETTE(AF3:AF53)

mit der Funktion LET in eine Variable auslagere. Aber dann wird die Funktion wohl weniger verständlich …

####

Hallo Rene,

… Du bist ja krass mit der Lösung – darauf muss man erstmal kommen- mit einer Rechenoperation…Hat super funktioniert… Vielen Dank

####

Hi Christian,

Die Lösung ist „uralt“: Wie oft ist ein Text in einem anderen Text vorhanden?

Man vergleiche die Länge der Buchstaben mit der Länge der Buchstaben nach Löschen des Textes.

Oder: in der Firma in Regensburg wollen Sie wissen, ob in einer Zeichenkette nur A, T, C und G enthalten sind (wir erinnern und: DNA).

Also: ATTTCGCGATCTAGCTGCTGAT …

Könnte ja sein, dass ein anderer Buchstabe in den Tausend Zeichen drin ist. Also löscht man A, T, C und G und schaut, was übrig bleibt …

Wenn du auch auf jemenden wartest, der sdich auf Händen trägt – gib nicht auf! Deine Beerdigung kommt sicher.

Frage in der Excelschulung: in drei Spalten stehen verteilt Titel | Vorname | Nachname.

Sie sollen verkettet werden.

Wendet man die Blitzvorschau an und schreibt in die erste Zelle Vorname und Nachname, dann fehlt der Titel:

Hat die erste Person einen Titel und trägt man alle drei Informationen ein, wird ein Leerzeichen bei den Personen ohne Titel eingefügt:

Die Blitzvorschau führt hier zu keinem brauchbaren Weg.

Die Teilnehmerin, die die Frage gestellt hatte, war sehr traurig.

Die Lösung für das Problem heißt beispielsweise: die Funktion „TEXTVERKETTEN“.

=TEXTVERKETTEN(" ";WAHR;C2:E2)

Wichtig ist der Wert WAHR beim Parameter Leer_ignorieren. So wird bei Personen, die keinen Titel haben, die leere Zelle nicht mit einem Leerzeichen verkettet, sondern übergangen.

Die Teilnehmerin war glücklich.

Ist es normal, dass mein Handy im Flugzeugmodus nicht weiter als 25 Meter fliegt?

Sehr geehrter Herr Martin,
Heute habe ich folgende Frage.
Wenn ich Wörter miteinander verketten möchte, habe ich bisher die Funktion Verketten genommen, wo ich auch zwischen 2 Wörtern ein Komma setzen konnte:

So weit, so gut.
Nehme ich aber die Funktion Textverketten, funktioniert es nicht mit dem Komma:

Frage: Was müßte ich tun bei der letzten Funktion, um z. B. zwischen Nachname und Ort ein Komma zu setzen, dem ein Leerzeichen folgt?

####

Hallo Herr F.,

nein, mit TEXTVERKETTEN können Sie nicht mit unterschiedlichen Trennzeichen verketten. Der Gedanke ist, dass TEXTVERKETTEN mit Bereichen arbeitet:

=TEXTVERKETTEN(„; „;WAHR;A2:H2)

Wenn Sie einzeln verbinden möchten, müssen Sie auf das «&» zurückgreifen oder die alte VERKETTEN-Funktion verwenden.

Ich benutze TEXTVERKETTEN gerne, wenn ich eine Reihe an Mailadressen habe – dann verbinde ich sie zu einer Zeile:

=TEXTVERKETTEN(„;“;WAHR;A1:A555)

und kann sie so über Outlook in das An-Feld eintragen. DAS würde mit VERKETTEN nicht funktionieren.

Liebe Grüße

Rene Martin

Verkaufe gebrauchte facebook-Freunde. Sie liken nicht, und haben noch nie kommentiert, aber sie wissen alles über mich.

Letzte Woche in der PowerBI-Schulung. Wir schauen uns einige der DAX-Funktionen an. Beispielsweise CONCATENATE.

Es folgt die Frage, warum man mit der Excel-Funktion VERKETTEN ( CONCATENATE) mehrere Texte verketten kann:

Dagegen in DAX:

Zwei Texte sind kein Problem. Jedoch bei drei Texten:

=CONCATENATE(tbl_ottos_mops[ottos];" ";tbl_ottos_mops[mops])

Also verketten wir in DAX mehrere Texte mit dem et-Zeichen „&“. Das funktioniert!

=tbl_ottos_mops[ottos]&" "&tbl_ottos_mops[mops]&" "&tbl_ottos_mops[Spalte1]&" "&tbl_ottos_mops[Spalte2]

Wurde der Fernseher repariert? Schneidet der Gärtner die Hecke zu Ende? Gibt es Trinkgeld für die Pizza? – Pornos lassen immer so viele Fragen offen …

Hallo, i ch benötige kurze Hilfe, ich habe hier ein Makro, noch von meinem Vorgänger, das wohl in einer Excel Liste eine Zeile entfernt und wo anders
dann wieder einsetzen kann. Nun ist es aber so, das ich Zeilen habe, die einen Zeilenumbruch in der Zelle haben, und dann immer eine Fehlermeldung 1004 kommt. Können Sie mir hier BITTE helfen????
Danke
Jörg

Hallo Jörg,
so groß und schwierig kann das ja wohl nicht sein – wenn das Makro vorher gelaufen ist …
schick es mir doch mal, bitte, dann schreibe ich dir etwas dazu.
Liebe Grüße Rene

Hallo Rene,

hier mal so….

In einer Excel Zelle habe ich einen Textumbruch, mit sehr viel Text.

Den kann ich zwar über den Makro „löschen“, aber dann nciht

mehr an einer anderen Stelle einfügen…..

BITTE Um HILFE! DANKE

Sub FahrtAuswaehlenEinfach()
Dim aDlg As DialogSheet
     Dim aRow As Range
     Set aDlg = Sheets("Fahrtauswahl")
     
     i = 1
     aDlg.ListBoxes("Fahrten").RemoveAllItems
     Do While Sheets("Tabelle2").Cells(i, 2).Value <> ""
         astring = Format$(Sheets("Tabelle2").Cells(i, 2).Value, "short time")
         astring = astring + " - " + Format$(Sheets("Tabelle2").Cells(i, 3).Value, "short time")
         astring = astring + "  " + Sheets("Tabelle2").Cells(i, 4).Value
         astring = astring + " --- " + Sheets("Tabelle2").Cells(i, 8).Value
         astring = astring + " / " + Sheets("Tabelle2").Cells(i, 11).Value
         aDlg.ListBoxes("Fahrten").AddItem astring
         i = i + 1
     Loop
     
     If aDlg.Show = True Then
         i = aDlg.ListBoxes("Fahrten").ListIndex
         Sheets("Tabelle2").Rows(i).EntireRow.Cut
         Sheets("Linienpläne - quer").Select
         ActiveCell.EntireRow.Insert Shift:=xlDown
         Sheets("Tabelle2").Rows(i).EntireRow.Delete xlUp
     End If 
End Sub

    Zuerst musst ich schlucken und mich auf die Suche machen. Was bitte ist ein DialogSheet? Ich werde fündig: in Excel 4.0 konnte man ein Dialogblatt einfügen. Und darauf Steuerelemente platzieren. Und die verwenden so etwas immer noch im Jahre 2020?

Egal. Meine Vermutung liegt beim Operator „+“. Wenn in den Zellen Zahlen stehen, hat VBA ein Problem beim Verketten von

astring = astring + "  " + Sheets("Tabelle2").Cells(i, 4).Value

Das ist aber nicht die Lösung. Ich bitte um die Daten. Der Zeilenumbruch ist nicht das Problem, sondern:

Der Text ist zu lang! Excel lässt mehr als 8.000 Zeichen / Zelle zu, die ListBox leider nur 255

Also schnell den Code geändert:

Sub FahrtAuswaehlen()

    Dim aDlg As DialogSheet
    Dim aRow As Range
    Set aDlg = Sheets("Fahrtauswahl")
    
    i = 1
    aDlg.ListBoxes("Fahrten").RemoveAllItems
    Do While Sheets("Tabelle2").Cells(i, 2).Value <> ""
        astring = Format$(Sheets("Tabelle2").Cells(i, 2).Value, "short time")
        astring = astring & " - " & Format$(Sheets("Tabelle2").Cells(i, 3).Value, "short time")
        astring = astring & "  " & Left(Sheets("Tabelle2").Cells(i, 4).Value, 200)
        astring = astring & " --- " & Sheets("Tabelle2").Cells(i, 11).Value
        aDlg.ListBoxes("Fahrten").AddItem astring
        i = i + 1
    Loop
    
    If aDlg.Show = True Then
        For i = aDlg.ListBoxes("Fahrten").ListCount To 1 Step -1
            If aDlg.ListBoxes("Fahrten").Selected(i) Then
                Sheets("Tabelle2").Rows(i).EntireRow.Cut
                Sheets("Linienpläne - quer").Select
                ActiveCell.EntireRow.Insert Shift:=xlDown
                Sheets("Tabelle2").Rows(i).EntireRow.Delete xlUp
            End If
        Next
    End If
    
End Sub

Die Antwort:

Hallo Rene,

JA, das wars wohl! Klappt SUPER!!!

TAusend DANK!!!

Wie kann ich mich revangieren?

D A N K E ! ! !

Gruß

Jörg

Sommer kommt – fuck the Pullis!

Das Telefon klingelt.
„Hallo René, ich bin’s: Angelika. Hast du mal nen Moment Zeit?“

Kennt ihr Gespräche, die so beginnen? „Hast du mal einen Moment Zeit?“ Das dauert normalerweise sehr, sehr lange. Egal – ich nehme mir die Zeit für Angelika.

„Jep, schieß los – was gibt’s?“
„Ach mein Excel nervt wieder! Ich bereite gerade eine Schulung vor. Excel zeigt mir immer nur die Formeln an, nicht die Ergebnisse. Ich bin völlig verzweifelt; ich weiß nicht, was ich machen soll!“
„Hast du die Formel-Anzeige eingeschaltet? – Formeln / Formeln anzeigen.“
„Ne, da habe ich schon geschaut.“
„Wie sind denn die Zellen formatiert? Wie lautet das Zahlenformat“
„Na – als Standard sind sie formatiert!“
„Mach mal einen Doppelklick auf die Zelle mit der Formel! Und dann [Enter]“
„Ach – jetzt geht es! Was war das?“
„Ich frag mal zurück: was hast du gemacht?“

„Also von vorne. Ich bereite gerade die Excelschulung für nächste Woche vor. Ich habe eine Liste mit Telefonnummern:

Ich füge Spalten ein und zeigen den Teilnehmern den Assistenten Daten / Text in Spalten.

Dann will ich die Teile wieder zusammenbauen. Und: egal, was ich verwendet habe: das &-Zeichen, VERKETTEN, TEXTKETTE, TEXTVERKETTEN … immer die Formel statt das Ergebnis!“

„Klar! Du hast die Telefonnummern als Text formatiert.“ Durch das Spalten Einfügen übernimmst du das Textformat. Und deshalb rechnet die Formel nicht mehr, sondern wird als Text eingefügt.“

„René – danke! Ich wusste doch, dass Excel nervt. Und dass du mir helfen kannst!“

Hatte neulich Stress. Sagte cool: „Fass mich nicht an – ich kann Kibotu“. Muss ja keiner wissen, dass das Kinderbodenturnen heißt.

Hallo Rene, ich mache zurzeit Ihre Ecel Schulung Tipps & Tricks, bis jetzt bin ich begeistert, vielen Dank. Eventuell können Sie mir mit einer Formel helfen? Ich habe zwei Tabellenblätter und möchte Emailadresse, die in Blatt 1 in Spalten aufgelistet sind, zu Blatt 2 einen automatischen Bezu herstellen, sodass dort in einer Zelle die EMailadressen automatisch erscheinen, wenn ich sie in Blatt 1 eingetippt habe. Ich danke bereits für Ihre Hilfe.

Hallo Anna, wenn sich der Cursor auf der Zelle befindet, wo die Email-Adresse stehen soll, genügt ein „=“, dann ein Klick auf das erste Tabellenblatt und ein zweiter Klick auf die Zelle, in der sich die Mailadresse befindet. Achtung: Nicht zurückklicken! Sondern mit [Enter] bestätigen! Dann steht in der Zielzelle beispielsweise =Tabelle1!C3 Hilft das? Oder denke ich zu einfach? schöne Grüße Rene Martin

Lieber Rene,    danke für die supr schnelle Antwort.  Es ist leider doch ein wenig komplizierter, aber ich denk für Sie ebenfalls problemlos zu lösen. Ich füge ein Beispieldokument ein zum besseren Verständnis.  Aus Tabelle1 möchte ich automatische alle Email-adressen von Runde 1 in aufgelistet in ein Zelle im Tabellenblatt ‚Mailverteiler‘ auflisten.  Wie lautet die Formel des Zellbezugs?   Besten Dank für Ihre Hilfe.

Hallo Anna, hast du eine neueres Excel? Und dort die Funktion TEXTVERKETTEN? Damit geht es LG  ::  Rene

Schokolade erweitert dein Speckdrum.

Heute in der Excelschulung. Ich zeige die Datenüberprüfung. Eine Artikelliste. In die Mengenspalte darf nur eine ganze Zahl eingetragen werden. Ein Teilnehmer fragt, ob man die Eingabe auch so begrenzen kann, dass die Gesamtmenge < 100 sein muss. Klar – kein Problem: Benutzerdefiniert – =SUMME(D:D)<100

Klappt.

Und dann möchte der Teilnehmer, dass die Eingabe nur ganze Zahlen akzeptiert. Leider kann man keine zwei Datenüberprüfungen miteinander verketten.

Man müsste die Formel erweitern, beispielsweise so:

=UND(RUNDEN(D1;0)=D1;SUMME(D:D)<100)

Was denn Excel zusammengefügt hat, soll ein Mensch nicht scheiden.

Hallo. Ich brauche einen Tipp. Ich erhalten einige Male im Jahr eine Tabelle mit vielen Zahlen. Da die Zeilen keine eindeutige ID haben, habe ich mit der Funktion VERKETTEN mehrere Felder konkateniert (zusammengefasst), so dass ich eine ziemlich eindeutige ID habe. Ich wollte diese Formel weiter runterziehen, weil sich die Liste ab und zu erweitert. Jedoch – Excel zeigt in den leeren Zeilen Fehler. Das verstehe ich nicht: leer & leer & leer müsste doch leer sein, oder?

VERKETTEN - klappt gut, aber irgendwann mit Fehler

VERKETTEN – klappt gut, aber irgendwann mit Fehler

Die Antwort: Sie haben die Tabelle in eine „intelligente Tabelle“ verwandelt (Einfügen / Tabellen / Tabelle). Die Formel in der Zelle lautet:

=VERKETTEN(Tabelle1[@ProdH];Tabelle1[@[BG Cd]];Tabelle1[@[BD / BU]];Tabelle1[@[Main Hier. Name]];Tabelle1[@[Sold-to party]];Tabelle1[@[MAT15 Nbr]];Tabelle1[@[DQ Conf]])

also: Tabellenname Tabelle1[Spaltenname]. Das impliziert: gleiche Zeile wie Formelzelle.

Allerdings befindet sich keine Tabelle neben den neuen Zellen unterhalb der Tabelle. Wenn es Sie stört, können Sie um die Funktion die Formel WENNFEHLER bauen, also:

=WENNFEHLER(VERKETTEN(Tabelle1[@ProdH];Tabelle1[@[BG Cd]];Tabelle1[@[BD / BU]];Tabelle1[@[Main Hier. Name]];Tabelle1[@[Sold-to party]];Tabelle1[@[MAT15 Nbr]];Tabelle1[@[DQ Conf]]);““)

Oder Sie schreiben den Zellbezug „per Hand“ in die Formel:

=VERKETTEN(B1;C1;D1;G1;J1;L1;T1)

Dann erhalten Sie auch keine Fehler.

Einsam bist du klein aber gemeinsam …

Geht das nicht?

Ich habe eine Liste. darin befinden sich in einer Spalte Vornamen, in einer anderen Nachnamen. Das Verketten mit

=D2&E2

klappt hervorragend. Aber – darf ich denn kein Leereichen zwischen Vor- und Nachname schreiben. Ohne – das wäre schon ganz schön doof.

Verketten

Verketten

Die Antwort: Natürlich geht es. Aber Sie dürfen nicht das Leerzeichen direkt eingeben, sondern müssen es als Leerzeichen kennzeichnen. Also so

=D2&“ „&E2

Dann funktioniert es. Und: Vergessen Sie nicht das zweite Verkettungszeichen „&“!

Übrigens: Ich finde es erstaunlich, dass Excel das Leerzeichen akzeptiert – eigentlich sind Leerzeichen in Formeln verboten und werden mit einer Fehlermeldung quittiert!