Wie Extrahieren ersten, mittlere und Nachname von einer Zelle in separaten Zellen in Excel
In diesem Artikel zeigen wir Ihnen, wie Sie mit mehreren Methoden in Microsoft Excel Vor-, Mittel- und Nachnamen aus einer bestimmten Textzeichenfolge extrahieren.
Oft sehen Sie sich einer Situation gegenüber, in der Sie eine Namensliste mit vollständigen Namen erhalten und diese anhand des Vor-, Mittel- und Nachnamens trennen und das Ergebnis dann in verschiedene Zellen zurückgeben müssen. Dies ist ein sehr häufiges Problem für jeden Excel-Benutzer, und auch Sie müssen darauf gestoßen sein.
Dies ist eine sehr häufige Belastung für viele Excel-Benutzer, aber es gibt keine spezielle integrierte Funktion in Excel, mit der Vorname, mittlerer Vor- und Nachname aus einer Zelle extrahiert werden können, die einen vollständigen Namen enthält.
Es gibt jedoch mehrere Methoden in Excel, die Ihnen dabei helfen können, dasselbe zu tun. Und darum geht es in dieser Artikelstunde.
Heute zeigen wir Ihnen zwei verschiedene Methoden, mit denen Namen in verschiedene Zellen aufgeteilt werden können.
Die gebräuchlichste Art, wie Excel Namen speichert, besteht darin, zuerst den Vornamen, dann das Leerzeichen und dann die mittlere Initiale (falls vorhanden) gefolgt vom Leerzeichen und dann den Nachnamen zu verwenden. Manchmal wird anstelle von Leerzeichen ein Komma für Trennungen verwendet.
Fahren Sie mit dem ersten Beispiel fort. Wie Sie sehen, gibt es in „Spalte A“ einige Namen, die extrahiert und in Spalte B, C und D als Vorname, mittlerer Vorname bzw. Nachname zurückgegeben werden sollen.
Beispiel 1
Hinweis: In diesem Lernprogramm werden zufällige Beispielnamen verwendet, um zu erläutern, wie Werte extrahiert und in verschiedenen Spalten zurückgegeben werden.
====== Beachten Sie, dass in diesem Lernprogramm zufällige Stichprobennamen verwendet werden, um zu erklären, wie Werte extrahiert und in verschiedenen Spalten zurückgegeben werden.
Hier verwenden wir die Funktion „Text in Spalte“, mit der einfacher Zelleninhalt als Vor- und Nachname in separate Spalten unterteilt wird.
Diese Funktion befindet sich auf der Registerkarte „Daten“ in der Kategorie „Datentools“. Sie können auch die Tastenkombination „ALT + D + E“ (link: / tips-excel-shortcut-keys [Weitere Tastaturkürzel]]) verwenden, um auf den Assistenten „Text to Column“ zuzugreifen.
=== Wählen Sie den Bereich von A9 bis A17 und drücken Sie dann “ALT + D + E”.
====== Beachten Sie, dass in diesem Lernprogramm zufällige Stichprobennamen verwendet werden, um zu erklären, wie Werte extrahiert und in verschiedenen Spalten zurückgegeben werden.
Und jetzt müssen wir drei einfache Schritte ausführen. Wie Sie oben in der Assistentenbox sehen können, befinden wir uns im ersten Schritt.
Wählen Sie „Begrenzt“. Klicken Sie auf „Weiter“. Im ersten Schritt müssen wir Excel mitteilen, wie unsere Daten in der Excel-Datei gespeichert und formatiert werden.
Jetzt befinden wir uns im zweiten Schritt dieses Assistenten, und wie Sie unten sehen können, müssen wir hier die Begrenzer festlegen, die unsere Daten enthalten.
====== Notethat * In diesem Tutorial werden zufällige Stichprobennamen verwendet, um zu erklären, wie Werte extrahiert und in verschiedenen Spalten zurückgegeben werden.
Hier sehen Sie einige Kontrollkästchen „Trennzeichen“. Sie können also entweder diese Kontrollkästchen aktivieren oder das Trennzeichen im Kontrollkästchen „Andere“ angeben. Wenn Ihre Daten andere Trennzeichen enthalten, die hier nicht aufgeführt sind. Sie können ein oder mehrere Trennzeichen auswählen, wenn Ihre Daten mehr als ein Trennzeichen enthalten.
====== Notethat * Zufallsstichprobennamen werden in diesem Lernprogramm verwendet, um zu erklären, wie Werte extrahiert und in verschiedenen Spalten zurückgegeben werden.
Aktivieren Sie das Trennzeichen „Leerzeichen“ und klicken Sie dann auf „Weiter“
====== Notethat * Zufallsstichprobennamen werden in diesem Lernprogramm verwendet, um zu erklären, wie Werte extrahiert und in verschiedenen Spalten zurückgegeben werden.
Fahren Sie mit dem letzten Schritt des Assistenten „Text in Spalte“ fort.
Wählen Sie im Spaltendatenformat „Allgemein“. Wählen Sie B9 als „Ziel“ und sperren Sie es. * Klicken Sie auf „Fertig stellen“
====== Notethat * In diesem Tutorial werden zufällige Stichprobennamen verwendet, um zu erklären, wie Werte extrahiert und in verschiedenen Spalten zurückgegeben werden.
Beachten Sie, dass die extrahierten Spalten die ursprünglichen Daten ersetzen, wenn Sie keine neue Zielzellenreferenz angeben.
Jetzt können Sie sehen, dass die Namen in „Spalte A“ in mehreren Spalten extrahiert wurden. Wenn Sie genau hinschauen, werden Sie feststellen, dass „Steve Smith“ in der 11. Zeile keine mittlere Initiale hat und sein Nachname in die Spalte mit dem zweiten Namen verschoben wird.
====== Notethat * In diesem Tutorial werden zufällige Stichprobennamen verwendet, um zu erklären, wie Werte extrahiert und in verschiedenen Spalten zurückgegeben werden.
Dies liegt daran, dass „Text in Spalte“ nur verwendet wird, um einfachen Inhalt anhand seiner Trennzeichen in verschiedene Zellen zu unterteilen. Und da sein Name nur ein einziges Leerzeichen enthält, wurde es nur in die nächsten zwei Zellen aufgeteilt. Dies ist der einzige Nachteil dieser Funktion.
=== Beispiel 2
Hinweis: Im zweiten Beispiel haben wir „Bürgermeister Tom C Bradley als vollständigen Namen in Zelle H9 verwendet, um den Benutzern zu zeigen, dass die Funktion beide extrahiert, wenn der zweite Vorname mehr als 1 Initialen enthält.
Nun fahren wir mit dem zweiten Beispiel fort, in dem wir mehrere Funktionen zusammen verwenden, um das zu erhalten, wonach wir suchen. Dies ist die fortschrittliche Technik zum Extrahieren von „Vorname“, „Mitte“ und „Nachname“ in verschiedene Spalten. Um dasselbe zu tun, haben wir ähnliche Daten in Spalte H genommen und Namen in drei Teilen getrennt als Vor-, Mittel- und Nachname in Spalte I, J bzw. K. B
====== Notethat * In diesem Tutorial werden zufällige Stichprobennamen verwendet, um zu erklären, wie Werte extrahiert und in verschiedenen Spalten zurückgegeben werden.
Dies ist die beste Lösung, wenn Sie Namen in „Erste“, „Mittlere“ und „Letzte“ aufteilen möchten, aber die vorherige Methode nicht jedes Mal wiederholen möchten, wenn Sie dies tun. Sie können die Formeln in die Zellen einfügen und sobald die Referenzzelle aktualisiert wird, werden die Namen automatisch in bestimmten Spalten extrahiert, in denen Sie die Formeln eingegeben haben.
=== Um den Vornamen zu extrahieren
Zelle I9 auswählen Geben Sie die folgende Formel ohne Anführungszeichen ein: „= LINKS (H9, SUCHE (“ „, H9) -1)“ * Drücken Sie die Eingabetaste
====== Notethat * In diesem Tutorial werden zufällige Stichprobennamen verwendet, um zu erklären, wie Werte extrahiert und in verschiedenen Spalten zurückgegeben werden.
Und wie Sie sehen können, hat die Funktion den Vornamen aus H9 extrahiert und ihn in Zelle I9 mit „Mayor“ zurückgegeben. Wie Sie wissen, wird die Funktion LEFT verwendet, um das Zeichen ganz links und die Funktion link: / text-excel-search [SEARCH]
zu extrahieren Die Funktion gibt die Position des spezifischen Zeichens zurück, das zuerst in der Zeichenfolge gefunden wird. Hier hat die SEARCH-Funktion das Leerzeichen zuerst an der 6. Position in der Zeichenfolge gefunden. Um Leerzeichen auszuschließen, haben wir einen negativen Wert angegeben, der LEFT dabei half, nur 5 Zeichen aufzunehmen.
=== Um Nachname zu extrahieren
K9-Zelle auswählen Folgende Formel mit doppelten Anführungszeichen eingeben _ “= TRIM (RECHTS (ERSATZ (H9,“ „, REPT (“ „, 255)), 255)) _ * Drücken Sie die Eingabetaste
====== Notethat * In diesem Tutorial werden zufällige Stichprobennamen verwendet, um zu erklären, wie Werte extrahiert und in verschiedenen Spalten zurückgegeben werden.
Um den Nachnamen zu extrahieren, haben wir die Funktionen REPT und SUBSTITUTE in der Funktion RIGHT verschachtelt. Schließlich haben wir alle Bedingungen in TRIM verschachtelt, um alle unerwünschten Leerzeichen zu entfernen. Mal sehen, wie all diese Anweisungen zusammengearbeitet haben, um den Nachnamen zurückzugeben.
REPT ist in SUBSTITUTE verschachtelt, das 255 Mal ein einzelnes Leerzeichen wiederholt, und dann hat SUBSTITUTE ein einzelnes Leerzeichen durch 255 Leerzeichen in der angegebenen Zeichenfolge ersetzt. Und jetzt wird jede Initiale durch 255 Leerzeichen getrennt, und dann wird die RECHTS-Funktion verwendet, um die obersten 255 Zeichen zu extrahieren. Anschließend wird TRIM verwendet, um alle zusätzlichen Leerzeichen zu entfernen Rückkehr mit nur der letzten Initiale des Namens.
=== Um den zweiten Vornamen zu extrahieren
Wählen Sie nun die Zelle J9 aus. Geben Sie die folgende Formel ohne doppelte Anführungszeichen ein: „_ = IF (LEN (H9) -LEN (ERSATZ (H9,“
„,“ „))> 1, TRIM (ERSATZ (ERSATZ (H9, I9)) , „“), K9, „“)), „“) „_ * Drücken Sie die Eingabetaste
====== Notethat * In diesem Tutorial werden zufällige Stichprobennamen verwendet, um zu erklären, wie Werte extrahiert und in verschiedenen Spalten zurückgegeben werden.
Update: _ _Wenn Sie den zweiten Vornamen trennen müssen, der mit dem Vor- oder Nachnamen identisch ist, verwenden Sie die folgende Formel in Spalte J anstelle der oben genannten Formel. „= TRIM (MID (H9, LEN (I9) +1, LEN (H9) -LEN (I9) -LEN (K9))) ` Das Extrahieren der mittleren Initiale sieht ziemlich schwierig aus, da wir sicherstellen müssen, dass die Funktion alle Bedingungen auswertet und nur den zweiten Vornamen daraus extrahiert. Dafür haben wir LEN-, SUBSTITUTE-, TRIM-Funktionen in IF
verschachtelt Im ersten Argument der Funktion IF‹ überprüft der logische Test wird verwendet. Und falls https://www.youtube.com/watch?v=SeqWla6pvfw[IF weiblicher Zustand den logischen Test nicht erfüllt, gibt die Funktion leeren Text zurück.
Auf diese Weise können Sie Anpassungsfunktionen und einfache Methoden verwenden, um „Vorname“, „Mittlere Initiale“ und „Nachname“ aus einer Zelle zu extrahieren, die einen vollständigen Namen enthält.
== Video: So extrahieren Sie Vor-, Mittel- und Nachnamen aus einer Zelle in separate Zellen in Excel In diesem interessanten Video-Tutorial werden alle Funktionen und Techniken zum Extrahieren von Namen in verschiedene Zellen ausführlich beschrieben.
Wenn Ihnen unsere Blogs gefallen haben, teilen Sie sie Ihren Freunden auf Facebook mit. Sie können uns auch auf Twitter und Facebook folgen.
Wir würden gerne von Ihnen hören, lassen Sie uns wissen, wie wir unsere Arbeit verbessern, ergänzen oder innovieren und für Sie verbessern können. Schreiben Sie uns an [email protected]