Text aus einer Zelle extrahieren – Excel und Google Sheets
Die Beispielarbeitsmappe herunterladen
In diesem Tutorial zeigen wir Ihnen, wie Sie einen Text aus einer Zelle in Excel und Google Sheets extrahieren können.
Text von links extrahieren
Mit der LINKS-Funktion können Sie einen Text aus der linken Seite einer Zelle in Excel extrahieren. Geben Sie einfach den Text und die Anzahl der zurückzugebenden Zeichen ein.
Dabei wird jedoch nur eine bestimmte Anzahl von Zeichen extrahiert. Wie Sie oben sehen können, werden einige Kochtemperaturen korrekt extrahiert (z. B. 300), einige jedoch nicht (z. B. 95F). Um eine dynamische Formel, die in allen Szenarien funktioniert, zu erstellen, können wir die LÄNGE-Funktion in Kombination mit der LINKS-Funktion verwenden.
Die LÄNGE-Funktion – Zählen der Zeichen in einer Zelle
Verwenden Sie die LÄNGE-Funktion, um die Zeichen in einer Zelle zu zählen:
=LÄNGE(C3)
Die LINKS-Funktion – Zeichen von links anzeigen
Erstellen Sie dann eine neue LINKS-Funktion, die eine Anzahl von Zeichen, die durch die oben erstellte LÄNGE-Funktion bestimmt wird, extrahiert.
=LINKS(C3;E3-1)
Die Kombination dieser Funktionen sieht wie folgt aus:
=LINKS(C3;LÄNGE(C3)-1)
Die Funktionen RECHTS und LÄNGE
In ähnlicher Weise können wir auch Zeichen von der rechten Seite einer Zelle extrahieren, indem wir die RECHTS-Funktion verwenden, um eine bestimmte Anzahl von Zeichen von rechts zurückzugeben.
=RECHTS(B3;LÄNGE(B3)-C3)
Die Funktionen TEIL und SUCHEN
Im nächsten Abschnitt werden wir die Funktionen SUCHEN und TEIL verwenden, um Zeichen aus der Mitte einer Textkette zu extrahieren.
=TEIL(B3;SUCHEN(" ";B3)+1;999)
Die SUCHEN-Funktion
Zunächst verwenden wir die SUCHEN-Funktion, um die Position des Leerzeichens (“ „) zwischen dem Vor- und dem Nachnamen zu finden.
=SUCHEN(" ";B3)
Die TEIL-Funktion
Als nächstes verwenden wir die TEIL-Funktion, um alle Zeichen nach dem Leerzeichen zu finden.
- Wir müssen 1 zum Ergebnis der vorherigen Formel hinzuaddieren, damit wir das erste Zeichen nach dem Leerzeichen zurückgeben.
- Wir verwenden die große Zahl 999, um alle Zeichen zurückzugeben.
=TEIL(B3;C3+1;999)
Kombiniert man diese 2 Funktionen, erhält man die ursprüngliche Formel für den Nachnamen.
=TEIL(B3;SUCHEN(" ";B3)+1;999)
Text vor oder nach einem bestimmten Zeichen extrahieren
Sie können auch die Funktionen LINKS, RECHTS, LÄNGE und SUCHEN verwenden, um den Text vor oder nach einem bestimmten Zeichen zu extrahieren. In diesem Fall werden wir Vor- und Nachnamen trennen.
Text vor einem Zeichen extrahieren
Zunächst können wir die SUCHEN-Funktion verwenden, um die Position des Kommas in der Textzeichenfolge zu finden.
=SUCHEN(" ";B3)
Anschließend können wir die LINKS-Funktion verwenden, um den Text vor der Position des Kommas zu extrahieren.
- Wir müssen 1 von der Position des Kommas subtrahieren, um das Komma nicht in unser Ergebnis aufzunehmen.
=LINKS(B3; SUCHEN(",";B3)-1)
Durch die Kombination dieser 2 Funktionen erhalten wir die ursprüngliche Formel für den Nachnamen.
Text nach einem Zeichen extrahieren
=RECHTS(B3;LÄNGE(B3)-SUCHEN(",";B3)-1)
Zusätzlich zur erneuten Verwendung der SUCHEN-Funktion verwenden wir auch die LÄNGE-Funktion in Verbindung mit der RECHTS-Funktion, um den Text nach einem bestimmten Zeichen zu extrahieren.
Die LÄNGE-Funktion dient dazu, die Länge des Textes in B3 zu ermitteln, während die SUCHEN-Funktion erneut verwendet wird, um die Position des Kommas zu finden. Anschließend verwenden wir die RECHTS-Funktion, um die Zeichen nach dem Komma in der Textzeichenfolge zu extrahieren.
Text aus der Mitte einer Zeichenkette extrahieren
Als Nächstes werden wir besprechen, wie man einen Text aus der Mitte einer Zeichenkette extrahiert.
Um einen Text aus der Mitte einer Zeichenkette zu extrahieren, können wir die Funktionen RECHTS, SUCHEN und LÄNGE verwenden. Damit erhalten wir den Text von der rechten Seite der Zeichenkette und dann verwenden wir die Funktionen TEIL und LÄNGE , um den Text in der Mitte zu erhalten. Wir werden auch die GLÄTTEN-Funktion einbeziehen, um alle Leerzeichen auf beiden Seiten der Textzeichenfolge zu entfernen.
=RECHTS(B3;LÄNGE(B3)-SUCHEN(" ";B3)-LÄNGE(GLÄTTEN(TEIL(B3;SUCHEN(" ";B3;1)+1;
SUCHEN(" ";B3;SUCHEN(" ";B3;1)+1)-SUCHEN(" ";B3;1))))-1)
Diese Formel funktioniert nur, wenn die Textzeichenfolge mehr als ein Leerzeichen enthält. Wenn nur ein Leerzeichen vorhanden ist, wird ein Fehler mit #WERT zurückgegeben.
Um dieses Problem zu lösen, können wir für Namen ohne zweiten Vornamen oder Initialen die ursprüngliche Formel unter Verwendung der Funktionen TEIL und SUCHEN verwenden.
=TEIL(B3;SUCHEN(" ";B3)+1;999))Wir können Unter Verwendung der WENNFEHLER-Funktion eine einzige Formel erstellen, die beide Techniken verwendet, um alle Situationen zu behandeln. (Die WENNFEHLER-Funktion führt eine Berechnung durch. Wenn diese Berechnung zu einem Fehler führt, wird eine weitere Berechnung durchgeführt).
=WENNFEHLER(RECHTS(B3;LÄNGE(B3)-SUCHEN(" ";B3)-LÄNGE(GLÄTTEN(TEIL(B3;SUCHEN(" ";B3;1)+1;
SUCHEN(" ";B3;SUCHEN(" ";B3;1)+1)-SUCHEN(" ";B3;1))))-1);TEIL(B3;SUCHEN(" ";B3)+1;999))
Anschließend können wir die Funktionen TEIL und LÄNGE verwenden, um den zweiten Vornamen oder die Initialen zu erhalten.
=TEIL(B3;LÄNGE(C3)+1;LÄNGE(B3)-LÄNGE(C3&D3))
Text aus einer Zelle in Google Sheets extrahieren
Alle o. g. Beispiele funktionieren in Google Sheets auf die gleiche Weise.
