Generische Formel
=RIGHT(name,LEN(name)-FIND("*",SUBSTITUTE(name," ","*",LEN(name)-LEN(SUBSTITUTE(name," ","")))))
Zusammenfassung
Wenn Sie den Nachnamen aus einem vollständigen Namen extrahieren müssen, können Sie dies mit dieser recht komplexen Formel tun, die mehrere Funktionen verwendet. In der generischen Form der Formel (oben) ist name ein vollständiger Name, wobei ein Leerzeichen den Vornamen von anderen Teilen des Namens trennt.
Im Beispiel enthält die aktive Zelle die folgende Formel:
=RIGHT(B4,LEN(B4)-FIND("*",SUBSTITUTE(B4," ","*",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))))
Erläuterung
Im Kern verwendet diese Formel die RECHTS-Funktion, um Zeichen von rechts zu extrahieren. Die anderen Funktionen, aus denen der komplexe Teil dieser Formel besteht, machen nur eines: Sie berechnen, wie viele Zeichen extrahiert werden müssen.
Auf hoher Ebene ersetzt die Formel das letzte Leerzeichen im Namen durch ein Sternchen "*" und bestimmt dann mithilfe von FIND die Position des Sternchens im Namen. Die Position wird verwendet, um herauszufinden, wie viele Zeichen mit RECHTS extrahiert werden sollen.
Wie ersetzt die Funktion nur das letzte Leerzeichen? Das ist der kluge Teil.
Schnall dich an, die Erklärung wird ein bisschen technisch.
Der Schlüssel zu dieser Formel ist dieses Bit:
SUBSTITUTE(B4," ","*",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))
Welches macht das eigentliche Ersetzen des letzten Leerzeichens durch "*".
SUBSTITUTE hat ein viertes (optionales) Argument, das angibt, welche "Instanz" des Suchtextes ersetzt werden soll. Wenn für dieses Argument nichts angegeben wird, werden alle Instanzen ersetzt. Wenn jedoch beispielsweise die Nummer 2 angegeben wird, wird nur die zweite Instanz ersetzt. Im obigen Snippet wird die Instanz mit dem zweiten SUBSTITUT berechnet:
LEN(B4)-LEN(SUBSTITUTE(B4," ",""))
Hier wird die Länge des Namens ohne Leerzeichen von der tatsächlichen Länge des Namens abgezogen. Wenn der Name nur ein Leerzeichen enthält, wird 1 erzeugt. Wenn zwei Leerzeichen vorhanden sind, ist das Ergebnis 2 und so weiter.
Im Beispielnamen in B4 gibt es zwei Leerzeichen im Namen, also erhalten wir:
15 - 13 = 2
Und zwei wird wie in der Instanznummer verwendet:
SUBSTITUTE(B4," ","*",2)
Dies ersetzt das zweite Leerzeichen durch "*". Der Name sieht dann so aus:
"Susan Ann * Chang"
Die FIND-Funktion übernimmt dann, um herauszufinden, wo sich das "*" im Namen befindet:
FIND("*", "Susan Ann*Chang")
Das Ergebnis ist 10 (das * steht an der 10. Stelle), das von der Gesamtlänge des Namens abgezogen wird:
LEN(B4)-10
Da der Name 15 Zeichen hat, haben wir:
15-10 = 5
Die Nummer 5 wird von RIGHT wie folgt verwendet:
=RIGHT(B4,5)
Was zu "Chang" führt
Wie Sie sehen, ist es eine Menge Arbeit, diese einfachen 5 zu berechnen!
Umgang mit inkonsistenten Leerzeichen
Zusätzliche Leerzeichen verursachen Probleme mit dieser Formel. Eine Lösung besteht darin, zuerst die TRIM-Funktion zum Bereinigen und dann die Parsing-Formel zu verwenden.
