Du bist hier::--Excel Formeln – Vorname und Nachname trennen

Excel Formeln – Vorname und Nachname trennen

In diesem Artikel werde ich ihnen zeigen, wie man es schafft aus einer Excel Zelle, die einen Namen enthält daraus die Vornamen und den Nachnamen zu erhalten. Titel werde ebenfalls mit berücksichtigt.

Sie haben in Excel eine Zelle, die einen kompletten Namen enthält. Dieser hat mehrere Vornamen, einen Doktortitel und einen Adelstite. Unmöglich mit einer Formel den Namen aufzuteilen? Nein. Ich werde ihnen nun zeigen, wie das geht.

Ein Vorname:

Zu Beginn werde ich mich um den Standardfall kümmern, dass die Person einen Vornamen und einen Nachnamen besitzt. Den kompletten Namen schreiben Sie bitte in die Zelle A1. In der Zelle B1 folgt nun die Ausgabe des Vornamens. Selbstverständlich benötigen Sie dafür eine Formel. Schreiben Sie in die Zelle B1 also nun folgendes:

=LINKS(A1;FINDEN(" ";A1))

Da dies keine Matrixformel ist, wird sofort nachdem Sie die Formel eingegeben haben der korrekte Vorname angezeigt. Gegebenenfalls müssen Sie die Formel mit der Enter Taste noch bestätigen.

(Zur Darstellung in Originalgröße Abbildungen anklicken)
01-excel-formeln-vorname-und-nachname-trennen-vorname-470.png?nocache=1308580669545

Vorname basierend auf dem Nachnamen:

Nun werde ich ihnen eine zweite Möglichkeit zeigen den Vornamen auszulesen. Diese geht im Gegensatz zu der ersten auch mit mehreren Vornamen oder Adelstiteln korrekt um. Das kommt aber daher, da Formel den Vornamen aus der Zelle A1 nicht direkt ausliest, sondern noch mit dem Nachnamen vergleicht. Eine Anwesenheit des Nachnamens in Zelle C1 ist also für diese Formel Pflicht. Sie lautet wie folgt:

=GLÄTTEN(LINKS(A1;LÄNGE(A1)-LÄNGE(C1)))

Haben Sie diese Formel bei ihnen nun eingegeben, so werden Sie hier keinen Vornamen mehr sehen, da der Nachname ja noch nicht explizit in der Zelle C1 angezeigt wurde. Angezeigt wird hier nun der komplette Name, wie das aber richtig gestellt wird, erfahren Sie im übernächsten Schritt

02-excel-formeln-vorname-und-nachname-trennen-vorname-anders-470.png?nocache=1308580694273

Nachname basierend auf dem Vornamen:

Die Formel die jetzt erklärt wird, darf nicht mit der Formel aus dem vorherigen Schritt kombiniert werden. Diese Formel nimmt nämlich auch Bezug auf den Vornamen, wodurch sich ein „Kreis“ in der Formel ergeben würde und folglich kein Ergebnis angezeigt werden kann. Benutzen Sie diese Formel also nur mit der Vornamenformel aus dem ersten Schritt. Die jetzt schon angesprochene Formel heißt wie folgt und muss in die Zelle C1 eingegeben werden:

=RECHTS(A1;LÄNGE(A1)-LÄNGE(B1))

Haben Sie die richtige Formel im Vornamenfeld stehen, so wird hier nun ganz korrekt der Nachname angezeigt. Haben Sie die falsche Formel in der Zelle B1, so wird nun ein Fehler zur „Zirkelbezugswarnung“ angezeigt.

03-excel-formeln-vorname-und-nachname-trennen-nachname-470.png?nocache=1308580711498

Nachname als Matrixformel:

Desweiteren haben Sie die Möglichkeit den Nachnamen mit einer Matrixformel darzustellen. Diese reagiert auch auf Namenszusätze, wie Adelstitel. Wählen Sie diese Art der Anzeige, so schreiben Sie in die Zelle C1 nun folgendes:

=WENN(ANZAHL(FINDEN({" zu ";" von ";" ob ";" de ";" van ";" auf ";" vom "};A1));TEIL(A1;VERWEIS(99;FINDEN({" zu ";" von ";" ob ";" de ";" van ";" auf ";" vom "};A1))+1;99);TEIL(A1;MAX((TEIL(A1;SPALTE($1:$1);1)=" ")*SPALTE($1:$1))+1;99))

Da das eine Matrixformel ist, kann sie nicht einfach durch eine Druck der Enter Taste bestätigt werden. Um sie richtig auszuführen, müssen Sie nun die Zelle C1 nochmal anklicken. Anschließend klicken Sie oben in die Bearbeitungszeile der Formel. Jetzt drücken Sie gleichzeitig die Tasten STRG+SHIFT+Enter auf ihrer Tastatur, wodurch nun in der Zelle der richtige Wert angezeigt wird. Doe Formel hat sich außerdem in folgende verwandelt:

{=WENN(ANZAHL(FINDEN({" zu ";" von ";" ob ";" de ";" van ";" auf ";" vom "};A1));TEIL(A1;VERWEIS(99;FINDEN({" zu ";" von ";" ob ";" de ";" van ";" auf ";" vom "};A1))+1;99);TEIL(A1;MAX((TEIL(A1;SPALTE($1:$1);1)=" ")*SPALTE($1:$1))+1;99))}

04-excel-formeln-vorname-und-nachname-trennen-nachname-anders-470.png?nocache=1308580731250

Zwei Vornamen, Adelstitel oder akademische Grade:

Möchten Sie nun, dass zwei Vornamen auch richtig aufgeteilt werden, oder auch ein akademischer Grad erfolgreich mit einbezogen wird, so benötigen Sie dafür natürlich wieder eine neue Formel. Diesmal kommen Sie aber ohne Matrixformel aus, es reicht also wenn Sie in die Zelle B1 einfach folgendes eingeben:

=WENN(ODER(ISTFEHLER(FINDEN(" ";A1));CODE(LINKS(A1;1))>96);"";WENN(UND(ISTFEHLER(FINDEN("ob ";A1));ISTFEHLER(FINDEN("von ";A1));ISTFEHLER(FINDEN("zu ";A1))); WENN(UND(CODE(TEIL(A1;FINDEN(" ";A1)+1;1)) < 97;LÄNGE(A1)-LÄNGE(WECHSELN(A1;" ";)) > 1);TEIL(A1;1;FINDEN(" ";A1;FINDEN(" ";A1)+1));TEIL(A1;1;FINDEN(" ";A1)));LINKS(A1; WENN(NICHT(ISTFEHLER(FINDEN("von ";A1)));FINDEN("von ";A1);WENN(NICHT(ISTFEHLER(FINDEN("zu ";A1)));FINDEN("zu ";A1);FINDEN("ob ";A1)))-1)))

Nachdem Sie die Formel eingegeben haben, wird ihnen nun sofort der Vorname angezeigt. Sofern Sie alles richtig gemacht haben, wird dieser auch stimmen.

05-excel-formeln-vorname-und-nachname-trennen-vorname-titel-470.png?nocache=1308580749929

Ausländische Namenszusätze:

Hat man nun aber einen nicht bekannten Namenszusatz (bekannte Namenszusätze sind: „zu“, „von“, „ob“, „de“, „van“, „auf“ und „vom“), so wird die Matrixformel in der Zelle C1 nicht funktionieren. Ich habe hier als Beispiel den Namen „Max der Mustermänner“ gewählt, welcher mit der Matrixformel nicht erkannt wurde. Wie man mit solchen Namen aber trotzdem umgehen kann, erfahren Sie im nächsten Schritt.

06-excel-formeln-vorname-und-nachname-trennen-vorname-ausland-falsch-470.png?nocache=1308580770778 

Da die Matrixformel für solche Fälle ja nicht funktioniert, benötigen Sie nun eine andere. Nehmen Sie nun für die Zelle C1 diese Formel:

=RECHTS(A1;LÄNGE(A1)-LÄNGE(B1))

Diese Formel wurde oben schon einmal angesprochen und ich habe erklärt, dass sie auf dem Vornamen basiert. Genau das ist jetzt ein Beispiel, in welchem so etwas gebraucht wird. Haben Sie die Formel eingegeben, so wird nämlich umgehend, der richtige Nachname angezeigt.

07-excel-formeln-vorname-und-nachname-trennen-vorname-ausland-richtig-470.png?nocache=1308580790930 

Nun wissen Sie, wie man einen Namen in Vor- und Nachnamen aufteilt, auch wenn mehrere Vornamen oder Adelstitel und Namenszusätze vorkommen. Auch das „Problem“ mit den ausländischen Namen haben wir gelöst.

Von |2018-07-25T14:23:01+00:00Juni 22nd, 2011|Kategorien: Tabellenkalkulation|0 Kommentare

Über den Autor:

Hinterlassen Sie einen Kommentar