In Excel ist es sehr einfach, Vor-, Mittel- und Nachnamen, die in derselben Spalte erscheinen, auf unterschiedliche Weise in separate Spalten aufzuteilen.
Angenommen, Sie haben eine Kontaktliste mit allen Namen in einer einzigen Spalte mit vollständigen Namen erhalten und müssen wahrscheinlich den Vor-, Mittel- und Nachnamen trennen und in separate Spalten aufteilen. Es gibt verschiedene Möglichkeiten, Namen zu trennen – mit der Funktion „Text in Spalten“, Flash-Füllung und Formeln.
In Excel ist es wirklich einfach, Namen aus einer Spalte in zwei oder mehr Spalten aufzuteilen. In diesem Artikel zeigen wir Ihnen, wie Sie die Namen in Excel auf unterschiedliche Weise in verschiedene Spalten aufteilen können.
So teilen Sie Namen in Excel auf
Es gibt drei einfache Möglichkeiten, Namen in Excel aufzuteilen. Wählen Sie je nach Datenstruktur und ob die Namensaufteilung statisch oder dynamisch sein soll, eine der folgenden Methoden:
- Teilen Sie Namen mit der Funktion „Text in Spalten“ auf
- Namen mit Formeln trennen
- Trennen Sie Namen mit Flash Fill
Trennen von Namen mit dem Text-in-Spalten-Assistenten
Der Text-in-Spalten-Assistent ist eine der gängigsten Methoden, um Vor- und Nachnamen in Excel sowie zweite Vornamen zu trennen. Stellen Sie sicher, dass sich neben den Namen, die Sie teilen möchten, leere Spalten befinden, da die vollständigen Namen in separate Spalten aufgeteilt werden.
Sie haben beispielsweise den folgenden Datensatz mit vollständigen Namen und möchten den Vor- und Nachnamen aufteilen/trennen und in separaten Zellen speichern.
Markieren Sie zunächst die Spalte mit den vollständigen Namen, die Sie trennen möchten. Gehen Sie dann zur Registerkarte „Daten“ und klicken Sie im Abschnitt „Datentools“ auf die Option „Text in Spalten“.
Der Assistent zum Konvertieren von Text in Spalten wird geöffnet. Wählen Sie im ersten Schritt des Assistenten die Option „Getrennt“ und klicken Sie auf „Weiter“.
Wählen Sie in Schritt 2 von 3 des Assistenten zum Konvertieren von Text in Spalten das Trennzeichen aus, das Ihre Daten trennt, entfernen Sie alle anderen Häkchen und klicken Sie auf „Weiter“. In unserem Fall trennt „Leerzeichen“ den Vor- und Nachnamen, daher wählen wir dieses Trennzeichen.
Der Abschnitt Datenvorschau unten im Fenster zeigt, wie Ihre Namen geparst werden.
In Schritt 3 von 3 wählen Sie das Datenformat und das Ziel aus und klicken auf „Fertig stellen“.
Normalerweise funktioniert die Standardeinstellung „Allgemein“ für die meisten Datentypen. Geben Sie im Feld „Ziel“ den Ort an, an dem die Ausgabe angezeigt werden soll. Sie müssen die Adresse der ersten Zelle in der Spalte angeben, in der die Ergebnisse angezeigt werden sollen (in unserem Fall B2).
Denken Sie daran, dass der Assistent die Originaldaten überschreibt, wenn Sie die Zielzelle nicht angeben. Stellen Sie also sicher, dass Sie eine leere Spalte auswählen.
Sobald Sie auf die Schaltfläche "Fertig stellen" klicken, werden die vollständigen Namen sofort in zwei separate Spalten (Vorname und Nachname) aufgeteilt.
Befolgen Sie die gleichen Schritte, wenn Sie Vor-, Zweit- und Nachnamen haben, und Ihre Namen werden in drei statt in zwei Spalten aufgeteilt.
Notiz: Das Ergebnis dieser Methode ist statisch. Das heißt, wenn Sie den ursprünglichen Namen ändern, müssen Sie dies noch einmal tun, um die Namen aufzuteilen.
Durch Kommas getrennte Namen teilen
Wenn Vor- und Nachname durch Kommas getrennt sind, führen Sie die folgenden Schritte aus, um Kommas zu entfernen und Vor- und Nachnamen aufzuteilen.
Im folgenden Beispiel werden Namen im umgekehrten Format formatiert (Nachname, Vorname), wobei der Nachname zuerst gefolgt von einem Komma und dann gefolgt vom Vornamen steht.
Wählen Sie die Namen aus und gehen Sie zu Daten –> Text in Spalte. Wählen Sie in Schritt 1 das „Trennzeichen“ und klicken Sie auf „Weiter“. Aktivieren Sie in Schritt 2 unter Trennzeichen „Komma“ (,) als Trennzeichen, da Ihre Namen durch ein Komma getrennt werden.
Im letzten Schritt wählen Sie als Datenformat „Allgemein“, geben das Ziel an und klicken auf „Fertig stellen“.
Jetzt haben Sie Namen in separaten Spalten.
Namen mit Formeln trennen
Mit dem Text-in-Spalten-Assistenten können Sie Namen schnell und einfach trennen. Wenn Sie jedoch die ursprünglichen Namen bearbeiten möchten und eine dynamische Methode wünschen, die sich bei jeder Namensänderung automatisch aktualisiert, ist das Aufteilen von Namen mit Formeln die richtige Wahl. Sie können die Funktionen LEFT, RIGHT, MID, LEN und SEARCH oder FIND verwenden, um Namen zu trennen.
Trennen Sie Vor- und Nachname in Excel mithilfe von Formeln
Holen Sie sich den Vornamen
Angenommen, Sie haben das folgende Dataset und möchten den Vornamen in eine separate Zelle aufteilen. Sie müssen die Funktion FIND und LEFT in einer Formel kombinieren, um den Vornamen zu erhalten.
Verwenden Sie die folgende Formel, um den Vornamen zu erhalten:
=LINKS(A2,SUCHEN(" ",A2)-1)Diese Formel verwendet die FIND-Funktion, um die Position des Leerzeichens („ “) zwischen dem Vor- und Nachnamen zu finden und subtrahiert 1, um das Leerzeichen selbst auszuschließen. Diese Nummer wird dann an die LEFT-Funktion übergeben, die diese Positionsnummer verwendet, um den gesamten Text davor zu extrahieren. Sie können anstelle der Funktion SUCHEN auch die Funktion SUCHEN verwenden.
Nachdem Sie die Formel in eine leere Zelle (B2) eingegeben haben, ziehen Sie das Ausfüllkästchen nach unten auf andere Zellen, um diese Formel anzuwenden, und alle Vornamen wurden wie unten gezeigt in Spalte B aufgeteilt:
Sie können entweder die SEARCH- und FIND-Funktion innerhalb der LEFT-Funktion verschachteln, um den Vornamen zu extrahieren. Der Hauptunterschied zwischen den beiden Funktionen besteht darin, dass bei FIND die Groß-/Kleinschreibung beachtet wird, während bei SEARCH die Groß-/Kleinschreibung nicht beachtet wird.
Wenn Sie ein Komma (,) anstelle eines Leerzeichens zwischen dem Vor- und Nachnamen haben, verwenden Sie das Komma als erstes Argument in der FIND-Funktion:
=LINKS(A2,SUCHEN(",",A2)-1)Holen Sie sich den Nachnamen
Wenn Sie nun den Nachnamen extrahieren müssen, verwenden Sie die RECHTE Funktion. Die folgende Formel extrahiert den Nachnamen aus demselben Datensatz:
=RECHTS(A2,LEN(A2)-FINDEN(" ",A2))Die Formel findet zuerst die Position des Leerzeichens, subtrahiert diese Zahl von der Gesamtlänge der Zeichenfolge (die von der LEN-Funktion zurückgegeben wird), und diese Zahl wird dann an die RIGHT-Funktion übergeben, um so viele Zeichen vom Ende der Zeichenfolge zu extrahieren (Name).
Trennen Sie den Vor-, Mittel- und Nachnamen in Excel mithilfe von Formeln
Das Aufteilen von Namen, die einen zweiten Vornamen enthalten, erfordert je nach Namensformat unterschiedliche Formeln.
Um das zu bekommen Vorname Wenn Sie einen zweiten Vornamen oder eine mittlere Initiale haben, verwenden Sie dieselbe LINKE FINDEN-Formel, mit der Sie bereits vertraut sind.
Holen Sie sich den Nachnamen
Die obige RIGHT FIND-Formel funktioniert gut, wenn nur der Vor- und Nachname vorhanden ist. Sie ist nicht von großem Nutzen, wenn Ihre ursprünglichen Namen einen zweiten Vornamen oder einen zweiten Anfangsbuchstaben enthalten. Dies liegt daran, dass Sie im Namen keine zwei Leerzeichen berücksichtigt haben.
Um den Nachnamen zu erhalten, wenn Sie auch einen zweiten Vornamen haben, verwenden Sie diese Formel:
=RECHTS(A2,LEN(A2)-SUCHE(" ",A2,SUCHE(" ",A2,1)+1))Um den Nachnamen zu extrahieren, bestimmen Sie zuerst die Position des zweiten Leerzeichens mithilfe der verschachtelten SEARCH-Funktionen, fügen Sie 1 zu SEARCH(“ „,A2,1) hinzu, um die Extraktion mit dem nächsten Zeichen zu starten. Als nächstes subtrahieren Sie die Position des 2. Leerzeichens von der gesamten Stringlänge und erhalten die Länge des Nachnamens als Ergebniszahl. Geben Sie diese resultierende Zahl dann an die RIGHT-Funktion, um die Anzahl der Zeichen vom Ende der Zeichenfolge zu extrahieren.
Holen Sie sich den zweiten Vornamen
Die MID-Funktion verwendet drei Argumente, das erste Argument gibt den Text oder die Zellenadresse an, das zweite die Startposition und das letzte Argument gibt die Anzahl der Zeichen an, um den zweiten Vornamen aus dieser Position zu extrahieren.
Die Syntax:
=MID(text, start_num, num_chars)Um den zweiten Vornamen zu erhalten, geben Sie diese Formel in eine leere Zelle ein:
=MITTEL(A2,SUCHE(" ",A2)+1,SUCHE(" ",A2,SUCHE(" ",A2)+1)-SUCHE(" ",A2)-1)Mal sehen, wie diese komplexe Formel funktioniert:
Um den zweiten Vornamen oder den zweiten Anfangsbuchstaben zu extrahieren, müssen Sie die Position beider Leerzeichen im vollständigen Namen bestimmen. Geben Sie dies ein, um die Position des ersten Leerzeichens zu ermitteln SUCHE(" ",A2) Funktion im Argument ‚start_num‘ und fügen Sie 1 hinzu, um die Extraktion ab dem nächsten Zeichen zu starten.
Um die Länge des zweiten Vornamens herauszufinden, geben Sie dies ein SUCHE(" ",A2,SUCHE(" ",A2)+1)-SUCHE(" ",A2)-1 verschachtelte Funktion im 'num_chars'-Argument, die die Position des 1. Leerzeichens von der Position des 2. Leerzeichens subtrahiert und 1 vom Ergebnis subtrahiert, um ein nachgestelltes Leerzeichen zu entfernen. Das Endergebnis sagt Ihnen, wie viele Zeichen Sie extrahieren müssen.
Jetzt trennt die MID-Funktion mit Startposition des zweiten Vornamens und Zahlenzeichen zum Extrahieren den zweiten Vornamen vom vollständigen Namen (A2).
Trennen Sie Namen in Excel mit Flash Fill
Flash Fill wird meistens zum automatischen Füllen von Daten eines bestimmten Musters verwendet. Es kann auch zum Aufteilen von Namen verwendet werden. Es ist nur in Excel 2013, 2016, 2019 und 365 verfügbar.
Angenommen, Sie haben den folgenden Datensatz und möchten nur die Vornamen aus den vollständigen Namen erhalten.
Geben Sie in der Zelle neben dem ursprünglichen Namen den Vornamen ein. Geben Sie in diesem Fall "Steve" in Zelle B2 ein.
Beginnen Sie dann mit der Eingabe des Vornamens in die zweite Zelle der Spalte. Wenn Excel während der Eingabe ein Muster erkennt, zeigt Flash Fill automatisch eine Liste der Vornamen in anderen Zellen an (in grau).
Wenn Sie die Liste der Namen in Grau sehen und diese Namen richtig sind, drücken Sie einfach die Eingabetaste und Flash Fill füllt den Rest der Spalte automatisch mit Vornamen.
Gehen Sie genauso vor, um Nachnamen in eine separate Spalte zu trennen.
Das Ergebnis:
Flash Fill funktioniert, indem es ein Muster in diesen Daten erkennt und diesem Muster folgt, während Sie die geänderten Daten erhalten. Wenn Sie den Vornamen in die erste Zelle eingeben, erkennt Flash Fill das Muster zunächst nicht. Wenn Sie jedoch in der zweiten Zelle erneut mit der Eingabe des Vornamens beginnen, erkennt Flash Fill das Muster und zeigt Ihnen den Vorschlag zur Aufteilung der Vornamen an. Drücken Sie dann einfach die Eingabetaste.
Im Allgemeinen ist die Flash Fill-Funktion standardmäßig aktiviert. Wenn es in Ihrem Excel nicht funktioniert, können Sie nach Eingabe des Vornamens in die erste Zelle einfach die zweite Zelle auswählen und auf die Schaltfläche "Flash Fill" in der Gruppe "Datentools" auf der Registerkarte "Daten" klicken.
Alternativ können Sie auch „Strg“ + „E“ drücken, um die gleichen Ergebnisse zu erhalten.
Jetzt werden die restlichen Zellen mit Vornamen ausgefüllt.
Manchmal wird der Mustervorschlag möglicherweise nicht grau angezeigt. In diesem Fall können Sie den Füllgriff verwenden, um das Ergebnis der Flash-Füllung zu erhalten.
Geben Sie zunächst die Namen manuell in zwei Zellen ein und wählen Sie diese beiden Zellen aus. Bewegen Sie dann den Cursor auf die untere rechte Ecke der Auswahl. Sie werden feststellen, dass sich der Cursor von einem kleinen grünen Quadrat (Füller-Symbol) zu einem Plus-Symbol ändert.
Als nächstes doppelklicken Sie auf das Plus-Symbol. Dadurch werden die restlichen Zellen gefüllt. An dieser Stelle sind die Ergebnisse falsch, Sie werden sehen, dass beide Vornamen immer wieder wiederholt werden. Dann sehen Sie in der unteren rechten Ecke der resultierenden Daten ein kleines Auto-Fill-Symbol wie unten gezeigt. Klicken Sie auf dieses Symbol „Auto-Fill“ und wählen Sie „Flash Fill“.
Dies würde Vornamen in alle Zellen füllen:
Entfernen Sie den zweiten Vornamen
Sie können auch das Flash Fill-Tool verwenden, um den zweiten Vornamen vom vollständigen Namen zu entfernen.
Angenommen, Sie haben den folgenden Datensatz und möchten nur den Vor- und Nachnamen ohne den zweiten Vornamen oder die zweite Initiale abrufen.
Um Namen ohne zweiten Vornamen oder zweiten Anfangsbuchstaben zu erhalten, geben Sie manuell „Lord Stark“ in die benachbarte Zelle ein. Geben Sie dann in die zweite benachbarte Zelle „Daenerys Targaryen“ ein. Während Sie tippen, erkennt Flash Fill ein Muster und zeigt Ihnen eine Liste mit Namen ohne zweite Vornamen (in grau) an.
Wenn der Vorschlag richtig ist, drücken Sie die Eingabetaste und Flash Fill füllt automatisch die restlichen Zellen mit den Namen ohne den zweiten Vornamen.
Wenn Sie nur die zweiten Vornamen ohne Vor- und Nachnamen erhalten möchten, geben Sie den zweiten Vornamen in die ersten beiden Zellen ein und verwenden Sie das Flash Fill-Tool, um die zweiten Vornamen aus allen vollständigen Vornamen in einer Spalte zu erhalten.
Dieses Tutorial zeigt, wie Sie Namen trennen, während Sie die Textdaten bearbeiten. Diese Methoden können auch bei der Arbeit mit anderen Datenformen wie Adressen, Produktnamen, Markennamen usw. hilfreich sein.