Wenn Sie jemals zehn Minuten damit verbracht haben, Zellen manuell zu kombinieren oder Text in Excel auseinanderzuschneiden, werden Sie schätzen, was TEXTJOIN, TEXTBEFORE und TEXTAFTER für Sie tun können. Diese drei Excel-Formeln stellen Trennzeichen in den Mittelpunkt und ermöglichen es Ihnen, Text mit weit weniger Aufwand als mit den alten Methoden zu verbinden, aufzuteilen und zu extrahieren.
Was ist ein Trennzeichen in diesem Zusammenhang?
Ein Trennzeichen (Delimiter) ist einfach ein Zeichen (oder eine Zeichenfolge), das eine Grenze zwischen Datenstücken markiert. In Excel-Formeln geben Sie ein Trennzeichen an, um der Funktion mitzuteilen, wo geschnitten oder wo verbunden werden soll. Gängige Beispiele sind Kommas, Leerzeichen, Pipes, Semikolons und Bindestriche.
Sobald Sie dieses Konzept verstanden haben, werden diese drei Funktionen deutlich einfacher nachvollziehbar.
TEXTJOIN: Text mit einem Trennzeichen kombinieren
TEXTJOIN fügt mehrere Textwerte zu einer Zeichenfolge zusammen und platziert Ihr gewähltes Trennzeichen zwischen jedem Element. Die Syntax ist unkompliziert:
- Wählen Sie Ihr Trennzeichen (zum Beispiel ein Komma und Leerzeichen).
- Setzen Sie Leere_ignorieren auf WAHR oder FALSCH, um leere Zellen zu überspringen oder einzuschließen.
- Wählen Sie den Bereich oder die einzelnen Zellen, die Sie kombinieren möchten.
Ein einfaches Beispiel: =TEXTJOIN(", ", TRUE, A2:A6) verbindet alle Werte in A2 bis A6 mit einem Komma und Leerzeichen dazwischen. Wenn A4 leer ist und Sie Leere_ignorieren auf WAHR gesetzt haben, entsteht kein doppeltes Komma in der Ausgabe.
Tipp: TEXTJOIN hat die alten CONCATENATE-Workarounds ersetzt. Wenn Sie immer noch manuell Zellen mit &-Operatoren aneinanderreihen, wechseln Sie zu TEXTJOIN und ersparen Sie sich die Frustration.
TEXTBEFORE: Text vor einem Trennzeichen extrahieren
TEXTBEFORE erfasst alles links von einem angegebenen Trennzeichen. Es ist perfekt, wenn Sie strukturierten Text haben, wie vollständige Namen oder Dateipfade, und nur das erste Segment benötigen.
Syntax: =TEXTBEFORE(Text, Trennzeichen, [Instanznr]). Das optionale Argument Instanznr ermöglicht es Ihnen, ein bestimmtes Vorkommen des Trennzeichens anzusprechen, was nützlich ist, wenn es mehrere Trennzeichen in einer Zelle gibt.
Zum Beispiel: Wenn A1 "London, UK, Europe" enthält, gibt =TEXTBEFORE(A1, ",") "London" zurück. Fügen Sie Instanznr als 2 hinzu und Sie erhalten stattdessen "London, UK".
TEXTAFTER: Text nach einem Trennzeichen extrahieren
TEXTAFTER funktioniert auf die gleiche Weise, zieht aber alles rechts vom Trennzeichen heraus. Mit dem gleichen Beispiel gibt =TEXTAFTER(A1, ", ") "UK, Europe" zurück. Es ist das natürliche Gegenstück zu TEXTBEFORE.
Sie können auch negative Instanznummern in beiden Funktionen verwenden, um vom Ende der Zeichenfolge statt vom Anfang zu zählen. Das ist ein praktischer Trick, wenn Sie das letzte Segment eines Pfades oder einer ID benötigen.
Schneller Vergleich
| Funktion | Was sie tut | Beispielergebnis |
|---|---|---|
| TEXTJOIN | Verbindet mehrere Werte mit einem Trennzeichen | "Katze, Hund, Fisch" |
| TEXTBEFORE | Gibt Text vor einem Trennzeichen zurück | "London" aus "London, UK" |
| TEXTAFTER | Gibt Text nach einem Trennzeichen zurück | "UK" aus "London, UK" |
Ein praktisches Workflow-Beispiel
Angenommen, Sie haben eine Spalte mit E-Mail-Adressen und müssen den Benutzernamen von der Domain trennen. TEXTBEFORE und TEXTAFTER erledigen das mit einer einzigen Formel. =TEXTBEFORE(A2, "@") liefert Ihnen den Benutzernamen. =TEXTAFTER(A2, "@") liefert Ihnen die Domain.
Wenn Sie dann eine bereinigte Liste von Domains in einer einzigen Zelle zusammenfassen möchten, übernimmt TEXTJOIN diese Aufgabe. Diese Funktionen arbeiten natürlich zusammen als Teil desselben Datenbereinigungs-Prozesses.
Achtung: TEXTBEFORE und TEXTAFTER sind nur in Excel 365 und Excel 2021 oder neuer verfügbar. Wenn Sie eine ältere Version verwenden, müssen Sie LINKS, RECHTS, TEIL und FINDEN als Ersatz nutzen.
Mit Trennzeichen außerhalb von Excel arbeiten
Manchmal beginnen Ihre Daten außerhalb von Excel, als CSV-Export oder als Rohtextdatei mit inkonsistenten Trennzeichen. Bevor Sie Excel überhaupt öffnen, müssen Sie möglicherweise diese Trennzeichen umformatieren. Der Online-Trennzeichen-Konverter auf Delimiter.site ermöglicht es Ihnen, ein Trennzeichen in Sekundenschnelle gegen ein anderes auszutauschen – ohne eine einzige Formel zu schreiben.
Sobald Ihre Datei ein einheitliches Trennzeichen verwendet, werden Excel-Formeln wie die oben genannten wesentlich zuverlässiger. Müll rein, Müll raus gilt nach wie vor.
Wichtige Punkte
- TEXTJOIN kombiniert einen Bereich von Zellen mit einem beliebigen Trennzeichen Ihrer Wahl und behandelt leere Zellen sauber.
- TEXTBEFORE extrahiert alles links von einem Trennzeichen, mit Unterstützung für das Ansprechen bestimmter Vorkommen.
- TEXTAFTER macht dasselbe von der rechten Seite, und beide unterstützen negative Instanznummern zum Zählen vom Ende.
- Alle drei Funktionen sind in Excel 365 und Excel 2021+ verfügbar, jedoch nicht in älteren Versionen.
- Wenn Ihre Quelldaten unordentliche oder gemischte Trennzeichen haben, bereinigen Sie diese zuerst mit einem Tool wie dem Komma-zu-Pipe-Konverter, bevor Sie die Daten in Excel importieren.
Setzen Sie diese Formeln ein
TEXTJOIN, TEXTBEFORE und TEXTAFTER sind wirklich nützlich, sobald Sie sich mit dem Trennzeichen-Argument vertraut gemacht haben. Sie decken die häufigsten Aufgaben zum Aufteilen und Verbinden von Text ab – ohne komplexe Hilfsspalten oder VBA.
Beginnen Sie mit einem kleinen Datensatz, probieren Sie jede Formel einmal aus, und Sie werden schnell sehen, wo sie in Ihren regulären Workflow passen. Und wenn Sie vor dem Import in Excel Trennzeichen online konvertieren müssen, ist auch dieser Schritt genauso schnell erledigt.