Nach dem Import von externen Daten in ein Tabellenblatt müssen oft in Zellen zusammengefasste Informationen für Auswertungen und Sortierungen getrennt und auf mehrere Zellen verteilt werden. Beispielsweise können Sie keine Liste nach Nachnamen sortieren, wenn die Vor- und Nachnamen sich alle in einer Zelle befinden. Mit den neuen Abo-Versionen von Excel können Sie seit geraumer Zeit neue Textfunktionen nutzen, um Texte aus einer Zelle leichter zu extrahieren.

Die neuen Textfunktionen lauten u.a. TEXTTEILEN, TEXTVOR und TEXTNACH.

Den Einsatz diese Funktionen zeigen wir Ihnen am folgenden Beispiel:

Adressenliste in Excel

Der Vor- und Nachname sowie das Land, die PLZ und der Ort befinden sich jeweils in einer Zelle.

Funktion TEXTTEILEN

Im ersten Schritt sollen der Vor- und der Nachname der Personen mit der Funktion TEXTTEILEN auf zwei Spalten aufgeteilt werden. Die Funktion TEXTTEILEN teilt den Text in einer Zelle mit Hilfe von Trennzeichen in mehreren Spalten und/oder Zeilen auf.

Geben Sie in der Zelle C2 folgende Formel ein:

=TEXTTEILEN(A2;” “)

Das Tabellenblatt sieht danach folgendermaßen aus:

Trennung von Vor- und Nachnamen mit TEXTTEILEN

Die Funktion TEXTTEILEN hat insgesamt 5 Parameter, wobei die beiden ersten Parameter eingegeben werden müssen.

Als ersten Parameter erwartet die Funktion die Zelladresse, deren Text Sie aufteilen möchten. Der zweiten Parameter bezeichnet das Trennzeichen. In unserem Beispiel ist es das Leerzeichen. Der Vorname und Nachname stehen danach in zwei verschiedenen Zellen. Diese beiden Zellen bilden einen Überlaufbereich, den Sie am an dem blauen Rahmen um die Zelle D2 erkennen.

Wenn Sie die Formel in C2 nach unten kopieren, Sie das Tabellenblatt so aus:

Trennung von Vor- und Nachnamen

Der Überlaufbereich umfasst bei beiden letzten Zeilen insgesamt 3 Spalten. Der Grund dafür sind zusätzliche Leerzeichen in den Zellen. Um diese Leerzeichen zu ignorieren, ändern Sie die Formel in Zelle C2 folgendermaßen ab:

=TEXTTEILEN(A2;” “;;WAHR)

Wenn Sie die Formel wieder nach unten kopieren, ändert sich die Darstellung so:

Trennung von Vor- und Nachnamen

Mit dem vierten Parameter teilen Sie der Funktion mit, ob die Leerzeichen ignorieren möchten oder nicht. Der Standardwert lautet FALSCH. Der Standardwert braucht nicht angegeben zu werden. In unserem Beispiel ist der Parameter notwendig. Geben Sie als vierten Parameter den Ausdruck WAHR ein.

Im zweiten Schritt soll das Land, die PLZ und der Ort auf 3 Spalten aufgeteilt werden. Da das Land und die PLZ durch einen Bindestrich und die PLZ und der Ort durch ein Leerzeichen getrennt werden, benötigen wir für die Formel zwei Trennzeichen: Geben Sie in der Zelle E2 folgende Formel ein und kopieren Sie sie danach nach unten:

=TEXTTEILEN(B2;{“-“;” “})

Das Tabellenblatt sieht jetzt so aus:

Trennung von Land, PLZ und Ort

Wenn die Funktion mehr als ein Trennzeichen berücksichtigen soll, muss der zweite Parameter in der Funktion als Matrix eingegeben werden. Die einzelnen Trennzeichen müssen in geschweiften Klammern stehen und durch ein Semikolon getrennt werden.

Funktionen TEXTVOR und TEXTNACH

Wenn Sie keinen Überlaufbereich wie bei der Funktion TEXTTEILEN erzeugen möchten oder nur einzelne Informationen aus einer Zelle mit Textinformationen extrahieren möchten, können Sie die neuen Funktionen TEXTVOR und TEXTNACH nutzen. Als Beispiel soll die folgende Adressenliste dienen:

Adressliste mit akademischen Titeln aufteilen

In diesem Beispiel sollen der akademische Titel, der Vorname und der Nachname jeweils auf 3 Spalten verteilt werden.

Als erstes soll der Nachname in Spalte D erscheinen. Geben Sie in der Zelle D2 folgende Formel ein und kopieren Sie si nach unten:

=TEXTNACH(A2;” “)

Das Tabellenblatt hat folgende Aussehen:

Adressliste mit akademischen Titeln aufteilen

Die Funktion TEXTNACH gibt den Text zurück, nach dem einem Trennzeichen steht. In dem Beispiel ist es der Text nach dem ersten Leerzeichen. Da sich in der Zelle A4 zwei Leerzeichen befinden, erscheinen der Vor- und de Nachname in derselben Zelle. Der bessere Ansatz ist in diesem Fall die Ausgabe des Textes nach dem letzten Leerzeichen in der Zelle, oder anders ausgedrückt: nach dem ersten Leerzeichen von rechts. Ändern Sie Formel folgendermaßen ab:

=TEXTNACH(A2;” “;-1)

Das Tabellenblatt sieht danach so aus:

Adressenliste mit der Funktion TEXTNACH()

Mit dem dritten Parameter in der Formel geben Sie an, ab dem wievielten Vorkommen eines Leerzeichens der Text extrahiert werden soll. Der Standardwert lautet 1. Wenn Sie eine -1 eingeben, so wird das erste Leerzeichen von rechts als Ausgangspunkt genommen.

Den Titel können Sie ermitteln, in dem sie die Funktion TEXTVOR in einer WENN-Bedingung integrieren:

Geben Sie die folgende Formel in der Zelle B2 ein:

=WENN(LINKS(A2;4)=”Dr. “;TEXTVOR(A2;” “);””)

Die Funktion TEXTVOR ermittelt den Text vor dem ersten Auftreten des angegebenen Trennzeichens, für den Fall, dass die ersten 4 Zeilen in der Zelle den Ausdruck Dr. enthalten.

Dern Vorname ermitteln Sie, in den Sie in Zelle C2 folgende Formel eingeben:

=WENN(LINKS(A2;4)=”Dr. “;TEXTVOR(TEXTNACH(A2;” “);” “);TEXTVOR(A2;” “))

Falls der Name nicht mit einem Doktortitel beginnt, wird der Text vor dem ersten Leerzeichen genommen. Ansonsten wird zunächst der Text nach dem ersten Leerzeichen extrahiert. Er enthält den Vor- und Nachnamen der Person. Aus diesem Text wird dann noch der Text vor dem ersten Leerzeichen herausgezogen. Das Ergebnis ist der Vorname dieser Person.

Das Tabellenblatt sieht danach so aus:

Adressenliste mit der Funktion TEXTNACH()

Fazit

Mit den neuen Textfunktionen wie TEXTVOR, TEXTNACH und TEXTTEILEN haben Sie zusätzliche Optionen, Texte aus einer Zelle zu extrahieren. Die Formel sind in vielen Fällen einfacher einzusetzen wie die bisherigen Funktionen LINKS, RECHTS oder TEIL.

Weitere Lösungsansätze, um Texte auf mehrere Zellen zu verteilen, finden Sie hier.