Excel-Substring-Techniken: Text extrahieren und formatieren

Text nach Position mit Excel-Substring-Funktionen extrahieren

Oft wollen wir bestimmte Teile einer Zeichenkette – etwa ein Wort oder eine Zeichenfolge – basierend auf ihrer Position herausziehen. Excel bietet mehrere Funktionen, um gezielt Textteile zu extrahieren. Zu den gängigsten gehören LEFT(), RIGHT(), MID(), TEXTBEFORE() und TEXTAFTER(). Schauen wir sie uns an. 

Die Excel-Substring-Funktion LEFT() verwenden

Mit LEFT() extrahierst du Zeichen vom Anfang einer Zeichenkette. Die Syntax lautet:

=LEFT(text, [num_chars])

Dabei gilt: 

• text ist die Zelle, die die ursprüngliche Zeichenkette enthält.

• num_chars ist die Anzahl der zu entnehmenden Zeichen. Standard ist 1.

Ein Beispiel: Hier habe ich Produktcodes und möchte die ersten drei Zeichen daraus ziehen. 

=LEFT(A2,3)

Die ersten drei Zeichen mit LEFT() holen. Bild: Autorin/Autor.

Die Excel-Substring-Funktion RIGHT() verwenden

RIGHT() extrahiert Zeichen vom Ende einer Zeichenkette. Die Syntax lautet:

=Right(text, [num_chars])

Dabei gilt: 

• text ist die Zelle mit der ursprünglichen Zeichenkette.

• num_chars ist die Anzahl der zu entnehmenden Zeichen. Standard ist 1.

Im Beispiel extrahiere ich die letzten drei Zeichen mit RIGHT().

=RIGHT(A2, 3)

Die letzten drei Zeichen mit RIGHT() holen. Bild: Autorin/Autor.

Die Excel-Substring-Funktion MID() verwenden

MID() extrahiert Zeichen aus der Mitte einer Zeichenkette. Die Syntax lautet:

=MID(text, start_num, num_chars)

Dabei gilt:

• text ist die Zelle mit der ursprünglichen Zeichenkette.

• num_chars ist die Anzahl der zu entnehmenden Zeichen. Standard ist 1.

• start_num ist die Startposition innerhalb der Zeichenkette.

Im Beispiel extrahiere ich mit MID() ab dem vierten Zeichen drei Zeichen aus der Mitte.

=MID(A2,4,3)

Die mittleren drei Zeichen mit MID() holen. Bild: Autorin/Autor. 

Und fertig: =MID(A2,4,3) zieht drei Zeichen aus Zelle A2, beginnend beim 4. Zeichen.

Die Excel-Funktion FIND() verwenden

FIND() lokalisiert eine Teilzeichenkette in einem Text und gibt die Position des ersten Zeichens dieser Teilzeichenkette zurück. Die Syntax lautet:

FIND(find_text, within_text, [start_num]) 

Dabei gilt:

• find_text ist der zu suchende Text.

• within_text ist der Text, in dem gesucht wird.

• start_num ist die Startposition der Suche, Standard ist 1.

Ein kleines Beispiel, um FIND() zu zeigen: In der Spalte Text steht DataCamp. Ich suche die Position der Teilzeichenkette Camp. Das Ergebnis ist 5, weil Camp beim fünften Zeichen beginnt.

=FIND("Camp", A2)

Mit FIND() die Position im Text ermitteln. Bild: Autorin/Autor.

Die Excel-Funktion SUBSTITUTE() verwenden

SUBSTITUTE() ersetzt eine bestimmte Teilzeichenkette durch eine andere. Die Syntax lautet:

 SUBSTITUTE(text, old_text, new_text, [instance_num])

Dabei gilt:

• text enthält den zu ändernden Text.

• old_text ist der zu ersetzende Text.

• new_text ist der Text, der den alten ersetzt.

• instance_num gibt an, welches Vorkommen ersetzt werden soll. Ohne Angabe werden alle Vorkommen ersetzt. 

Dazu ein einfaches Dataset mit Trennzeichen. In der Spalte Text stehen Namen und Alter, getrennt durch Semikolons. Um das zu bereinigen, verwende ich:

=SUBSTITUTE(A2, ";" , ",")

Dabei gilt:

• A2 ist die Zelle mit dem Originaltext.

• ";" ist das zu ersetzende alte Trennzeichen.

• "," ist das neue Trennzeichen.

Mit SUBSTITUTE alten Text ersetzen. Bild: Autorin/Autor.

Das war’s. Mit der Formel in B2 ersetzt Excel das Semikolon durch ein Komma.

Text per Trennzeichen mit Excel-Substring-Funktionen extrahieren

Trennzeichen sind Zeichen, die Textteile voneinander abgrenzen, z. B. Semikolon oder Komma. In Excel kannst du mit TEXTBEFORE() und TEXTAFTER() Text extrahieren, wenn Trennzeichen vorhanden sind.  

Die Excel-Substring-Funktion TEXTBEFORE() verwenden 

Wie der Name sagt, extrahiert TEXTBEFORE() den Text vor einem bestimmten Zeichen oder einer Teilzeichenkette. Die Syntax lautet:

=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]

Dabei gilt: 

• text ist der Originaltext.

• delimiter ist in der Regel ein Komma oder Semikolon.

• instance_num ist das gewünschte Vorkommen des Trennzeichens. Standard ist 1.

• match_mode gibt an, ob die Suche nach dem Trennzeichen groß-/kleinschreibungssensitiv (TRUE, Standard) oder -unsensitiv (FALSE) ist.

• match_end legt fest, ob das Ende der Zeichenkette als Trennzeichen behandelt wird. Bei TRUE wird der Originaltext zurückgegeben, wenn kein Trennzeichen gefunden wird.

• if_not_found definiert einen benutzerdefinierten Rückgabewert, wenn kein Trennzeichen gefunden wird.

Ein Beispiel: In der Spalte Text stehen Mitarbeiterdetails wie Name, Abteilung und Standort. Ich möchte nur Namen und Abteilungen extrahieren.

=TEXTBEFORE(A2, ",", 2, 1, 1)

TEXTBEFORE() zum Extrahieren verwenden. Bild: Autorin/Autor 

So funktioniert’s: 

• A2 enthält den Text, aus dem ich extrahiere. 

• "," ist das Trennzeichen im Originaltext. 

• 2 nimmt das zweite Vorkommen des Trennzeichens.

• 1 ignoriert Groß-/Kleinschreibung.

• 1 (am Ende) gibt den Originaltext zurück, wenn kein Trennzeichen vorhanden ist.

Die Excel-Substring-Funktion TEXTAFTER() verwenden

TEXTAFTER() ähnelt TEXTBEFORE() – der Unterschied liegt im Ergebnis. TEXTAFTER() extrahiert den Text nach einem Trennzeichen. Die Syntax lautet:

=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]

Dabei gilt: 

• text ist der Originaltext.

• delimiter ist ein Komma oder Semikolon. 

• instance_num ist das gewünschte Vorkommen des Trennzeichens. Standard ist 1.

• match_mode legt fest, ob groß-/kleinschreibungssensitiv (TRUE, Standard) oder -unsensitiv (FALSE) gesucht wird.

• match_end bestimmt, ob das Ende der Zeichenkette als Trennzeichen gilt. Bei TRUE wird der Originaltext zurückgegeben, wenn kein Trennzeichen gefunden wird.

• if_not_found definiert einen benutzerdefinierten Rückgabewert, wenn kein Trennzeichen gefunden wird.

Hier enthält die Spalte Text die Mitarbeiterdetails mit Name, Abteilung und Standort. Daraus möchte ich Standort und ID extrahieren. 

=TEXTAFTER(A2,",",2,1,1)

Mit TEXTAFTER() Daten extrahieren. Bild: Autorin/Autor 

So funktioniert’s: 

• A2 enthält den Text, aus dem ich extrahiere. 

• "," ist das Trennzeichen im Originaltext. 

• 2 nimmt das zweite Vorkommen.

• 1 ignoriert Groß-/Kleinschreibung.

• 1 (am Ende) gibt den Originaltext zurück, wenn kein Trennzeichen vorhanden ist.

Spezifische Anwendungsfälle für Excel-Substrings

Jetzt, da du die Grundlagen kennst, geht’s an die fortgeschrittenen Methoden. Damit gelingen komplexere Textmanipulationen – und deine Datenverarbeitung wird noch effizienter. 

Umgang mit unterschiedlich langen Vornamen

Ich habe ein Dataset mit dem vollständigen Namen, möchte aber nur den Vornamen extrahieren.

Eine Tabelle mit vollständigen Namen. Bild: Autorin/Autor.

Klingt simpel – LEFT() nehmen und den Vornamen holen. Also habe ich getestet:

=LEFT(A2,4)

Mit LEFT() den Vornamen holen. Bild: Autorin/Autor.

Statt des Vornamens erhalte ich aber nur die ersten vier Zeichen, weil ich num_char auf 4 gesetzt habe – da „Jane“ vier Buchstaben hat. Übertrage ich die Formel, passt das Ergebnis bei anderen Namen nicht.

Ich brauche eine flexiblere Lösung. Unabhängig von der Länge lässt sich der Vorname extrahieren, wenn du LEFT() mit FIND() kombinierst. FIND() ermittelt die Position des ersten Leerzeichens, das Vor- und Nachnamen trennt. LEFT() extrahiert dann bis zu dieser Position. 

=LEFT(A2,FIND(" ",A2)-1)

FIND() und LEFT() kombinieren, um den Vornamen zu holen. Bild: Autorin/Autor.

So setzt sich die Formel zusammen:

• A2 enthält den vollständigen Namen.

• FIND(" ", A2, 1) findet die Position des ersten Leerzeichens in A2, ausgehend vom ersten Zeichen.

• -1 entfernt das Leerzeichen.

• LEFT(A2, ...) zieht die angegebene Zeichenanzahl vom linken Rand.

Domain aus E-Mail-Adresse extrahieren

Noch ein Beispiel für LEFT() und FIND() in anderem Kontext: Ich habe ein Dataset mit E-Mail-Adressen und möchte die Domain extrahieren.

Daten mit E-Mail-Adressen. Bild: Autorin/Autor.

Dafür lege ich die Spalte Email Domain an und tippe folgende Formel, die ich dann nach unten kopiere:

=RIGHT(A2, LEN(A2) - FIND("@",A2))

Domain mit RIGHT(), LEN() und FIND() extrahieren. Bild: Autorin/Autor.

Und schon habe ich alle Domains. So ermittelt Excel das Ergebnis:

• FIND("@", A2) findet die Position des @-Zeichens in der E-Mail-Adresse.

• LEN(A2) berechnet die Gesamtlänge der Adresse.

• LEN(A2) - FIND("@", A2) berechnet die Zeichenanzahl nach dem @.

• Am Ende extrahiert RIGHT(A2, LEN(A2) - FIND("@", A2)) genau diese Anzahl an Zeichen von rechts.

Produktcodes mit LEFT(), MID() und RIGHT() kombinieren und extrahieren

Bisher haben wir LEFT(), MID() und RIGHT() einzeln genutzt. In Kombination kannst du jedoch wesentlich mehr erreichen und je nach Muster unterschiedliche Textteile herausziehen.

Hier habe ich eine Liste mit Produktcodes und möchte einen bestimmten Abschnitt extrahieren. 

• Aus dem ersten Abschnitt brauche ich ein Zeichen.
• Aus dem zweiten Abschnitt brauche ich alle vier Zeichen.
• Aus dem letzten Abschnitt benötige ich die letzten drei Zeichen.

Eine Liste mit Produktcodes. Bild: Autorin/Autor.

Dazu kombiniere ich LEFT(), RIGHT() und MID() und nutze folgende Formel:  

=LEFT(A2, 1) & "-"& MID(A2, FIND("-", A2) + 1, 4) & "-" & RIGHT(A2, 3)

MID(), LEFT() und RIGHT() kombinieren, um Daten zu holen. Bild: Autorin/Autor.

Wie du siehst, extrahiert die kombinierte Formel aus jedem Abschnitt die gewünschten Zeichen. So funktioniert’s:

• LEFT(A2, 1) holt das erste Zeichen.

• MID(A2, FIND("-", A2) + 1, 4) findet den ersten Bindestrich, springt ein Zeichen nach rechts und extrahiert vier Zeichen.

• "-" fügt einen Bindestrich hinzu.

• RIGHT(A2, 3) holt die letzten drei Zeichen.

• & verknüpft die Teile. 

SUBSTITUTE() mit MID() für dynamische Textextraktion nutzen 

Schauen wir uns an, wie du MID() mit SUBSTITUTE() kombinierst, um gezielt Teile eines Texts zu extrahieren und anschließend Zeichen oder Teilzeichenketten innerhalb dieses Abschnitts zu ersetzen.

Beispiel: Ich habe Bestelldetails von Kundinnen/Kunden und möchte die Bestell-ID durch den Text Order confirmed ersetzen.

Tabelle mit Bestelldetails. Bild: Autorin/Autor.

Ich verwende diese Formel:

=SUBSTITUTE(A2, MID(A2,1,19), "Order confirmed")

SUBSTITUTE() und MID() zum Ersetzen verwenden. Bild: Autorin/Autor.

Und das war’s – die Formel extrahiert und ersetzt den Text wie gewünscht.

• MID(A2, 1, 19) holt die ersten 19 Zeichen aus A2.

• SUBSTITUTE(text, old_text, new_text) ersetzt old_text durch new_text.

Noch ein Beispiel: In der Spalte Product Detail möchte ich die Produktgröße aus eckigen Klammern extrahieren.

=SUBSTITUTE(MID(A2, FIND("[", A2)+1, FIND("]", A2)-FIND("[", A2)-1), "[", "")

Produktgröße aus eckigen Klammern extrahieren. Bild: Autorin/Autor.

Fertig – alle Details sind extrahiert.

• FIND("[", A2) findet die Startposition der öffnenden Klammer.

• FIND("]", A2) findet die Position der schließenden Klammer.

• MID(A2, FIND("[", A2)+1, FIND("]", A2)-FIND("[", A2)-1) extrahiert den Text zwischen den Klammern.

• SUBSTITUTE(...,"[", "") entfernt eine eventuell verbleibende öffnende Klammer im extrahierten Text.

Datenbereinigung von Eingaben

Manchmal sind Daten uneinheitlich formatiert. Das musst du nicht hinnehmen. Hier habe ich Telefonnummern in verschiedenen Formaten und möchte sie vereinheitlichen.

Unformatierte Telefonnummern. Bild: Autorin/Autor.

Zur Standardisierung lege ich die Spalte Clean data an. In die zweite Zelle schreibe ich die folgende Formel und kopiere sie nach unten:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "-", ""), "(", ""), ")", ""), ".", "")

Dabei gilt: 

• SUBSTITUTE(A2, "-", "") entfernt Bindestriche.

• SUBSTITUTE(..., "(", "") entfernt Klammer auf.

• SUBSTITUTE(..., ")", "") entfernt Klammer zu.

• SUBSTITUTE(..., ".", "") entfernt Punkte.

So werden aus den unterschiedlichen Formaten durchgängige Ziffernfolgen, wie unten gezeigt. Die Daten sind bereinigt und einsatzbereit. Du kannst sie so belassen, wenn du möchtest.

Daten mit SUBSTITUTE() bereinigen. Bild: Autorin/Autor.

Text für Berichte formatieren

Nach der Bereinigung kannst du mit Substring-Funktionen auch die Darstellung formatieren.

Im Anschluss an das obige Beispiel mit Telefonnummern möchte ich die Zahlen so formatieren, dass sie wie Telefonnummern aussehen – nicht nur wie eine Ziffernfolge. 

=TEXT(LEFT(B2, 3), "000") & "-" & TEXT(MID(B2, 4, 3), "000") & "-" & TEXT(RIGHT(B2, 4), "0000")

Standardisiertes Nummernformat mit mehreren Substrings. Bild: Autorin/Autor.

Und fertig. So arbeitet die Formel:

• LEFT(B2, 3) holt die ersten drei Ziffern.

• MID(B2, 4, 3) holt die nächsten drei Ziffern ab der vierten Position.

• RIGHT(B2, 4) holt die letzten vier Ziffern.

• TEXT(..., "000") und TEXT(..., "0000") formatiert die Abschnitte mit führenden Nullen, falls nötig.

• & "-" & verbindet die formatierten Teile mit Bindestrichen.

Fazit

Du hast gelernt, wie du Excel-Substring-Funktionen wie LEFT(), RIGHT(), MID(), TEXTBEFORE() und TEXTAFTER() für die Arbeit mit Textdaten nutzt. Ob du gezielt Teile einer Zeichenkette herausziehst oder unübersichtliche Daten bereinigst – diese Funktionen machen deine Arbeit schneller und leichter.

Mit Excel gibt es immer mehr zu entdecken. Der Kurs Introduction to Excel ist der perfekte nächste Schritt für den Einstieg. Wenn du tiefer in die Datenanalyse einsteigen willst, probiere Data Analysis in Excel – ideal in Kombination mit unserem Data Manipulation in Excel Cheat Sheet. So legst du ein starkes Fundament und bringst deine Kompetenzen aufs nächste Level.