Vor-, Mittel- und Nachnamen aus dem vollständigen Namen in Excel extrahieren
Angenommen, Sie haben eine Liste von Benutzernamen und möchten nun die vollständigen Namen in Vor-, Mittel- und Nachnamen in getrennte Spalten aufteilen, wie im folgenden Screenshot gezeigt. In diesem Artikel werde ich einige Formeln vorstellen, um diese Aufgabe in Excel zu bewältigen.
- Vornamen aus vollständigen Namen in Excel extrahieren oder abrufen
- Nachnamen aus vollständigen Namen in Excel extrahieren oder abrufen
- Mittlere Namen aus vollständigen Namen in Excel extrahieren oder abrufen
- Vor-, Mittel- und Nachnamen aus vollständigen Namen mit einer erstaunlichen Funktion extrahieren oder abrufen
Vornamen aus vollständigen Namen in Excel extrahieren oder abrufen
Um Vornamen aus vollständigen Namen zu extrahieren, können Ihnen die Funktionen LINKS und SUCHEN helfen. Die allgemeine Syntax lautet:
- name: Der vollständige Name oder die Zellreferenz, aus der Sie den Vornamen extrahieren möchten.
Bitte kopieren Sie die folgende Formel in eine leere Zelle, wo Sie das Ergebnis erhalten möchten:
Ziehen Sie dann den Ausfüllknauf nach unten in die Zellen, auf die Sie diese Formel anwenden möchten, und alle Vornamen wurden wie im folgenden Screenshot gezeigt extrahiert:
Erklärung der Formel:
SUCHEN(" ";A2)-1: Diese SUCHEN-Funktion wird verwendet, um die Position des ersten Leerzeichens zu erhalten. Subtrahiert man 1, bedeutet dies, dass die Anzahl der Zeichen, die Sie benötigen, um eins kleiner ist als die Position des Leerzeichens. Dies ergibt das Ergebnis: 5.
LINKS(A2;SUCHEN(" ";A2)-1)= LINKS("Jerry T. Stone";5): Diese LINKS-Funktion extrahiert den Text im vollständigen Namen „Jerry T. Stone“ von links mit 5 Zeichen, die von der SUCHEN-Funktion zurückgegeben werden.
Nachnamen aus vollständigen Namen in Excel extrahieren oder abrufen
Um Nachnamen aus vollständigen Namen zu extrahieren, kann die Kombination der Funktionen RECHTS, LÄNGE, FINDEN, LÄNGE und ERSETZEN helfen. Die allgemeine Syntax lautet:
- name: Der vollständige Name oder die Zellreferenz, aus der Sie den Nachnamen extrahieren möchten.
Bitte kopieren Sie die folgende Formel in eine leere Zelle, wo Sie die Nachnamen erhalten möchten:
Und ziehen Sie dann den Ausfüllknauf nach unten in die Zellen, um diese Formel auszufüllen, und alle Nachnamen wurden aus den vollständigen Namen extrahiert, siehe Screenshot:
Erklärung der Formel:
1. LÄNGE(A2)-LÄNGE(ERSETZEN(A2;" ";"")): Dieser Teil wird verwendet, um die Anzahl der Leerzeichen in Zelle A2 zu erhalten.
- LÄNGE(A2): Diese LÄNGE-Funktion gibt die Gesamtanzahl der Zeichen in Zelle A2 zurück. Es wird zurückgegeben: 14.
- ERSETZEN(A2;" ";""): Diese ERSETZEN-Funktion wird verwendet, um alle Leerzeichen durch nichts zu ersetzen. Und Sie erhalten das Ergebnis: “JerryT.Stone”.
- LÄNGE(ERSETZEN(A2;" ";""): Gibt die Gesamtlänge des vollständigen Namens in Zelle A2 ohne Leerzeichen zurück.
- LÄNGE(A2)-LÄNGE(ERSETZEN(A2;"-";"")): Subtrahieren Sie die Länge des vollständigen Namens ohne Leerzeichen von der Gesamtlänge des vollständigen Namens, um die Anzahl der Leerzeichen zu erhalten, und dies ergibt 2.
2. ERSETZEN(A2;" ";"*";LÄNGE(A2)-LÄNGE(ERSETZEN(A2;" ";"")))= ERSETZEN(A2;" ";"*";2): Diese ERSETZEN-Funktion wird verwendet, um das letzte Auftreten des Leerzeichens, das von der ersten Teilsformel zurückgegeben wurde, durch ein *-Zeichen zu ersetzen. Und Sie erhalten dieses Ergebnis: “Jerry T.*Stone”.
3. FINDEN("*";ERSETZEN(A2;" ";"*";LÄNGE(A2)-LÄNGE(ERSETZEN(A2;" ";""))))= FINDEN("*";"Jerry T.*Stone" ): Die FINDEN-Funktion gibt die Position des *-Zeichens in der Textzeichenfolge zurück, die von der ERSETZEN-Funktion zurückgegeben wird, und Sie erhalten die Zahl 9.
4. LÄNGE(A2)-FINDEN("*";ERSETZEN(A2;" ";"*";LÄNGE(A2)-LÄNGE(ERSETZEN(A2;" ";""))))= LÄNGE(A2)-9: Dieser Teil der Formel gibt an, wie viele Zeichen sich nach dem letzten Leerzeichen befinden. Dies ergibt die Zahl 5. Dies wird als num_chars-Argument in der RECHTS-Funktion erkannt.
5. RECHTS(A2;LÄNGE(A2)-FINDEN("*";ERSETZEN(A2;" ";"*";LÄNGE(A2)-LÄNGE(ERSETZEN(A2;" ";"")))))=RECHTS(A2; 5): Schließlich wird die RECHTS-Funktion verwendet, um 5 Zeichen, die von der Formel in Schritt 4 zurückgegeben werden, von der rechten Seite der Textzeichenfolge in Zelle A2 zu extrahieren.
Hinweis: Wenn Ihre Liste vollständiger Namen nur Vornamen und Nachnamen enthält, können Sie die folgende einfache Formel anwenden, um den Nachnamen zu erhalten:
Mittlere Namen aus vollständigen Namen in Excel extrahieren oder abrufen
Wenn Sie die mittleren Namen aus den vollständigen Namen extrahieren müssen, hilft Ihnen diese Formel, die mit den Funktionen TEIL und SUCHEN erstellt wurde. Die allgemeine Syntax lautet:
- name: Der vollständige Name oder die Zellreferenz, aus der Sie den mittleren Namen extrahieren möchten.
Bitte geben Sie die folgende Formel in eine leere Zelle ein, um das Ergebnis auszugeben:
Ziehen Sie dann den Ausfüllknauf nach unten in die Zellen, um diese Formel anzuwenden, und alle mittleren Namen werden ebenfalls extrahiert, siehe Screenshot:
Erklärung der Formel:
Um den mittleren Namen zu extrahieren, sollten Sie zuerst die Position beider Leerzeichen zwischen dem vollständigen Namen finden und dann die TEIL-Funktion anwenden, um den Text in der Mitte der Textzeichenfolge zu extrahieren.
1. SUCHEN(" "; A2) + 1: Diese SUCHEN-Funktion erhält die Position des ersten Leerzeichens, das Hinzufügen von 1 bedeutet, die Extraktion vom nächsten Zeichen zu beginnen. Dies ergibt die Zahl 7. Dieser Teil wird als start_num-Argument innerhalb der TEIL-Funktion erkannt.
2. SUCHEN(" "; A2; SUCHEN(" "; A2)+1) - SUCHEN(" "; A2)-1: Dieser Teil wird als num_chars-Argument innerhalb der TEIL-Funktion erkannt.
- SUCHEN(" "; A2; SUCHEN(" "; A2)+1): Dieser Teil der Formel wird verwendet, um die Position des zweiten Leerzeichens zu erhalten, es ergibt die Zahl 9.
- SUCHEN(" "; A2): Diese SUCHEN-Funktion erhält die Position des ersten Leerzeichens in Zelle A2. Es ergibt die Zahl 6.
- SUCHEN(" "; A2; SUCHEN(" "; A2)+1) - SUCHEN(" "; A2)-1=9-6-1: Subtrahieren Sie die Position des ersten Leerzeichens von der Position des zweiten Leerzeichens und subtrahieren Sie dann 1 vom Ergebnis, was bedeutet, ein führendes Leerzeichen zu entfernen. Und das Ergebnis ist 2.
3. TEIL(A2; SUCHEN(" "; A2) + 1; SUCHEN(" "; A2; SUCHEN(" "; A2)+1) - SUCHEN(" "; A2)-1)=TEIL(A2;7;2): Schließlich extrahiert die TEIL-Funktion 2 Zeichen in der Mitte von Zelle A2, die am siebten Zeichen beginnen.
Hinweis: Wenn es in den vollständigen Namen nur Vor- und Nachnamen gibt, wird die obige Formel einen Fehlerwert wie im folgenden Screenshot gezeigt zurückgeben:
Um dieses Problem zu beheben, sollten Sie die obige Formel in die WENNFEHLER-Funktion einfügen, bitte wenden Sie die folgende Formel an:
Vor-, Mittel- und Nachnamen aus vollständigen Namen mit einer erstaunlichen Funktion extrahieren oder abrufen
Wenn Sie Schwierigkeiten mit den obigen Formeln haben, empfehle ich hier ein einfaches Tool - Kutools für Excel. Mit seiner Funktion Namen aufteilen können Sie alle Vornamen, Mittelnamen und Nachnamen auf einmal in separate Zellen extrahieren. Klicken Sie hier, um Kutools für Excel kostenlos zu testen!
Verwandte verwendete Funktionen:
- LINKS:
- Die LINKS-Funktion extrahiert die angegebene Anzahl von Zeichen von der linken Seite einer bereitgestellten Zeichenfolge.
- RECHTS:
- Die RECHTS-Funktion wird verwendet, um eine bestimmte Anzahl von Zeichen von der rechten Seite der Textzeichenfolge zu extrahieren.
- TEIL:
- Die TEIL-Funktion wird verwendet, um eine bestimmte Anzahl von Zeichen aus der Mitte einer gegebenen Textzeichenfolge zu finden und zurückzugeben.
- LÄNGE:
- Die LÄNGE-Funktion gibt die Anzahl der Zeichen in einer Textzeichenfolge zurück.
- FINDEN:
- Die FINDEN-Funktion wird verwendet, um eine Zeichenfolge innerhalb einer anderen Zeichenfolge zu finden und gibt die Startposition der Zeichenfolge innerhalb einer anderen zurück.
- SUCHEN:
- Die SUCHEN-Funktion kann Ihnen helfen, die Position eines bestimmten Zeichens oder Teilstrings aus der gegebenen Textzeichenfolge zu finden.
- ERSETZEN:
- Die Excel-ERSETZEN-Funktion ersetzt Text oder Zeichen innerhalb einer Textzeichenfolge durch einen anderen Text oder andere Zeichen.
Weitere Artikel:
- Ersten Buchstaben und Nachnamen aus dem vollständigen Namen extrahieren
- Angenommen, Sie haben eine Liste vollständiger Namen und möchten nun den ersten Buchstaben und den Nachnamen oder den Vornamen und den ersten Buchstaben des Nachnamens aus dem vollständigen Namen extrahieren, wie in den folgenden Screenshots gezeigt. Wie könnten Sie diese Aufgaben in einer Excel-Arbeitsmappe bewältigen?
- Vor- und Nachnamen in einer Excel-Liste umkehren
- Wenn Sie eine Liste vollständiger Namen haben, die als Nachname und Vorname formatiert sind, möchten Sie nun die Nach- und Vornamen so umkehren, dass sie als Vor- und Nachname erscheinen, wie im folgenden Screenshot gezeigt. In diesem Artikel werde ich einige Formeln vorstellen, um diese Aufgabe in Excel zu bewältigen.
- Mehrere Zeilen aus einer Zelle extrahieren
- Wenn Sie eine Liste von Textzeichenfolgen haben, die durch Zeilenumbrüche getrennt sind (die durch Drücken der Tasten Alt + Enter beim Eingeben des Textes entstehen), und Sie möchten diese Zeilen von Text in mehrere Zellen extrahieren, wie im folgenden Screenshot gezeigt. Wie könnten Sie dies mit einer Formel in Excel lösen?
- N-tes Wort aus einer Textzeichenfolge in Excel extrahieren
- Wenn Sie eine Liste von Textzeichenfolgen oder Sätzen haben, möchten Sie nun das spezifische n-te Wort aus der Liste extrahieren, wie im folgenden Screenshot gezeigt. In diesem Artikel werde ich einige Methoden zur Lösung dieser Aufgabe in Excel vorstellen.
Die besten Tools zur Büroproduktivität
Kutools für Excel - Hilft Ihnen, aus der Menge hervorzustechen
Kutools für Excel bietet über 300 Funktionen, sodass das, was Sie benötigen, nur einen Klick entfernt ist...
Office Tab - Aktiviert tabbed Lesen und Bearbeiten in Microsoft Office (inklusive Excel)
- In einer Sekunde zwischen Dutzenden offener Dokumente wechseln!
- Reduziert Hunderte von Mausklicks für Sie jeden Tag, verabschieden Sie sich von der Maushand.
- Steigert Ihre Produktivität um 50 % beim Anzeigen und Bearbeiten mehrerer Dokumente.
- Bringt effiziente Tabs ins Office (inklusive Excel), genau wie bei Chrome, Edge und Firefox.