Für die Arbeit mit Texten und Textteilen bietet Excel einige sehr praktische Funktionen. So lassen sich mit den Funktionen LINKS() oder RECHTS() gezielt bestimmte Teile aus einem Text herauslösen und anschließend getrennt weiterverarbeiten.
Wie kann man nun z.B. aus einem Satz variabler Länge gezielt das letzte Wort herauslösen?
In Kenntnis der RECHTS-Funktion eine auf den ersten Blick trivial erscheinende Aufgabe, die es bei näherer Betrachtung aber in sich hat. Wozu man das brauchen sollte? Um z.B. in einem kombinierten Namensfeld nur den Nachnamen zu isolieren.
Der folgende Artikel stellt zwei mögliche Lösungen vor.
Die Ausgangslage
Ich habe eine kleine Musterliste, in der sämtliche Namensbestandteile (Titel, Vorname, Name) in einer einzigen Zelle stehen:
Die unbearbeitete Namensliste
Wie unschwer zu erkennen ist, hat nicht jeder Datensatz die gleiche Struktur: Manche Namen bestehen aus nur 2 Elementen (Vorname, Name), während andere mit Titel und zweitem Vornamen durchaus 4 oder 5 Namensbestandteile haben können. Die einzige Gemeinsamkeit: alle Elemente sind durch ein Leerzeichen voneinander getrennt.
Wie bekommt man nun die Trennung hin?
Möglichkeit 1: Text in Spalten
Die bequemste Funktion bietet Excel mit „Text in Spalten“, die sich in der Registerkarte „Daten“ verbirgt:
Die Funktion „Text in Spalten“
Dazu markiert man zunächst die Liste mit den Namen und startet dann die genannte Funktion. Dies ruft den sogenannten Textkonvertierungs-Assistenten auf den Plan, mit dessen Hilfe in drei kleinen Schritten die Namensliste in ihre Bestandteile zerlegt wird:
Schritt 1: Da die Einzelteile durch ein Leerzeichen getrennt sind, können wir hier die vorgegebene Option „Getrennt“ einfach übernehmen:
Schritt 1 im Textkonvertierungs-Assistenten
Schritt 2: Hier setzen wir den Haken vor „Leerzeichen“ (alle anderen Trennzeichen werden deaktiviert) und man sieht unten in der Vorschau schon das Ergebnis:
Schritt 2: Das Trennzeichen festlegen
Schritt 3: Die einzige Änderung, die wir vornehmen, ist die Zellangabe im Feld „Zielbereich“. Hier wähle ich eine leere Zelle rechts neben unserer Ausgangsliste. Das hat den Vorteil, dass die Original-Liste unverändert bleibt:
Schritt 3: Das Ausgabeziel festlegen
Nach einem Klick auf „Fertigstellen“ erhalten wir unsere gesplittete Adressenliste:
Die aufgesplittete Namensliste
Das angestrebte Ziel – die Extraktion des Nachnamens – wurde zwar erreicht. Aber jeder Datensatz unterschiedlich viele Elemente beinhaltet, steht der Nachname leider in unterschiedlichen Spalten.
Das geht doch noch besser, oder?
Möglichkeit 2: Die Formel-Lösung
Die Funktion, die uns hier weiterhelfen soll, heißt RECHTS():
=RECHTS(Text; Anzahl Zeichen)
Damit wird vom angegebenen Text ausgehend vom rechten Ende die definierte Anzahl an Zeichen ausgegeben.
=RECHTS(„Martin Weiß“; 4) ergibt folglich „Weiß“
Das Dumme ist nur, dass nicht jeder Name gleich lang ist, wir also keine feste Zeichenanzahl angeben können. Wir müssen also nach dem letzten Leerzeichen im Datensatz suchen und von dort die Anzahl der Zeichen bis zum Ende bestimmen.
Doch wie findet man das letzte Leerzeichen?
Die Funktion FINDEN() ist zumindest schon mal ein guter Ausgangspunkt. Sie gibt die Position zurück, an welcher Stelle sich ein gesuchter Text befindet:
=FINDEN(Gesuchter Text; Text; [Erstes Zeichen])
Der gesuchte Text wird dabei in doppelte Anführungszeichen gesetzt. Um in Zelle A2 die Position des ersten Leerzeichens zu finden, lautet die Funktion
=FINDEN(" "; A2)
Uns interessiert aber das letzte Leerzeichen. Dazu müssen wir jedoch erst wissen, wie viele davon im Text enthalten sind. Um das zu bestimmen, bedienen wir uns eines kleinen Tricks:
Mit der Funktion LÄNGE() ermitteln wir zunächst die Gesamtlänge des Textes inklusive Leerzeichen und ein zweites Mal die Länge ohne Leerzeichen. Die Differenz der beiden Längen ergibt die Anzahl der Leerzeichen.
Um die Textlänge ohne Leerzeichen zu berechnen, müssen wir alle Leerzeichen aus unserem Datensatz entfernen. Dies geschieht über die Funktion WECHSELN():
=WECHSELN(Text; Alter Text; Neuer Text)
So sucht…
=WECHSELN(A2;" ";)
…im Text in Zelle A2 nach dem Leerzeichen (= 2. Parameter). Da wir den dritten Parameter einfach weggelassen haben, wird das Leerzeichen durch nichts ersetzt – also entfernt:
Alle Leerzeichen entfernen
Somit ergibt sich für unsere Beispieltabelle folgendes Bild:
Längenermittlung: Mit und ohne Leerzeichen
Wir wissen also jetzt, wie viele Leerzeichen in jedem Datensatz enthalten sind. Damit können wir die Position des letzten Leerzeichens bestimmen, indem wir dieses durch ein anderes eindeutiges Zeichen ersetzen und dann danach suchen.
Das erfolgt wieder mit der WECHSELN-Funktion. Allerdings verwenden wir jetzt noch einen vierten optionalen Parameter, mit dem bestimmt werden kann, das wievielte Vorkommen eines Zeichens für den Wechsel berücksichtigt werden soll:
Das letzte Leerzeichen wird ersetzt
Im Bild oben haben wir in Zelle E2 den Wert 2 (für zwei enthaltene Leerzeichen) ermittelt. Diesen Wert übergeben wir als 4. Parameter an die WECHSELN-Funktion. Der neue Text muss ein Zeichen sein, dass normalerweise nicht in unserer Adressenliste vorkommt. Ich habe mich hier für das #-Zeichen entschieden, denkbar wären aber auch §, ~ und ähnliches.
Wir nähern uns langsam der Zielgeraden. Jetzt können wir mit der FINDEN-Funktion die Position des #-Zeichens berechnen:
Die Position des neuen Zeichens
Damit haben wir alles, was notwendig ist und können nun mit der RECHTS-Funktion den Nachnamen herausschneiden, indem wir von der Gesamtlänge des Datensatzes die zuvor ermittelte Position des #-Zeichens abziehen:
Der isolierte Nachname
Abschluss-Arbeiten… und Schwachstellen
Zu guter Letzt packen wir noch alle oben erstellten Teil-Formeln in ein einziges Gesamtkunstwerk und haben damit unsere Nachnamen in einer schönen Liste:
Alle Funktionen in einer Formel
Eine Schwachstelle an dieser „Traum“-Formel möchte ich dir jedoch nicht vorenthalten: Besteht der eigentliche Nachname ebenfalls aus mehreren durch Leerzeichen getrennte Elemente, scheitert dies Lösung leider grandios:
Die einzige Schwachstelle…
Hier musst du wohl oder übel von Hand nacharbeiten – oder dir eine noch genialere Formel ausdenken. Lass es uns unten in den Kommentaren wissen, wenn du eine Lösung dafür gefunden hast!
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Guten Morgen Herr Weiß,
vielen Dank für diese geniale Formel, die hätte ich vor ein paar Woche gut gebrauchen können und mir ein paar Stunden Arbeit erspart. Die Funktion „WECHSELN“ kannte ich bisher noch nicht. Aber beim nächsten mal dann …
Ich habe öfters mal lange Adresslisten als Export aus unserem ERP-System, aus der ich verschiedene Teile extrahieren muss. Nun hat die exportierte Liste jedoch die Besonderheit, dass das ERP-System leere Datenbankfelder in der Excelliste durch Leerzeichen ersetzt, so dass öfters Textteile durch mehrere aufeinanderfolgende Leerzeichen getrennt werden. Gibt es für diesen Fall auch so eine „einfache“ Formel wie die oben beschriebene?
Guten Morgen,
ja, die Formel gibt es und heißt GLÄTTEN. Sie schneidet führende und folgende Leerzeichen ab und reduziert im Text mehrfache Leerzeichen auf jeweils ein einzelnes.
Carsten
Hallo Herr Esper,
ich kann mich dem Tipp von Carsten nur anschließen: Die GLÄTTEN-Funktion ist gerade Zusammenhang mit externen Daten Gold Wert!
Schöne Grüße,
Martin
Hallo Martin,
mangels Office 2013/2016-Verfügbarkeit kann ich es nicht testen, jedoch wäre eine Erwähnung der „Blitzvorschau“ ab der 2013er Version nicht verkehrt. Je nach Anwendungsfall kann es zu einer schnellen Lösung ganz ohne Formel führen.
Hallo Andi,
guter Hinweis, die Blitzvorschau ist auch eine interessante Alternative. Wer es noch nicht kennt, ich hatte vor einiger Zeit schon einen Artikel dazu veröffentlicht.
Schöne Grüße,
Martin
Schöne Lösung.
Für das Problem mit den „von“ oder „zu“ im Nachnamen könnte man noch eine einfache WENN-DANN-Formel drum herum basteln, die im Falle eines “ von “ in der Zelle dieses einfach noch davor schreibt. So in der Art =WENN(FINDEN(“ von „;A1;1)>1;“ von „& (jetzt hier ohne klammern die Nachnahmeformel);(und noch mal die Formel)). Würde natürlich etwas verschachtel werden, sollte man nach „von“, „zu“, „van“ und was weiß ich nicht alles suchen.
Oder man verändert beim Finden von solchen Zusätzen die Position, ab der Text zurück gegeben wird, um 4, respektive 3 Felder nach links.
Hallo Tim,
danke für Deinen Tipp. Es lässt sich vermutlich nicht vermeiden, dass es etwas verschachtelt wird, wenn man viele Varianten abfangen möchte.
Schöne Grüße,
Martin
Eigentlich bekommt man ja selten das letzte Wort, daher mag ich ebenfalls mein Lob aussprechen und sehe im gegebenen Beispiel neben der geschilderten Funktion auch ein gutes Beispiel für eine Kombination aus Bestelllung, Kundennummer oder andere Merkwürdigkeiten die es so in einer Tabellenzelle geben kann 🙂 Von daher an dieser Stelle ein großes Lob in Richtung deiner schrittweisen Erläuterung und Verzicht des Zerlegen des Textstring und bearbeiten per INDEX Funktion :-))) Die beschriebene Formel erscheint mir da wesentlich angenehmer und tatsächlich vielseitig einsetzbar.
Viele Grüße
Andreas
Hallo Andreas,
vielen Dank für das Lob. Das Beispiel mit mit Bestellnummer, Kundennummer etc. ist auch ein guter Anwendungsfall. Gerade im Umgang mit Daten aus ERP-System gibt es ja praktisch nichts, was es nicht gibt 🙂
Schöne Grüße,
Martin
Hallo Martin,
>>>Hier musst du wohl oder übel von Hand nacharbeiten – oder dir eine noch genialere Formel ausdenken. Lass es uns unten in den Kommentaren wissen, wenn du eine Lösung dafür gefunden hast!<<<
das gibt es bereits 😉 :
http://www.excelformeln.de/formeln.html?welcher=158
Gruß
Günter
Hallo Günter,
vielen Dank für diesen Tipp. Ich wusste, dass sich irgendjemand schon mal Gedanken dazu gemacht hat.
Schöne Grüße,
Martin
@Andreas Esper Daten aus ERP-System
Daten aus SAP sind zwar „schön tabelliert“, aber die fehlenden Zeichen werden durch Leerzeichen ersetzt.
Sieht beim Drucken noch aus, wie eine richtige Tabelle.
Ich lese diese „Tabelle“ in WORD ein.
Suchen und Ersetzen (Start; Bearbeiten)
Ganz wichtig: Haken bei Platzhalter verwenden
Suchen nach und Ersetzen durch (sehr mächtige Funktion)
(hier hilft http://Www.uni-giessen.de/~g021/PDF/wd2013_suchen_ersetzen.pdf)
Ich ersetzte zwei und mehr Leerzeichen durch einen TAB
Jetzt habe ich eine richtige Tabelle, die ich in EXCEL einlesen kann
Neuer Link ist dann zur Zeit hier zu finden, wurde auf Word 2016 aktualisiert.
https://www.staff.uni-giessen.de/~g021/MS-Word/Word-A/wd2016_suchen_ersetzen.pdf
Gruß EuroCafe
„Mann, bist du gut!!!!!“ Ich arbeite nun schon so lange mit Excel, aber was ich hier in einem Abend lernte, begriff ich in Jahrzehnten nicht. Ich bin dankbar. Respekt.
Hi Alexander,
vielen Dank, das hört man gerne!
Schöne Grüße,
Martin
Guten Abend Herr Weiß,
super toller Tipp und der hat mir viel Zeit gerettet. Ich kann zwar nur jetzt die ersten Zeichen (Links) verwenden und zum Glück die letzten Zahlen von (Rechts) aber mehr habe ich auch nicht benötigt.
Kleiner Tipp für die anderen, mit der Formel gibt wird mit ein Text in dieser Form zurück gegeben 888.121,53, Excel interpretiert dieses als Text und nicht als Zahl. Zellenformatieren in Zahl geht nicht , Text in Spalten passiert auch nicht, daher habe ich es so gelöst
=WERT(RECHTS(A5;LÄNGE(A5)-FINDEN(„#“;WECHSELN(A5;“ „;“#“;LÄNGE(A5)-LÄNGE(WECHSELN(A5;“ „;))))))
Damit wird in diesem Fall aus erkannter Text in Excel dann eine Zahl mit der man dann auch weiterrechnen kann, ist außerdem wichtig für den SVERWEIS. Denn am Anfang meiner Tabelle steht die Konto-Nummer oder Lohnart, mit Links erhält man aber wieder nur Text, und das gibt natürlich dann #NV mit dem SVERWEIS, weil das Suchkriterium eben eine Zahl ist.
Hoffe das hilft anderen weiter und jetzt gehe ich mal den einzelnen Hinweisen unten nach, besonders UNI Gießen, dort Arbeite ich 🙂
Einen schönen Abend und nochmals vielen Dank
Hallo EuroCafe,
Danke für das Lob und für die hilfreichen Ergänzungen, das wird sicherlich für einige Leser nützlich sein. Es kommt oben immer auf den konkreten Anwendungsfall an.
Schöne Grüße,
Martin