9 Wege, Vor- und Nachnamen in Microsoft Excel zu trennen

Datensätze, die Personennamen enthalten, haben üblicherweise den Vor- und Nachnamen in einer einzigen Zelle, getrennt durch ein Leerzeichen. Obwohl dies nicht unbedingt ein Problem darstellt, kann es nützlicher sein, Vor- und Nachnamen in verschiedenen Spalten zu haben. Auf diese Weise können Sie weitere Analysen der Daten, wie das Sortieren und Filtern nur nach dem Nachnamen, einfacher durchführen.

1. Vor- und Nachnamen mit „Text in Spalten“ trennen

Wenn Sie Vor- und Nachnamen in Ihren Daten gelegentlich trennen müssen, sollte „Text in Spalten“ Ihre bevorzugte Methode sein. Die Funktion „Text in Spalten“ ermöglicht es Ihnen, Namen in einem neuen Bereich zu teilen oder die aktuellen Daten zu ersetzen. Je nachdem, was Sie bevorzugen.

Dies ist am besten für einmalige Anwendungen geeignet, da Sie die Text-in-Spalten-Trennung für jeden neu hinzugefügten Namen in der Liste erneut durchführen müssen.

Hier erfahren Sie, wie Sie „Text in Spalten“ verwenden, um Vor- und Nachnamen zu trennen:

1. Wählen Sie den Bereich der Namen aus, die Sie teilen möchten.
2. Gehen Sie zur Registerkarte „Daten“.
3. Klicken Sie auf den Befehl „Text in Spalten“ in der Gruppe „Datentools“.
4. Wählen Sie die Option „Getrennt“ aus, um Ihre Namen zu trennen.
5. Deaktivieren Sie die Standardoption „Tabulator“ und wählen Sie „Leerzeichen“.
6. Wählen Sie eine Zelle für das „Ziel“ aus.
7. Klicken Sie auf „Fertig stellen“.

Excel teilt die Namen basierend auf dem Leerzeichen, das sie trennt, und platziert die Ergebnisse an der gewünschten Position.

2. Vor- und Nachnamen mit Textformeln trennen

Eine andere Möglichkeit, Vor- und Nachnamen aus Ihrem vollständigen Namensset zu extrahieren, ist die Verwendung einer Kombination von Textfunktionen, um jeden Namen zu analysieren.

Sie können die Funktionen „LINKS“ und „RECHTS“ verwenden, um die gewünschte Anzahl von Zeichen vom Anfang oder Ende eines Textwerts zu erhalten. Sie können diese in Kombination mit der Funktion „FINDEN“ verwenden, um die Position des Leerzeichens zu erhalten, und der Funktion „LÄNGE“, um die Gesamtzahl der Zeichen im Namen zu erhalten.

Den Vornamen erhalten

= LINKS ( B3, FINDEN ( " ", B3 ) - 1 )

Die obige Formel extrahiert den Vornamen aus der vollständigen Namensbeschreibung in Zelle B3. Die Funktion „FINDEN“ findet die Position des ersten Leerzeichens. Wenn Sie 1 von diesem Ergebnis subtrahieren, erhalten Sie die Position des letzten Zeichens des Vornamens. Die Funktion „LINKS“ verwendet diesen Wert dann, um diese ersten Zeichen aus dem Namen zurückzugeben.

Den Nachnamen erhalten

= RECHTS ( B3, LÄNGE ( B3 ) - FINDEN ( " ", B3 ) )

Die obige Formel extrahiert den Nachnamen aus der vollständigen Namensbeschreibung in Zelle B3. Die Funktion „LÄNGE“ ermittelt die Gesamtzahl der Zeichen aus dem vollständigen Namen. Die Funktion „FINDEN“ ermittelt die Position des Leerzeichens, das Vor- und Nachnamen trennt. Wenn Sie die Gesamtzahl der Zeichen des Vornamens von der Position des Leerzeichens subtrahieren, erhalten Sie die Anzahl der Zeichen des Nachnamens. Diese Information wird dann in der Funktion „RECHTS“ verwendet, um den Nachnamen zu extrahieren.

3. Vor- und Nachnamen mit der FILTERXML-Funktion trennen

Eine clevere Möglichkeit, Text basierend auf dem Leerzeichen zu teilen, ist die Verwendung der Funktion „FILTERXML“. Diese ist dazu gedacht, spezifische Teile von XML-Daten basierend auf dem Knotennamen zu extrahieren.

Aber Sie können sie auch verwenden, um Namen zu extrahieren, indem Sie zuerst die XML-Daten aus Ihrem Namen erstellen.

= TRANSPONIEREN ( FILTERXML ( "<t><s>" & WECHSELN ( B3," ","</s><s>")&"</s></t>","//s"))

Die obige Formel teilt den vollständigen Namen in Zelle B3 in Vor- und Nachnamen.

Die Funktion „WECHSELN“ wird verwendet, um das Leerzeichen durch einen Text wie oben zu ersetzen. Dies erzeugt eine XML-Datenstruktur, und Sie können die Funktion „FILTERXML“ verwenden, um jeden der Namen zu extrahieren.

Nur den Vornamen erhalten

= FILTERXML ( "<t><s>" & WECHSELN ( B3, " ", "</s><s>" ) & "</s></t>", "//s[1]" )

Die vorherige Version der FILTERXML-Formel gibt nur den Vornamen zurück. Der Filter //s[1] gibt nur das erste Element in einem s-Tag zurück.

Nur den Nachnamen erhalten

= FILTERXML ( "<t><s>" & WECHSELN ( B3, " ", "</s><s>" ) & "</s></t>", "//s[2]" )

Ebenso verwendet die vorherige Formel den Filter //s[2], um das zweite Element in einem s-Tag zurückzugeben, welches der Nachname ist.

4. Vor- und Nachnamen mit der TEXTTEILEN-Funktion trennen

Wenn Sie eine neuere Excel-Version haben, können Sie komplexe Formellösungen vermeiden, da es eine spezielle Funktion zum Teilen von Text gibt.

Sie können die Funktion „TEXTTEILEN“ verwenden, um einen Textwert basierend auf einem Trennzeichen zu teilen.

Das bedeutet, Sie können das Leerzeichen als Trennzeichen verwenden, um Vor- und Nachnamen zu trennen.

= TEXTTEILEN ( B3, " " )

Die obige Formel teilt den Namen in Zelle B3 basierend auf dem Leerzeichen. Das ist eine der einfachsten Möglichkeiten, die Sie bekommen können!

5. Vor- und Nachnamen mit „Blitzvorschau“ trennen

„Blitzvorschau“ ist eine praktische Möglichkeit, Ihre Daten basierend auf Beispielen zu transformieren. Sie geben einige Beispiele der gewünschten Ergebnisse in der angrenzenden Spalte ein, und die „Blitzvorschau“ ermittelt das Muster und füllt den Rest aus.

Dies kann Ihre Namen leicht trennen, wenn Sie die Beispielergebnisse angeben.

Hier ist, wie es funktioniert:

1. Geben Sie einige Vornamen manuell in die Zellen rechts neben dem entsprechenden vollständigen Namen ein.
2. Dies sollte „Blitzvorschau“ aktivieren, und Sie sehen die vorgeschlagenen Ergebnisse in hellem Grau. Drücken Sie „Enter“, um diese Ergebnisse zu akzeptieren und in die Zellen einzufügen.
3. Wenn „Blitzvorschau“ nach den ersten zwei oder drei Eingaben nicht automatisch aktiviert wird, müssen Sie es manuell aktivieren.
4. Wählen Sie die erste leere Zelle unter Ihren Beispielvornamen aus.
5. Gehen Sie zur Registerkarte „Daten“ und klicken Sie auf den Befehl „Blitzvorschau“ in der Gruppe „Datentools“.
6. Wiederholen Sie den Vorgang in der nächsten Spalte, um die Nachnamen zu erhalten.

6. Vor- und Nachnamen mit Power Query trennen

Power Query ist ein erstaunliches Tool, um Ihre Daten praktisch auf jede erdenkliche Weise zu transformieren. Das Trennen Ihrer Namen ist eine einfache Aufgabe für Power Query!

Power Query ist definitiv der richtige Weg für Ihre Aufgabe, Vor- und Nachnamen in separaten Zellen zu erhalten, wenn Sie einen wiederholbaren Prozess zum Importieren und Transformieren Ihrer Daten in Excel wünschen.

Bevor Sie Power Query verwenden, müssen Sie Ihre Namensdaten zu einer Excel-Tabelle hinzufügen.

Hier erfahren Sie, wie Sie Power Query verwenden, um Namen in Ihrer Tabelle zu teilen:

1. Gehen Sie zur Registerkarte „Daten“.
2. Klicken Sie auf den Befehl „Aus Tabelle/Bereich“.
3. Dies öffnet den Power Query-Editor.
4. Klicken Sie mit der rechten Maustaste auf die Spaltenüberschrift, die die zu teilenden Namen enthält, und wählen Sie „Spalte teilen“.
5. Wählen Sie im Untermenü die Option „Nach Trennzeichen“.
6. Wählen Sie „Leerzeichen“ aus dem Dropdown-Menü „Trennzeichen auswählen oder eingeben“.
7. Drücken Sie die Schaltfläche „OK“.

Jetzt haben Sie zwei neue Spalten mit getrennten Vor- und Nachnamen.

7. Vor- und Nachnamen mit Power Pivot trennen

Wenn Sie Daten analysieren, die einen vollständigen Namen enthalten, möchten Sie die Namen möglicherweise mit Power Pivot teilen.

Das Power Pivot-Datenmodell kann eine große Anzahl von Zeilen im Speicher enthalten, daher ist es perfekt für die Arbeit mit jedem riesigen Datensatz.

Nachdem Sie Ihren Datensatz in das Datenmodell geladen haben, können Sie weitere Spalten mit Berechnungen mithilfe der DAX-Formelsprache hinzufügen.

Die DAX-Formelsprache hat viele gemeinsame Funktionen mit Excel, und in diesem Fall werden die verwendeten Funktionen genau die gleichen sein wie die zuvor gesehenen Textfunktionen.

Sie können Ihre Daten zum Datenmodell hinzufügen. Wählen Sie eine Zelle in der Tabelle aus, die Ihre Namen enthält, und gehen Sie zur Registerkarte „Power Pivot“, klicken Sie dann auf die Option „Zu Datenmodell hinzufügen“.

Dies öffnet den Power Pivot-Editor mit der in das Modell geladenen Tabelle.

Sie können neue Spalten hinzufügen, indem Sie auf die Überschrift „Spalte hinzufügen“ rechts neben Ihren Daten klicken.

= LINKS ( Namen[Vollständiger Name], FINDEN ( " ", Namen[Vollständiger Name] ) - 1 )

Die obige Formel extrahiert den Vornamen für die gesamte Spalte.

= RECHTS ( Namen[Vollständiger Name], LÄNGE ( Namen[Vollständiger Name] ) - FINDEN ( " ", Namen[Vollständiger Name] ) )

Die obige Formel liefert den Nachnamen für die gesamte Spalte.

Jetzt können Sie diese berechneten Spalten in einer PivotTabelle verwenden.

8. Vor- und Nachnamen mit VBA trennen

Vielleicht müssen Sie Namen oft trennen und wünschen sich eine Ein-Klick-Lösung dafür.

VBA könnte die beste Lösung für Sie sein.

Sie können Ihrem Arbeitsblatt ein Makro hinzufügen, das die Namen in angrenzende Spalten teilt.

Dies kann durch Hinzufügen des Makros zur Symbolleiste für den Schnellzugriff erfolgen, damit es immer mit einem Klick verfügbar ist.

Hier erfahren Sie, wie:

1. Gehen Sie zur Registerkarte „Entwickler“ und klicken Sie auf den Befehl „Visual Basic“.
2. Erstellen Sie ein neues Modul im Visual Basic-Editor und fügen Sie den folgenden Code hinzu:

Sub SplitNames()
Dim rng As Range
Dim arrNames() As String
Dim colCount As Integer
colCount = Selection.Columns.Count
If colCount <> 1 Then
MsgBox ("Select a single column!")
End
End If
For Each rng In Selection
arrNames = Split(rng.Value, " ")
rng.Offset(0, 1).Value = arrNames(0)
rng.Offset(0, 2).Value = arrNames(1)
Next rng
End Sub

Dieser Code überprüft zuerst, ob Sie nur einen Bereich mit einer einzelnen Spalte ausgewählt haben. Wenn Sie mehrere Spalten ausgewählt haben, werden Sie darauf hingewiesen, eine einzelne Spalte auszuwählen, und die Ausführung wird beendet.

Wenn eine einzelne Spalte ausgewählt ist, iteriert der Code durch den ausgewählten Bereich und teilt jede Zelle basierend auf dem Leerzeichen.

Der erste und zweite Teil der geteilten Namen werden in die Zellen direkt rechts von Ihrer ursprünglichen Auswahl eingefügt.

Nachdem Sie Ihre Namen ausgewählt und den Code ausgeführt haben, haben Sie Vor- und Nachnamen in Excel getrennt.

9. Vor- und Nachnamen mit Office Scripts trennen

Wenn Sie hauptsächlich Excel online verwenden, müssen Sie Office Scripts verwenden, um einen benutzerdefinierten Code zum Trennen Ihrer Namen zu erstellen.

Sie können ein ähnliches Office-Skript erstellen:

1. Gehen Sie zur Registerkarte „Automatisieren“ und klicken Sie auf den Befehl „Neues Skript“, um den „Code-Editor“ für Office Scripts zu öffnen.
2. Fügen Sie den folgenden Code in den Editor ein:

   function main(workbook: ExcelScript.Workbook) {
   //getselected range
   let rng = workbook.getSelectedRange();
   let rows = rng.getRowCount();
   let cols = rng.getColumnCount();
   if (cols != 1) {
   return;
   };
   //loop through selected cells
   for (let i = 0; i < rows; i++) {
   for (let j = 0; j < cols; j++) {
   //split name based on space
   let txtName = rng.getCell(i, j).getValue().toString();
   let arrName = txtName.split(" ");
   //enter names in adjacent cells
   rng.getCell(i, j + 1).setValue(arrName[0]);
   rng.getCell(i, j + 2).setValue(arrName[1]);
   };
   };
   };
   

Fügen Sie den Code in den „Code-Editor“ ein und klicken Sie auf die Schaltfläche „Speichern“.

1. Jetzt können Sie einen einzelnen Spaltenbereich von Namen in Ihrem Excel-Blatt auswählen und die Schaltfläche „Ausführen“ drücken.
2. Der Code überprüft, ob Ihre Auswahl eine einzelne Spalte ist, und beendet die Codeausführung andernfalls.

Andernfalls fährt der Code fort und iteriert durch Ihre ausgewählten Zellen, um die Namen zu teilen. Vor- und Nachnamen werden in den Spalten rechts von Ihrer Auswahl hinzugefügt.

Fazit

Viele Datensätze werden ein Namensfeld enthalten. Manchmal wird dies als vollständiger Name in einer einzelnen Zelle bereitgestellt, und Sie müssen den Namen in die Komponenten Vor- und Nachname trennen. „Text in Spalten“ oder „Blitzvorschau“ lösen das Problem mit wenigen einfachen Schritten und sind perfekt für einmalige Anwendungen.

Eine Formellösung stellt eine dynamischere Lösung dar, die besser geeignet ist, wenn der Quelldatensatz Namen hinzufügen oder ändern wird. Die Formel, die Sie verwenden, hängt von den in Ihrer Excel-Version verfügbaren Funktionen ab. Für größere Datensätze oder externe Datensätze, die zuerst in Excel importiert werden müssen, funktioniert die Power Query- oder Power Pivot-Lösung am besten. Haben Sie jemals die Notwendigkeit gehabt, Namen zu trennen? Wie haben Sie es gelöst? Lassen Sie es mich in den Kommentaren unten wissen!

Pubblicato in Excel