Ersten, mittleren und Nachname Namen aus Vollständiger Name in Excel extrahieren
Angenommen, Sie haben eine Liste von Benutzernamen. Nun möchten Sie den vollständigen Namen in separate Spalten für Vorname, mittleren Namen und Nachname aufteilen – wie im folgenden Screenshot dargestellt. In diesem Artikel stelle ich einige Formeln vor, mit denen Sie diese Aufgabe in Excel erledigen können.
- Vorname aus Vollständiger Name in Excel extrahieren oder abrufen
- Nachname aus Vollständiger Name in Excel extrahieren oder abrufen
- Zweiter Vorname aus Vollständiger Name in Excel extrahieren oder abrufen
- Ersten, mittleren und Nachname Namen aus Vollständiger Name mit einer beeindruckenden Funktion extrahieren
Vorname aus Vollständiger Name in Excel extrahieren oder abrufen
Um den Vornamen aus dem vollständigen Namen zu extrahieren, helfen Ihnen die LINKS- und SUCHEN-Funktionen. Die allgemeine Syntax lautet:
- name: Der vollständige Name oder die Zellreferenz, aus der Sie den Vornamen extrahieren möchten.
Kopieren oder geben Sie die folgende Formel in eine leere Zelle ein, in der Sie das Ergebnis erhalten möchten:
Ziehen Sie anschließend den Ausfüllknauf nach unten auf die Zellen, auf die Sie diese Formel anwenden möchten. Alle Vornamen wurden wie im folgenden Screenshot gezeigt extrahiert:
Erläuterung der Formel:
SUCHEN(„ ";A2)-1: Diese SUCHEN-Funktion ermittelt die Position des ersten Leerzeichens. Durch die Subtraktion von 1 wird sichergestellt, dass die gewünschte Zeichenanzahl um eins kleiner ist als die Position des Leerzeichens. Das Ergebnis lautet: 5.
LINKS(A2;SUCHEN(„ ";A2)-1)= LEFT(„Jerry T. Stone",[[PH_89]]): Diese LINKS-Funktion extrahiert die ersten 5 Zeichen des vollständigen Namens „Jerry T. Stone“ von links – genau die Anzahl, die die SUCHEN-Funktion liefert.
Nachname aus Vollständiger Name in Excel extrahieren oder abrufen
Um den Nachnamen aus dem vollständigen Namen zu extrahieren, unterstützt Sie die Kombination der Funktionen RECHTS, LÄNGE, FINDEN und WECHSELN. Die allgemeine Syntax lautet:
- nameDer vollständige Name oder die Zellreferenz, aus der Sie den Nachnamen extrahieren möchten.
Kopieren oder geben Sie die folgende Formel in eine leere Zelle ein, in der Sie den Nachname erhalten möchten:
Ziehen Sie anschließend den Ausfüllknauf nach unten, um die Formel auf weitere Zellen zu übertragen. Alle Nachnamen wurden aus dem vollständigen Namen extrahiert – siehe Screenshot:
Erläuterung der Formel:
1. LÄNGE(A2)-LÄNGE(WECHSELN(A2;" ";„")): Dieser Teil ermittelt die Anzahl der Leerzeichen in Zelle A2.
- LÄNGE(A2): Diese LÄNGE-Funktion gibt die Gesamtanzahl der Zeichen in Zelle A2 zurück – das Ergebnis lautet: 14.
- WECHSELN(A2;" ";„"): Diese WECHSELN-Funktion ersetzt alle Leerzeichen durch nichts – das Ergebnis lautet: „JerryT.Stone“.
- LÄNGE(WECHSELN(A2;" ";„")): Ermittelt die Gesamtlänge des vollständigen Namens in Zelle A2 ohne Leerzeichen.
- LÄNGE(A2)-LÄNGE(WECHSELN(A2;"-";„")): Subtrahiert die Länge des vollständigen Namens ohne Leerzeichen von der Gesamtlänge des vollständigen Namens, um die Anzahl der Leerzeichen zu ermitteln. Das Ergebnis ist 2.
2. WECHSELN(A2;" ";„*";LÄNGE(A2)-LÄNGE(WECHSELN(A2;" ";„")))= SUBSTITUTE(A2," ",„*",[[PH_94]]): Diese WECHSELN-Funktion ersetzt das letzte Vorkommen des Leerzeichens – ermittelt durch den ersten Formelteil – durch ein *-Zeichen. Das Ergebnis lautet: „Jerry T.*Stone“.
3. FINDEN(„*";WECHSELN(A2;" ";„*";LÄNGE(A2)-LÄNGE(WECHSELN(A2;" ";„"))))= FIND("*",„Jerry T.*Stone" ): Die FINDEN-Funktion gibt die Position des *-Zeichens in der Textzeichenfolge zurück, die von der WECHSELN-Funktion geliefert wurde. Das Ergebnis ist die Zahl 9.
4. LÄNGE(A2)-FINDEN(„*";WECHSELN(A2;" ";„*";LÄNGE(A2)-LÄNGE(WECHSELN(A2;" ";„")))) = LÄNGE(A2)-9: Dieser Teil der Formel ermittelt, wie viele Zeichen nach dem letzten Leerzeichen stehen. Das Ergebnis ist die Zahl 5 – und genau dieser Wert wird als Argument „Anzahl_Zeichen“ in der RECHTS-Funktion verwendet.
5. RECHTS(A2;LÄNGE(A2)-FINDEN(„*";WECHSELN(A2;" ";„*";LÄNGE(A2)-LÄNGE(WECHSELN(A2;" ";„")))))=RECHTS(A2; 5): Schließlich extrahiert die RECHTS-Funktion die letzten 5 Zeichen der Textzeichenfolge in Zelle A2 – genau die Anzahl, die die Formel aus Schritt 4 liefert.
Hinweis: Wenn Ihre Liste von Vollständiger Name nur aus Vor- und Nachname besteht, können Sie die folgende einfache Formel verwenden, um den Nachname zu erhalten, den Sie benötigen:
Zweiter Vorname aus Vollständiger Name in Excel extrahieren oder abrufen
Müssen Sie den zweiten Vornamen aus dem vollständigen Namen extrahieren, verwenden Sie diese Formel, die mithilfe der Funktionen MITTE und SUCHEN erstellt wurde. Die allgemeine Syntax lautet:
- nameDer vollständige Name oder die Zellreferenz, aus der Sie den zweiten Vornamen extrahieren möchten.
Geben oder kopieren Sie die folgende Formel in eine leere Zelle ein, um das Ergebnis auszugeben:
Ziehen Sie anschließend den Ausfüllkästchen nach unten auf die Zellen, um diese Formel anzuwenden, und alle Zweiter Vorname werden ebenfalls extrahiert; siehe Screenshot:
Erläuterung der Formel:
Um den zweiten Vornamen zu extrahieren, ermitteln Sie zunächst die Position der beiden Leerzeichen im vollständigen Namen und wenden dann die MID-Funktion an, um den Text zwischen diesen Stellen zu extrahieren.
1. SEARCH(„ ", A2) + 1: Diese SEARCH-Funktion ermittelt die Position des ersten Leerzeichens. Durch das Hinzufügen von 1 beginnt die Extraktion beim darauffolgenden Zeichen – in diesem Fall ergibt sich die Zahl 7. Dieser Wert wird als Argument „start_num“ in der MID-Funktion verwendet.
2. SEARCH(„ ", A2, SEARCH(„ ", A2)+1) - SEARCH(„ ", A2)-1: Dieser Teil dient als Argument „num_chars“ innerhalb der MID-Funktion.
- SUCHEN(„ "; A2; SUCHEN(„ "; A2)+1): Dieser Teil der Formel ermittelt die Position des zweiten Leerzeichens – das Ergebnis ist die Zahl 9.
- SUCHEN(„ "; A2): Diese SUCHEN-Funktion ermittelt die Position des ersten Leerzeichens in Zelle A2 – das Ergebnis ist die Zahl 6.
- SUCHEN(„ "; A2; SUCHEN(„ "; A2)+1) - SUCHEN(„ "; A2)-1=9-6-1: Subtrahiert die Position des ersten Leerzeichens von der Position des zweiten Leerzeichens und zieht anschließend 1 vom Ergebnis ab, um ein führendes Leerzeichen zu entfernen. Das Ergebnis lautet 2.
3. MID(A2, SEARCH(„ ", A2) + 1, SEARCH(„ ", A2, SEARCH(„ ", A2)+1) - SEARCH(„ ", A2)-1)=MID(A2,7,2): Schließlich extrahiert die MID-Funktion zwei Zeichen aus der Mitte der Zelle A2 – beginnend beim siebten Zeichen.
Hinweis: Wenn im Vollständiger Name nur Vorname und Nachname vorhanden sind, liefert die obige Formel einen Fehlerwert, wie im folgenden Screenshot gezeigt:
Um dieses Problem zu beheben, betten Sie die obige Formel einfach in die IFERROR-Funktion ein – verwenden Sie dazu folgende Formel:
Extrahieren oder erhalten Sie Vorname, Mittelname und Nachname aus Vollständiger Name mit einer beeindruckenden Funktion
Wenn Ihnen die oben genannten Formeln Kopfschmerzen bereiten, empfehle ich Ihnen hier ein einfaches Tool –Kutools für Excel, mit dessen Namen aufteilen-Funktion Sie alle Vornamen, mittleren Namen und Nachname gleichzeitig in separate Zellen extrahieren können.Klicken Sie hier, um Kutools für Excel kostenlos zur Testversion herunterzuladen!
Verwendete verwandte Funktionen:
- LEFT:
- Die LINKS-Funktion extrahiert die angegebene Anzahl von Zeichen vom Anfang der übergebenen Zeichenfolge.
- RIGHT:
- Die RECHTS-Funktion extrahiert eine bestimmte Anzahl von Zeichen von der rechten Seite einer Textzeichenfolge.
- MID:
- Die MITTE-Funktion dient dazu, eine bestimmte Anzahl von Zeichen aus der Mitte einer vorgegebenen Textzeichenfolge zu extrahieren und zurückzugeben.
- LEN:
- Die LÄNGE-Funktion gibt die Anzahl der Zeichen in einer Textzeichenfolge zurück.
- FIND:
- Die FINDEN-Funktion sucht eine Zeichenfolge innerhalb einer anderen und gibt die Startposition der gesuchten Zeichenfolge zurück.
- SEARCH:
- Die SUCHEN-Funktion hilft Ihnen, die Position eines bestimmten Zeichens oder Teilstrings innerhalb der angegebenen Textzeichenfolge zu ermitteln.
- SUBSTITUTE:
- Die Excel-Funktion WECHSELN ersetzt Text oder Zeichen innerhalb einer Textzeichenfolge durch einen anderen Text bzw. andere Zeichen.
Weitere Artikel:
- Ersten Anfangsbuchstaben und Nachname aus Vollständiger Name extrahieren
- Angenommen, Sie haben eine Liste mit vollständigen Namen. Nun möchten Sie – wie in den folgenden Screenshots gezeigt – den ersten Buchstaben des Vornamens und den Nachnamen bzw. den Vornamen sowie das erste Zeichen des Nachnamens aus dem vollständigen Namen extrahieren. Wie können Sie diese Aufgaben in einer Excel-Arbeitsmappe bewältigen?
- Ersten und Nachname in Excel-Liste umkehren oder vertauschen
- Wenn Sie eine Liste mit vollständigen Namen haben, die im Format „Nachname, Vorname“ vorliegt, und diese so umstellen möchten, dass der Vorname an erster und der Nachname an letzter Position steht – wie im folgenden Screenshot gezeigt –, stellen wir Ihnen in diesem Artikel einige praktische Excel-Formeln vor, mit denen Sie diese Aufgabe mühelos erledigen können.
- Mehrere Zeilen aus einer Zelle extrahieren
- Wenn Sie eine Liste von Textzeichenfolgen haben, die durch Zeilenumbrüche getrennt sind (entstanden durch Drücken von Alt + Enter beim Eingeben des Texts), und diese Textzeilen wie im folgenden Screenshot gezeigt in mehrere Zellen extrahieren möchten – wie lässt sich das mithilfe einer Formel in Excel umsetzen?
- Das n-te Wort aus einer Textzeichenfolge in Excel extrahieren
- Wenn Sie eine Liste von Textzeichenfolgen oder Sätzen haben und das spezifische n-te Wort daraus extrahieren möchten – wie im folgenden Screenshot gezeigt –, stellen wir in diesem Artikel einige effektive Methoden zur Lösung dieser Aufgabe in Excel vor.
Die besten Office-Produktivitätswerkzeuge
Kutools für Excel – Hilft Ihnen, sich von der Masse abzuheben
Kutools für Excel bietet über 300 Funktionen und stellt sicher, dass das, was Sie benötigen, nur einen Klick entfernt ist...
Office Tab – Aktiviert tabbasiertes Lesen und Bearbeiten in Microsoft Office (inklusive Excel)
- Mit einem Klick zwischen Dutzenden geöffneter Dokumente wechseln!
- Reduziert täglich Hunderte Mausklicks für Sie – verabschieden Sie sich von der Maus-Hand.
- Steigert Ihre Produktivität um 50 %, wenn Sie mehrere Dokumente gleichzeitig anzeigen und bearbeiten.
- Bringt effiziente Tabs in Office (inklusive Excel) – genau wie bei Chrome, Edge und Firefox.