Holen Sie sich den Nachnamen aus dem Namen

Wenn Sie den Nachnamen aus einem vollständigen Namen extrahieren müssen, können Sie dies mit dieser ziemlich 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 diese Formel:

wie man eine Excel-Formel schreibt

= RIGHT (name, LEN (name)- FIND ('*', SUBSTITUTE (name,' ','*', LEN (name)- LEN ( SUBSTITUTE (name,' ','')))))

Im Kern verwendet diese Formel die RIGHT-Funktion, um Zeichen von rechts beginnend zu extrahieren. Die anderen Funktionen, die den komplexen Teil dieser Formel bilden, machen nur eines: Sie berechnen, wie viele Zeichen extrahiert werden müssen.

Auf einer höheren Ebene ersetzt die Formel das letzte Leerzeichen im Namen durch ein Sternchen '*' und verwendet dann FIND, um die Position des Sternchens im Namen zu bestimmen. Die Position wird verwendet, um herauszufinden, wie viele Zeichen mit RECHTS extrahiert werden sollen.

Wie ersetzt die Funktion nur das letzte Leerzeichen? Dies ist der clevere Teil.

Schnallen Sie sich an, die Erklärung wird etwas technisch.

Der Schlüssel zu dieser Formel ist dieses Bit:

 
= RIGHT (B4, LEN (B4)- FIND ('*', SUBSTITUTE (B4,' ','*', LEN (B4)- LEN ( SUBSTITUTE (B4,' ','')))))

Was die tatsächliche Ersetzung des letzten Leerzeichens durch '*' bewirkt.

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. Wird jedoch beispielsweise die Zahl 2 geliefert, wird nur die zweite Instanz ersetzt. Im obigen Snippet wird die Instanz mit dem zweiten SUBSTITUTE berechnet:

 
 SUBSTITUTE (B4,' ','*', LEN (B4)- LEN ( SUBSTITUTE (B4,' ','')))

Dabei 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, ergibt sich 1. Wenn es zwei Leerzeichen gibt, 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:

 
 LEN (B4)- LEN ( SUBSTITUTE (B4,' ',''))

wodurch das zweite Leerzeichen durch '*' ersetzt wird. Der Name sieht dann so aus:

'Susan Ann * Chang'

Die FIND-Funktion übernimmt dann, um herauszufinden, wo das '*' im Namen ist:

 
 SUBSTITUTE (B4,' ','*',2)

Das Ergebnis ist 10 (das * steht an 10. Stelle), was von der Gesamtlänge des Namens abgezogen wird:

 
 FIND ('*', 'Susan Ann*Chang')

Da der Name 15 Zeichen lang ist, haben wir:

15-10 = 5

Die Zahl 5 wird von RIGHT so verwendet:

 
 LEN (B4)-10

Was zu 'Chang' führt

Excel-Formel Vorname Nachname

Wie Sie sehen, ist es oben viel Arbeit, diese einfache 5 zu berechnen!

Umgang mit inkonsistenten Leerzeichen

Zusätzliche Leerzeichen führen zu Problemen mit dieser Formel. Eine Lösung ist die Verwendung der TRIM-Funktion Zuerst die Dinge bereinigen, dann die Parsing-Formel verwenden.