Extrahieren Sie Vor- und Nachnamen aus dem vollständigen Namen in Excel
Angenommen, Sie haben eine Liste mit Benutzernamen. Jetzt möchten Sie die vollständigen Namen in getrennten Spalten in Vor-, Mittel- und Nachnamen aufteilen, wie im folgenden Screenshot gezeigt. In diesem Artikel werde ich einige Formeln für den Umgang mit diesem Job in Excel vorstellen.
- Extrahieren oder erhalten Sie Vornamen aus vollständigen Namen in Excel
- Extrahieren oder erhalten Sie Nachnamen aus vollständigen Namen in Excel
- Extrahieren oder erhalten Sie Zweitnamen aus vollständigen Namen in Excel
- Extrahieren oder erhalten Sie Vor-, Mittel- und Nachnamen aus vollständigen Namen mit einer erstaunlichen Funktion
Extrahieren oder erhalten Sie Vornamen aus vollständigen Namen in Excel
Um die Vornamen aus vollständigen Namen zu extrahieren oder zu erhalten, können Ihnen die Funktionen LEFT und SEARCH helfen. Die generische Syntax lautet:
- name: Der vollständige Name oder die Zellreferenz, aus der Sie zuerst extrahieren möchten.
Bitte kopieren Sie die folgende Formel oder geben Sie sie in eine leere Zelle ein, in der Sie das Ergebnis erhalten möchten:
Ziehen Sie dann den Füllpunkt nach unten auf die Zellen, auf die Sie diese Formel anwenden möchten. Alle Vornamen wurden wie im folgenden Screenshot gezeigt extrahiert:
Erklärung der Formel:
SUCHE ("", A2) -1: Diese SEARCH-Funktion wird verwendet, um die Position des ersten Leerzeichens zu ermitteln. 1 subtrahieren bedeutet, dass die Anzahl der gewünschten Zeichen um eins geringer ist als die Position des Leerzeichens. Dies ergibt das Ergebnis: 5.
LINKS (A2, SUCHE ("", A2) -1) = LINKS ("Jerry T. Stone", 5): Diese LEFT-Funktion extrahiert den Text im vollständigen Namen „Jerry T. Stone“ von der linken Seite mit 5 Zeichen, die von der SEARCH-Funktion zurückgegeben werden.
Extrahieren oder erhalten Sie Nachnamen aus vollständigen Namen in Excel
Um die Nachnamen aus den vollständigen Namen zu extrahieren, kann Ihnen die Kombination der Funktionen RIGHT, LEN, FIND, LEN und SUBSTITUTE einen Gefallen tun. Die generische Syntax lautet:
- name: Der vollständige Name oder die Zellreferenz, aus der Sie den Nachnamen extrahieren möchten.
Bitte kopieren Sie die folgende Formel oder geben Sie sie in eine leere Zelle ein, in der Sie die Nachnamen erhalten möchten:
Ziehen Sie dann den Füllpunkt nach unten in die Zellen, um diese Formel zu füllen. Alle Nachnamen wurden aus den vollständigen Namen extrahiert (siehe Abbildung):
Erklärung der Formel:
1. LEN (A2) -LEN (ERSATZ (A2, "", "")): Dieser Teil wird verwendet, um die Anzahl der Leerzeichen in Zelle A2 abzurufen.
- LEN (A2): Diese LEN-Funktion gibt die Gesamtzahl der Zeichen in Zelle A2 zurück. Es wird zurückkehren: 14.
- ERSATZ (A2, "", ""): Diese SUBSTITUTE-Funktion wird verwendet, um alle Leerzeichen durch nichts zu ersetzen. Und Sie erhalten das Ergebnis wie folgt: „JerryT.Stone".
- LEN (ERSATZ (A2, "", ""): Ermitteln Sie die Gesamtlänge des vollständigen Namens in Zelle A2 ohne Leerzeichen.
- LEN (A2) -LEN (ERSATZ (A2, "-", "")): Subtrahieren Sie die Länge des vollständigen Namens ohne Leerzeichen von der gesamten Länge des vollständigen Namens, um die Nummer des Leerzeichens zu erhalten. Dies ergibt 2.
2. SUBSTITUT (A2, "", "*", LEN (A2) -LEN (SUBSTITUT (A2, "", "")) = SUBSTITUT (A2, "", "*", 2): Diese SUBSTITUTE-Funktion wird verwendet, um das letzte Vorkommen des von der Formel des ersten Teils zurückgegebenen Leerzeichens durch ein * -Zeichen zu ersetzen. Und Sie erhalten dieses Ergebnis: „Jerry T. * Stone".
3. FIND ("*", SUBSTITUTE (A2, "", "*", LEN (A2) -LEN (SUBSTITUTE (A2, "", ""))) = FIND ("*", "Jerry T. *Stein" ): Die FIND-Funktion gibt die Position des * -Zeichens in der Textzeichenfolge zurück, die von der SUBSTITUTE-Funktion zurückgegeben wird, und Sie erhalten die Nummer 9.
4. LEN (A2) -FIND ("*", SUBSTITUT (A2, "", "*", LEN (A2) -LEN (SUBSTITUT (A2, "", ""))) = LEN (A2) - 9: Dieser Teil der Formel gibt an, wie viele Zeichen nach dem letzten Leerzeichen vorhanden sind. Dies erhält die Nummer 5. Dies wird als num_chars-Argument in der RIGHT-Funktion erkannt.
5. RECHTS (A2, LEN (A2) - FINDEN ("*", ERSATZ (A2, "", "*", LEN (A2) -LEN (ERSATZ (A2, "", "")))) = RECHTS (A2, 5): Zuletzt 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.
Note: Wenn Ihre Liste der vollständigen Namen nur den Vor- und Nachnamen enthält, können Sie die folgende einfache Formel anwenden, um den Nachnamen nach Bedarf zu erhalten:
Extrahieren oder erhalten Sie Zweitnamen aus vollständigen Namen in Excel
Wenn Sie die zweiten Vornamen aus den vollständigen Namen extrahieren müssen, wird diese Formel von den Funktionen MID und SEARCH erstellt. Die generische Syntax lautet:
- name: Der vollständige Name oder die Zellreferenz, aus der Sie den zweiten Vornamen extrahieren möchten.
Bitte geben Sie die folgende Formel ein oder kopieren Sie sie in eine leere Zelle, um das Ergebnis auszugeben:
Ziehen Sie dann den Füllpunkt nach unten auf die Zellen, um diese Formel anzuwenden. Alle zweiten Vornamen werden ebenfalls extrahiert (siehe Abbildung):
Erklärung der Formel:
Um den zweiten Vornamen zu extrahieren, müssen Sie zuerst die Position beider Leerzeichen zwischen dem vollständigen Namen ermitteln und dann die MID-Funktion anwenden, um den Text in der Mitte der Textzeichenfolge zu extrahieren.
1. SUCHE ("", A2) + 1: Diese SEARCH-Funktion ermittelt die Position des ersten Leerzeichens und fügt 1 Mittel hinzu, um die Extraktion aus dem nächsten Zeichen zu starten. Dies erhält die Nummer 7. Dieser Teil wird als start_num-Argument innerhalb der MID-Funktion erkannt.
2. SUCHE ("", A2, SUCHE ("", A2) +1) - SUCHE ("", A2) -1: Dieser Teil wird als num_chars-Argument innerhalb der MID-Funktion erkannt.
- SUCHE ("", A2, SUCHE ("", A2) +1): Dieser Teil der Formel wird verwendet, um die Position des zweiten Feldes zu erhalten. Er erhält die Nummer 9.
- SUCHE ("", A2): Diese SEARCH-Funktion ermittelt die Position des ersten Leerzeichens in Zelle A2. Es wird die Nummer 6 bekommen.
- SEARCH ("", A2, SEARCH ("", A2) +1) - SEARCH ("", 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, um ein führendes Leerzeichen zu entfernen. Und das Ergebnis ist 2.
3. MITTE (A2, SUCHE ("", A2) + 1, SUCHE ("", A2, SUCHE ("", A2) +1) - SUCHE ("", A2) -1) = MITTE (A2,7,2 , XNUMX): Zuletzt extrahiert die MID-Funktion 2 Zeichen in der Mitte der Zelle A2, die beim siebten Zeichen beginnt.
Note: Wenn die vollständigen Namen nur Vor- und Nachnamen enthalten, erhält die obige Formel einen Fehlerwert, wie im folgenden Screenshot gezeigt:
Um dieses Problem zu beheben, sollten Sie die obige Formel in die IFERROR-Funktion einschließen. Wenden Sie die folgende Formel an:
Extrahieren oder erhalten Sie Vor-, Mittel- und Nachnamen aus vollständigen Namen mit einer erstaunlichen Funktion
Wenn Sie mit den oben genannten Formeln Schmerzen haben, empfehle ich hier ein einfaches Werkzeug - Kutools for ExcelMit seinen Geteilte Namen Mit dieser Funktion können Sie alle Vor-, Mittel- und Nachnamen gleichzeitig in separate Zellen extrahieren. Klicken Sie hier, um Kutools for Excel kostenlos herunterzuladen!
Verwendete relative Funktionen:
- LEFT:
- Die LEFT-Funktion extrahiert die angegebene Anzahl von Zeichen von der linken Seite einer angegebenen Zeichenfolge.
- RIGHT:
- Mit der RECHTS-Funktion wird eine bestimmte Anzahl von Zeichen von der rechten Seite der Textzeichenfolge extrahiert
- MID:
- Die MID-Funktion wird verwendet, um eine bestimmte Anzahl von Zeichen aus der Mitte einer bestimmten Textzeichenfolge zu finden und zurückzugeben.
- LEN:
- Die LEN-Funktion gibt die Anzahl der Zeichen in einer Textzeichenfolge zurück.
- FIND:
- Die FIND-Funktion wird verwendet, um eine Zeichenfolge in einer anderen Zeichenfolge zu finden, und gibt die Startposition der Zeichenfolge in einer anderen Zeichenfolge zurück.
- SEARCH:
- Mit der SEARCH-Funktion können Sie die Position eines bestimmten Zeichens oder Teilstrings anhand der angegebenen Textzeichenfolge ermitteln
- SUBSTITUTE:
- Die Excel SUBSTITUTE-Funktion ersetzt Text oder Zeichen innerhalb einer Textzeichenfolge durch einen anderen Text oder andere Zeichen.
Weitere Artikel:
- Extrahieren Sie den Vor- und Nachnamen aus dem vollständigen Namen
- Angenommen, Sie haben eine Liste mit vollständigen Namen. Jetzt möchten Sie den Vor- und Nachnamen oder den Vornamen und das erste Zeichen des Nachnamens aus dem vollständigen Namen extrahieren, wie in den folgenden Screenshots gezeigt. Wie können Sie mit diesen Aufgaben in der Excel-Arbeitsmappe umgehen?
- Vor- und Nachnamen in der Excel-Liste spiegeln oder umkehren
- Wenn Sie eine Liste mit vollständigen Namen haben, die als Nachname und Vorname formatiert sind, möchten Sie jetzt den Nach- und Vornamen wie im folgenden Screenshot auf Vor- und Nachname umschalten. In diesem Artikel werde ich einige Formeln für den Umgang mit diesem Job in Excel vorstellen.
- Extrahieren Sie mehrere Zeilen aus einer Zelle
- Wenn Sie eine Liste von Textzeichenfolgen haben, die durch Zeilenumbrüche getrennt sind (dies geschieht durch Drücken von Alt + Eingabetaste bei der Texteingabe), möchten Sie diese Textzeilen jetzt in mehrere Zellen extrahieren, wie im folgenden Screenshot gezeigt. Wie können Sie es mit einer Formel in Excel lösen?
- N-te Wort aus Textzeichenfolge in Excel extrahieren
- Wenn Sie eine Liste mit Textzeichenfolgen oder -sätzen haben, möchten Sie jetzt das spezifische n-te Wort aus der Liste extrahieren, wie im folgenden Screenshot gezeigt. In diesem Artikel werde ich einige Methoden zum Lösen dieses Jobs in Excel vorstellen.
Die besten Tools für die Office-Produktivität
Kutools für Excel - Hilft Ihnen, sich von der Masse abzuheben
Kutools für Excel bietet über 300 Funktionen, Stellen Sie sicher, dass das, was Sie brauchen, nur einen Klick entfernt ist ...
Office-Registerkarte - Aktivieren Sie das Lesen und Bearbeiten von Registerkarten in Microsoft Office (einschließlich Excel).
- Eine Sekunde, um zwischen Dutzenden offener Dokumente zu wechseln!
- Reduzieren Sie jeden Tag Hunderte von Mausklicks für Sie und verabschieden Sie sich von der Maushand.
- Erhöht Ihre Produktivität um 50 % beim Anzeigen und Bearbeiten mehrerer Dokumente.
- Bringt effiziente Tabs in Office (einschließlich Excel), genau wie Chrome, Edge und Firefox.
