Vor- und Nachname in Excel trennen (Namen mithilfe von Formeln aufteilen)

Video ansehen - Namen in Excel aufteilen (in Vor-, Mittel- und Nachname)

Excel ist ein erstaunliches Werkzeug, wenn es um das Schneiden und Würfeln von Textdaten geht.

Es gibt so viele nützliche Formeln und Funktionen, die Sie verwenden können, um mit Textdaten in Excel zu arbeiten.

Eine der häufigsten Fragen, die ich zur Manipulation von Textdaten bekomme, lautet: „Wie trennt man Vor- und Nachnamen (oder Vor-, Mittel- und Nachnamen) in Excel?“.

Es gibt ein paar einfache Möglichkeiten, Namen in Excel aufzuteilen. Welche Methode Sie wählen, hängt davon ab, wie Ihre Daten strukturiert sind und ob das Ergebnis von coothe statisch oder dynamisch sein soll.

Beginnen wir also und sehen wir uns verschiedene Möglichkeiten zum Aufteilen von Namen in Excel an.

Teilen Sie Namen mit Text in Spalten auf

Mit der Text-in-Spalten-Funktion in Excel können Sie Textwerte schnell in separate Zellen in einer Zeile aufteilen.

Angenommen, Sie haben das unten gezeigte Dataset und möchten den Vor- und Nachnamen trennen und diese in separaten Zellen abrufen.

Nachfolgend finden Sie die Schritte zum Trennen des Vor- und Nachnamens mithilfe von Text in Spalten:

1. Wählen Sie alle Namen in der Spalte aus (A2:A10 in diesem Beispiel)
2. Klicken Sie auf die Registerkarte „Daten“
3. Klicken Sie in der Gruppe „Datentools“ auf die Option „Text in Spalten“.
4. Nehmen Sie die folgenden Änderungen im Assistenten zum Konvertieren von Text in Spalten vor:
   1. Schritt 1 von 3: Wählen Sie Getrennt (damit können Sie Leerzeichen als Trennzeichen verwenden) und klicken Sie auf Weiter
   2. Schritt 2 von 3: Wählen Sie die Option Leerzeichen und klicken Sie auf Weiter
   3. Schritt 3 von 3: B2 als Zielzelle festlegen (sonst werden die vorhandenen Daten überschrieben)
5. Klicken Sie auf Fertig stellen

Die obigen Schritte würden die Namen sofort in Vor- und Nachnamen aufteilen (mit Vornamen in Spalte B und Nachname in Spalte C).

Hinweis: Falls sich bereits Daten in den Zellen befinden (diejenigen, in denen die Ausgabe von Text in Spalten erwartet wird), zeigt Excel Ihnen eine Warnung an, die Sie darüber informiert, dass sich bereits einige Daten in den Zellen befinden. Sie können wählen, ob Sie die Daten überschreiben oder Text in Spalten abbrechen und zuerst manuell entfernen möchten.

Wenn Sie fertig sind, können Sie die vollständigen Namensdaten löschen, wenn Sie möchten.

Einige Dinge, die Sie wissen sollten, wenn Sie Text in Spalten verwenden, um Vor- und Nachnamen in Excel zu trennen:

1. Das Ergebnis ist statisch. Das heißt, falls Sie neue Daten haben oder die Originaldaten einige Änderungen aufweisen, müssen Sie dies noch einmal tun, um die Namen aufzuteilen.
2. Wenn Sie nur den Vornamen oder nur den Nachnamen wünschen, können Sie die nicht gewünschten Spalten in Schritt 3 des Dialogfelds „Text-in-Spalten-Assistent“ überspringen. Wählen Sie dazu in der Vorschau die Spalte aus, die Sie überspringen möchten und wählen Sie dann die Option „Spalte nicht importieren (überspringen)“.
3. Falls Sie die Zielzelle nicht angeben, überschreibt Text in Spalte die aktuelle Spalte

Die Option Text in Spalten eignet sich am besten, wenn Sie konsistente Daten haben (z. B. haben alle Namen nur Vor- und Nachnamen oder alle Namen haben Vor-, Mittel- und Nachnamen).

In diesem Beispiel habe ich Ihnen gezeigt, wie Sie Namen trennen können, die Leerzeichen als Trennzeichen enthalten. Falls das Trennzeichen ein Komma oder eine Kombination aus Komma und Leerzeichen ist, können Sie immer noch die gleichen Schritte ausführen. In diesem Fall können Sie das Trennzeichen in Schritt 2 von 3 des Assistenten angeben. Es gibt bereits eine Option, das Komma als Trennzeichen zu verwenden, oder Sie können die Option "Andere" auswählen und auch ein benutzerdefiniertes Trennzeichen angeben.

Text in Spalten ist zwar eine schnelle und effiziente Methode zum Aufteilen von Namen, eignet sich jedoch nur, wenn die Ausgabe ein statisches Ergebnis sein soll. Falls Sie einen Datensatz haben, der sich erweitern oder ändern kann, verwenden Sie besser Formeln, um die Namen zu trennen.

Trennen Sie Vor-, Mittel- und Nachnamen mithilfe von Formeln

Formeln ermöglichen es Ihnen, die Textdaten zu schneiden und zu würfeln und zu extrahieren, was Sie wollen.

In diesem Abschnitt werde ich verschiedene Formeln vorstellen, die Sie verwenden können, um Namensdaten zu trennen (basierend auf der Struktur Ihrer Daten).

Unten sind die drei Formeln, die Sie verwenden können, um Vor-, Mittel- und Nachname zu trennen (weiter unten in den folgenden Abschnitten ausführlich erklärt).

Formel um den Vornamen zu erhalten:

=LINKS(A2,SUCHE(" ",A2)-1)

Formel, um den zweiten Vornamen zu erhalten:

=MITTEL(A2,SUCHE(" ",A2)+1,SUCHE(" ",ERSETZEN(A2," ","@",1))-SUCHE(" ",A2))

Formel, um den Nachnamen zu erhalten:

=RECHTS(A15,LEN(A15)-SUCHE("@",ERSETZEN(A15," ","@",LEN(A15)-LEN(ERSETZEN(A15," ","")))))

Holen Sie sich den Vornamen

Angenommen, Sie haben den unten gezeigten Datensatz und möchten den Vornamen in einer Zelle und den Nachnamen in einer Zelle schnell trennen.

Die folgende Formel gibt Ihnen den Vornamen:

=LINKS(A2,SUCHE(" ",A2)-1)

Die obige Formel verwendet die SEARCH-Funktion, um die Position des Leerzeichens zwischen dem Vor- und Nachnamen zu ermitteln. Die LEFT-Funktion verwendet dann diese Leerzeichenpositionsnummer, um den gesamten Text davor zu extrahieren.

Dies ist eine ziemlich einfache Verwendung zum Extrahieren eines Teils des Textwerts. Da wir nur die erste Leerzeichenposition identifizieren müssen, spielt es keine Rolle, ob der Name einen zweiten Vornamen hat oder nicht. Die obige Formel wird gut funktionieren.

Kommen wir nun mit jedem Beispiel etwas weiter.

Holen Sie sich den Nachnamen

Nehmen wir an, Sie haben den gleichen Datensatz und müssen diesmal den Nachnamen abrufen.

Die folgende Formel extrahiert den Nachnamen aus dem obigen Datensatz:

=RECHTS(A2,LEN(A2)-SUCHE(" ",A2))

Wieder ganz einfach.

Dieses Mal suchen wir zuerst die Position des Leerzeichens, die dann verwendet wird, um die Anzahl der Zeichen herauszufinden, die nach dem Leerzeichen (das wäre der Nachname) übrig bleiben.

Dies wird erreicht, indem der Positionswert des Leerzeichens von der Gesamtzahl der Zeichen im Namen subtrahiert wird.

Diese Nummer wird dann in der RIGHT-Funktion verwendet, um alle diese Zeichen rechts vom Namen abzurufen.

Während diese Formel gut funktioniert, wenn nur der Vor- und Nachname vorhanden ist, würde sie nicht funktionieren, wenn Sie auch einen zweiten Vornamen haben. Dies liegt daran, dass wir nur ein Leerzeichen (zwischen Vor- und Nachname) berücksichtigt haben. Bei einem zweiten Vornamen werden dem Namen mehr Leerzeichen hinzugefügt.

Um den Nachnamen abzurufen, wenn Sie auch einen zweiten Vornamen haben, verwenden Sie die folgende Formel:

=RECHTS(A15,LEN(A15)-SUCHE("@",ERSETZEN(A15," ","@",LEN(A15)-LEN(ERSETZEN(A15," ","")))))

Nun, das ist ein bisschen komplex geworden … nicht wahr?

Lassen Sie mich erklären, wie das funktioniert.

Die obige Formel ermittelt zuerst die Gesamtzahl der Leerzeichen im Namen. Dies geschieht, indem die Länge des Namens mit und ohne Leerzeichen ermittelt wird und dann der Name ohne Leerzeichen von dem mit Leerzeichen abgezogen wird. Dies ergibt die Gesamtzahl der Leerzeichen.

Die Funktion SUBSTITUTE wird dann verwendet, um das letzte Leerzeichen durch ein @-Symbol zu ersetzen (Sie können jedes beliebige Symbol verwenden - etwas, das wahrscheinlich nicht als Teil des Namens vorkommt).

Nachdem Sie das letzte Leerzeichen durch das @-Symbol ersetzt haben, können Sie die Position dieses @-Symbols leicht finden. Dies geschieht über die SEARCH-Funktion.

Jetzt müssen Sie nur noch alle Zeichen rechts von diesem @-Symbol extrahieren. Dies geschieht mit der RECHTEN Funktion.

Holen Sie sich den zweiten Vornamen

Angenommen, Sie haben das unten gezeigte Dataset und möchten den zweiten Vornamen extrahieren.

Die folgende Formel wird dies tun:

=MITTEL(A2,SUCHE(" ",A2)+1,SUCHE(" ",ERSETZEN(A2," ","@",1))-SUCHE(" ",A2))

Die obige Formel verwendet die MID-Funktion, mit der Sie eine Startposition und die Anzahl der Zeichen angeben können, die von dieser Position extrahiert werden sollen.

Die Startposition ist mit der SEARCH-Funktion leicht zu finden.

Der schwierige Teil besteht darin, herauszufinden, wie viele Zeichen nach dieser Startposition extrahiert werden müssen. Um dies zu erhalten, müssen Sie ermitteln, wie viele Zeichen von der Startposition bis zum letzten Leerzeichen vorhanden sind.

Verwenden Sie dazu die Funktion SUBSTITUTE und ersetzen Sie das letzte Leerzeichen durch ein @-Symbol. Sobald dies erledigt ist, können Sie die Position dieses letzten Leerzeichens einfach mit der SUCHE-Funktion finden.

Da Sie nun die Startposition und die Position des letzten Leerzeichens haben, können Sie den zweiten Vornamen einfach mithilfe der MID-Funktion abrufen.

Einer der Vorteile der Verwendung einer Formel zum Trennen der Namen besteht darin, dass das Ergebnis dynamisch ist. Für den Fall, dass Ihr Dataset erweitert wird und weitere Namen hinzugefügt werden oder sich einige Namen im ursprünglichen Dataset ändern, müssen Sie sich keine Sorgen um die resultierenden Daten machen.

Trennen Sie Namen mit Suchen und Ersetzen

Ich liebe die Flexibilität, die mit „Suchen und Ersetzen“ einhergeht – weil Sie darin Platzhalterzeichen verwenden können.

Lassen Sie mich zunächst erklären, was ein Wildcard-Zeichen ist.

Ein Platzhalterzeichen ist etwas, das Sie anstelle von Text verwenden können. Sie können beispielsweise ein Sternchensymbol (*) verwenden, das eine beliebige Anzahl von Zeichen in Excel darstellt. Um Ihnen ein Beispiel zu geben: Wenn ich alle Namen finden möchte, die mit dem Alphabet A beginnen, kann ich A* in Suchen und Ersetzen verwenden. Dadurch werden alle Zellen gefunden und ausgewählt, deren Name mit A beginnt.

Wenn Sie immer noch nicht klar sind, machen Sie sich keine Sorgen. Lesen Sie weiter und die nächsten Beispiele werden deutlich machen, was Platzhalterzeichen sind und wie Sie diese verwenden können, um Namen (oder Textwerte in Excel) schnell zu trennen.

Stellen Sie in allen unten behandelten Beispielen sicher, dass Sie eine Sicherungskopie des Datasets erstellen. Suchen und Ersetzen ändert die Daten, für die es verwendet wird. Es empfiehlt sich, zuerst die Daten zu kopieren und einzufügen und dann Suchen und Ersetzen für den kopierten Datensatz zu verwenden.

Holen Sie sich den Vornamen

Angenommen, Sie haben einen Datensatz wie unten gezeigt und möchten nur den Vornamen abrufen.

Im Folgenden sind die Schritte dazu aufgeführt:

1. Kopieren Sie die Namensdaten in Spalte A und fügen Sie sie in Spalte B ein.
2. Wenn die Daten in Spalte B ausgewählt sind, klicken Sie auf die Registerkarte Start
3. Klicken Sie in der Gruppe Bearbeiten auf Suchen & Auswählen.
4. Klicken Sie auf Ersetzen. Dadurch wird das Dialogfeld „Suchen und Ersetzen“ geöffnet.
5. Geben Sie im Dialogfeld „Suchen und Ersetzen“ Folgendes ein:
   1. Finden Sie was: * (Leerzeichen gefolgt von dem Sternsymbol)
   2. Ersetzen durch: dieses Feld leer lassen
6. Klicken Sie auf Alle ersetzen.

Die obigen Schritte würden Ihnen den Vornamen geben und alles nach dem Vornamen entfernen.

Dies funktioniert auch, wenn Sie Namen haben, die einen zweiten Vornamen haben.

Profi-Tipp: Die Tastenkombination zum Öffnen des Dialogfelds Suchen und Ersetzen lautet Steuerung + H (Halten Sie die Steuertaste gedrückt und drücken Sie dann die H-Taste).

Holen Sie sich den Nachnamen

Angenommen, Sie haben einen Datensatz wie unten gezeigt und möchten nur den Nachnamen abrufen.

Im Folgenden sind die Schritte dazu aufgeführt:

1. Kopieren Sie die Namensdaten in Spalte A und fügen Sie sie in Spalte B ein.
2. Wenn die Daten in Spalte B ausgewählt sind, klicken Sie auf die Registerkarte Start
3. Klicken Sie in der Gruppe Bearbeiten auf Suchen & Auswählen.
4. Klicken Sie auf Ersetzen. Dadurch wird das Dialogfeld „Suchen und Ersetzen“ geöffnet.
5. Geben Sie im Dialogfeld „Suchen und Ersetzen“ Folgendes ein:
   1. Finden Sie was: * (Sternchen gefolgt von einem Leerzeichen)
   2. Ersetzen durch: dieses Feld leer lassen
6. Klicken Sie auf Alle ersetzen.

Die obigen Schritte würden Ihnen den Nachnamen geben und alles vor dem Vornamen entfernen.

Dies funktioniert auch, wenn Sie Namen haben, die einen zweiten Vornamen haben.

Entfernen Sie den zweiten Vornamen

Falls Sie nur den zweiten Vornamen loswerden möchten und nur den Vor- und Nachnamen haben, können Sie dies mit Suchen und Ersetzen tun.

Angenommen, Sie haben einen Datensatz wie unten gezeigt und möchten den zweiten Vornamen daraus entfernen.

Im Folgenden sind die Schritte dazu aufgeführt:

1. Kopieren Sie die Namensdaten in Spalte A und fügen Sie sie in Spalte B ein.
2. Wenn die Daten in Spalte B ausgewählt sind, klicken Sie auf die Registerkarte Start
3. Klicken Sie in der Gruppe Bearbeiten auf Suchen & Auswählen.
4. Klicken Sie auf Ersetzen. Dadurch wird das Dialogfeld „Suchen und Ersetzen“ geöffnet.
5. Geben Sie im Dialogfeld „Suchen und Ersetzen“ Folgendes ein:
   1. Finden Sie was: * (Leerzeichen gefolgt von einem Sternchen gefolgt von einem Leerzeichen)
   2. Ersetzen durch: (hier einfach ein Leerzeichen einfügen)
6. Klicken Sie auf Alle ersetzen.

Die obigen Schritte würden den zweiten Vornamen aus einem vollständigen Namen entfernen. Falls einige Namen keinen zweiten Vornamen haben, werden sie nicht geändert.

Trennen Sie Namen mit Flash Fill

Flash-Füllung wurde in Excel 2013 eingeführt und macht es wirklich einfach, einen Textdatensatz zu ändern oder zu bereinigen.

Und wenn es darum geht, Namensdaten zu trennen, ist es ganz oben in der Gasse von Flash Fill.

Das Wichtigste bei der Verwendung von Flash Fill ist, dass es ein Muster geben muss, das Flash Fill identifizieren kann. Sobald es das Muster identifiziert hat, wird es Ihnen leicht helfen, Namen in Excel aufzuteilen (Sie werden mehr Klarheit darüber bekommen, wenn Sie unten einige Beispiele durchgehen).

Holen Sie sich den Vor- oder Nachnamen von Full Name

Angenommen, Sie haben einen Datensatz wie unten gezeigt und möchten nur den Vornamen abrufen.

1. Geben Sie in der angrenzenden Zelle manuell den Vornamen aus dem vollständigen Namen ein. In diesem Beispiel würde ich Rick eingeben.
2. Geben Sie in der zweiten Zelle manuell den Vornamen aus dem Namen der angrenzenden Zelle ein. Während Sie tippen, sehen Sie, dass Flash Fill Ihnen automatisch eine Liste mit dem Vornamen anzeigt (in grau).
3. Wenn Sie die Namen in Grau sehen, sehen Sie schnell durch, um sicherzustellen, dass die richtigen Namen angezeigt werden. Wenn diese richtig sind, drücken Sie die Eingabetaste und Flash Fill füllt automatisch den Rest der Zellen mit dem Vornamen.

Flash Fill erfordert, dass Sie ihm ein Muster geben, dem es folgen kann, wenn es Ihnen die geänderten Daten liefert. Wenn Sie in unserem Beispiel den Vornamen in die erste Zelle eingeben, kann Flash Fill das Muster nicht erkennen.

Sobald Sie jedoch den Vornamen in die zweite Zelle eingeben, versteht Flash Fill das Muster und zeigt Ihnen einige Vorschläge an. Wenn der Vorschlag richtig ist, drücken Sie einfach die Eingabetaste.

Und wenn es nicht richtig ist, können Sie versuchen, einige weitere Zellen manuell einzugeben und zu prüfen, ob Flash Fill das Muster erkennen kann oder nicht.

Manchmal wird das Muster möglicherweise nicht grau angezeigt (wie in Schritt 2 oben gezeigt). Wenn dies der Fall ist, führen Sie die folgenden Schritte aus, um das Flash Fill-Ergebnis zu erhalten:

1. Geben Sie den Text manuell in zwei Zellen ein.
2. Wählen Sie diese beiden Zellen aus
3. Bewegen Sie den Cursor auf den unteren rechten Teil der Auswahl. Sie werden feststellen, dass sich der Cursor in ein Plus-Symbol verwandelt
4. Doppelklicken Sie darauf (Maus-Links-Taste). Dadurch werden alle Zellen gefüllt. Zu diesem Zeitpunkt sind die Ergebnisse wahrscheinlich falsch und entsprechen nicht Ihren Erwartungen.
5. Unten rechts in den resultierenden Daten sehen Sie ein kleines Auto-Fill-Symbol. Klicken Sie auf dieses Symbol zum automatischen Ausfüllen
6. Klicken Sie auf Flash Fill

Mit den obigen Schritten erhalten Sie das Ergebnis von Flash Fill (basierend auf dem abgeleiteten Muster).

Sie können auch Flash Fill verwenden, um den Nachnamen oder den zweiten Vornamen zu erhalten. Geben Sie in die ersten beiden Zellen den Nachnamen (oder den zweiten Vornamen) ein und Flash Fill kann das Muster verstehen

Neuanordnen des Namens mit Flash Fill

Flash Fill ist ein intelligentes Werkzeug und kann auch leicht komplexe Muster entziffern

Angenommen, Sie haben ein Dataset wie unten gezeigt und möchten den Namen von Rick Novak in Novak, Rick umordnen (wobei der Nachname zuerst gefolgt von einem Komma und dann dem Vornamen steht).

Im Folgenden sind die Schritte dazu aufgeführt:

1. Geben Sie in die benachbarte Zelle manuell ein Novak, Rick
2. Geben Sie in die zweite Zelle manuell ein Connor, Susan. Während Sie tippen, sehen Sie, dass Flash Fill eine Liste der Namen im gleichen Format (in Grau) anzeigt.
3. Wenn Sie die Namen in Grau sehen, sehen Sie schnell durch, um sicherzustellen, dass die richtigen Namen angezeigt werden. Wenn diese richtig sind, drücken Sie die Eingabetaste und Flash Fill füllt automatisch die restlichen Zellen mit den Namen im gleichen Format.

Entfernen Sie den zweiten Vornamen (oder holen Sie sich einfach den zweiten Vornamen)

Sie können Flash Fill auch verwenden, um den zweiten Vornamen loszuwerden oder nur den zweiten Vornamen zu erhalten.

Angenommen, Sie haben ein Dataset wie unten gezeigt und möchten nur den Vor- und Nachnamen und nicht den zweiten Vornamen abrufen.

Im Folgenden sind die Schritte dazu aufgeführt:

1. Geben Sie in die benachbarte Zelle manuell ein Rick Novak
2. Geben Sie in die zweite Zelle manuell ein Susan Connor. Während Sie tippen, sehen Sie, dass Flash Fill Ihnen eine Liste der Namen im gleichen Format (in Grau) anzeigt.
3. Wenn Sie die Namen in Grau sehen, sehen Sie schnell durch, um sicherzustellen, dass die richtigen Namen angezeigt werden. Wenn diese richtig sind, drücken Sie die Eingabetaste und Flash Fill füllt den Rest der Zellen automatisch mit den Namen ohne den zweiten Vornamen.

Wenn Sie nur die zweiten Vornamen abrufen möchten, geben Sie den zweiten Vornamen in die ersten beiden Zellen ein und verwenden Sie Flash Fill, um den zweiten Vornamen aus allen verbleibenden Namen abzurufen.

Die in diesem Tutorial gezeigten Beispiele verwenden Namen beim Bearbeiten der Textdaten. Sie können die gleichen Konzepte verwenden, um auch mit anderen Datenformaten (wie Adressen, Produktnamen usw.)