So teilen Sie Zellen in Excel (in mehrere Spalten aufteilen)

Es kann Situationen geben, in denen Sie Zellen in Excel teilen müssen. Dies kann der Fall sein, wenn Sie die Daten aus einer Datenbank beziehen oder aus dem Internet kopieren oder von einem Kollegen beziehen.

Ein einfaches Beispiel, bei dem Sie Zellen in Excel teilen müssen, ist, wenn Sie vollständige Namen haben und diese in Vor- und Nachnamen aufteilen möchten.

Oder Sie erhalten Adresse“ und möchten die Adresse aufteilen, damit Sie die Städte oder den PIN-Code separat analysieren können.

So teilen Sie Zellen in Excel

In diesem Tutorial erfahren Sie, wie Sie Zellen in Excel mit den folgenden Techniken teilen:

• Verwenden der Text-in-Spalte-Funktion.
• Verwenden von Excel-Textfunktionen.
• Verwenden von Flash Fill (verfügbar in 2013 und 2016).

Lass uns anfangen!

Teilen Sie Zellen in Excel mit Text in Spalte

Unten habe ich eine Liste mit Namen einiger meiner Lieblingsfiguren und ich möchte diese Namen in separate Zellen aufteilen.:

Hier sind die Schritte, um diese Namen in den Vornamen und den Nachnamen aufzuteilen:

• Wählen Sie die Zellen aus, in denen Sie den Text haben, den Sie teilen möchten (in diesem Fall A2:A7).
• Klicken Sie auf die Registerkarte Daten
• Klicken Sie in der Gruppe „Datentools“ auf „Text in Spalten“.
• Im Assistenten zum Konvertieren von Text in Spalten:
  • Schritt 1 von 3 des Text-to-Spalten-Assistenten: Stellen Sie sicher, dass Getrennt ausgewählt ist (dies ist die Standardauswahl). Auf diese Weise können Sie den Vornamen und den Nachnamen anhand eines angegebenen Trennzeichens (in diesem Fall Leerzeichen) trennen.
  • Klicken Sie auf Weiter.
  • Schritt 2 von 3 Text-to-Spalten-Assistent: Wählen Sie Leerzeichen als Trennzeichen und deaktivieren Sie alles andere. Im Abschnitt Datenvorschau des Dialogfelds können Sie sehen, wie Ihr Ergebnis aussehen würde.
  • Klicken Sie auf Weiter.
  • Schritt 3 von 3 Text-to-Spalten-Assistent: In diesem Schritt können Sie das Datenformat und das gewünschte Ergebnis angeben. Ich behalte das Datenformat als Allgemein bei, da ich Textdaten aufteilen muss. Das Standardziel ist A2 und wenn Sie damit fortfahren, wird der ursprüngliche Datensatz ersetzt. Wenn Sie die Originaldaten beibehalten möchten, wählen Sie eine andere Zelle als Ziel aus. In diesem Fall wird B2 ausgewählt.
  • Klicken Sie auf Fertig stellen.

Dadurch wird der Text der Zelle sofort in zwei verschiedene Spalten aufgeteilt.

Notiz:

• Die Funktion "Text in Spalte" teilt den Inhalt der Zellen basierend auf dem Trennzeichen auf. Während dies gut funktioniert, wenn Sie den Vornamen und den Nachnamen trennen möchten, werden Vor-, Mittel- und Nachname in drei Teile geteilt.
• Das Ergebnis, das Sie durch die Verwendung der Funktion "Text in Spalte" erhalten, ist statisch. Das bedeutet, dass Sie bei Änderungen an den Originaldaten den Vorgang wiederholen müssen, um aktualisierte Ergebnisse zu erhalten.

Teilen von Zellen in Excel mit Textfunktionen

Excel-Textfunktionen eignen sich hervorragend, wenn Sie Textzeichenfolgen in Scheiben schneiden und würfeln möchten.

Während die Funktion Text in Spalte ein statisches Ergebnis liefert, ist das Ergebnis, das Sie durch die Verwendung von Funktionen erhalten, dynamisch und würde automatisch aktualisiert, wenn Sie die Originaldaten ändern.

Aufteilen von Namen, die einen Vor- und Nachnamen haben

Angenommen, Sie haben die gleichen Daten wie unten gezeigt:

Extrahieren des Vornamens

Um den Vornamen aus dieser Liste zu erhalten, verwenden Sie die folgende Formel:

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

Diese Formel würde das erste Leerzeichen erkennen und dann den gesamten Text vor diesem Leerzeichen zurückgeben:

Diese Formel verwendet die SEARCH-Funktion, um die Position des Leerzeichens zu ermitteln. Bei Bruce Wayne steht das Leerzeichen an der 6. Stelle. Es extrahiert dann alle Zeichen links davon mit der LEFT-Funktion.

Extrahieren des Nachnamens

Um den Nachnamen zu erhalten, verwenden Sie auf ähnliche Weise die folgende Formel:

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

Diese Formel verwendet die Suchfunktion, um die Position der Leertaste mithilfe der SUCHEN-Funktion zu finden. Es subtrahiert dann diese Zahl von der Gesamtlänge des Namens (die von der LEN-Funktion angegeben wird). Dies gibt die Anzahl der Zeichen im Nachnamen an.

Dieser Nachname wird dann mithilfe der RIGHT-Funktion extrahiert.

Notiz: Diese Funktionen funktionieren möglicherweise nicht richtig, wenn die Namen führende, nachgestellte oder doppelte Leerzeichen enthalten. Klicken Sie hier, um zu erfahren, wie Sie führende/nachgestellte/doppelte Leerzeichen in Excel entfernen.

Aufteilen von Namen, die einen Vornamen, zweiten Vornamen und Nachnamen haben

Es kann Fälle geben, in denen Sie eine Kombination von Namen erhalten, in denen einige Namen auch einen zweiten Vornamen haben.

Die Formel ist in solchen Fällen etwas komplex.

Extrahieren des Vornamens

Um den Vornamen zu erhalten:

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

Dies ist die gleiche Formel, die wir verwendet haben, als es keinen zweiten Vornamen gab. Es sucht einfach nach dem ersten Leerzeichen und gibt alle Zeichen vor dem Leerzeichen zurück.

Extrahieren des zweiten Vornamens

Um den zweiten Vornamen zu erhalten:

=IFERROR(MID(A2,SUCHE(" ",A2)+1,SUCHE(" ",A2,SUCHE(" ",A2)+1)-SEARCH(" ",A2)),"")

Die MID-Funktion beginnt mit dem ersten Leerzeichen und extrahiert den zweiten Vornamen unter Verwendung der Positionsdifferenz des ersten und des zweiten Leerzeichens.

In Fällen, in denen kein zweiter Vorname vorhanden ist, gibt die MID-Funktion einen Fehler zurück. Um den Fehler zu vermeiden, wird er in die IFERROR-Funktion eingeschlossen.

Extrahieren des Nachnamens

Um den Nachnamen zu erhalten, verwenden Sie die folgende Formel:

=WENN(LEN(A2)-LEN(ERSETZEN(A2," ",""))=1,RECHTS(A2,LEN(A2)-SUCHE(" ",A2)),RECHTS(A2,LEN(A2) -SUCHE(" ",A2,SUCHE(" ",A2)+1)))

Diese Formel prüft, ob ein zweiter Vorname vorhanden ist oder nicht (durch Zählen der Leerzeichen). Wenn nur 1 Leerzeichen vorhanden ist, wird einfach der gesamte Text rechts vom Leerzeichen zurückgegeben.

Wenn es jedoch 2 gibt, erkennt es das zweite Leerzeichen und gibt die Anzahl der Zeichen nach dem zweiten Leerzeichen zurück.

Hinweis: Diese Formel funktioniert gut, wenn Sie Namen haben, die entweder nur den Vornamen und den Nachnamen oder den Vor-, Mittel- und Nachnamen enthalten. Wenn Sie jedoch eine Mischung mit Suffixen oder Anreden haben, müssen Sie die Formeln weiter ändern.

Teilen Sie Zellen in Excel mit Flash Fill

Flash Fill ist eine neue Funktion, die in Excel 2013 eingeführt wurde.

Es kann sehr praktisch sein, wenn Sie ein Muster haben und schnell einen Teil davon extrahieren möchten.

Nehmen wir zum Beispiel den Vornamen und den Nachnamen:

Flash Fill funktioniert, indem Muster identifiziert und für alle anderen Zellen repliziert werden.

So können Sie mit Flash Fill den Vornamen aus der Liste extrahieren:

• Geben Sie in Zelle B2 den Vornamen für Bruce Wayne (d. h. Bruce) ein.
• Wenn die Zelle ausgewählt ist, sehen Sie am rechten Ende der Zellenauswahl ein kleines Quadrat. Doppelklicken Sie darauf. Dadurch wird derselbe Name in alle Zellen gefüllt.
• Wenn die Zellen gefüllt sind, sehen Sie unten rechts das Symbol für die Autofill-Optionen. Klick es an.
• Wählen Sie Flash Fill aus der Liste aus.
• Sobald Sie Flash Fill auswählen, werden Sie feststellen, dass sich alle Zellen selbst aktualisieren und jetzt für jeden Namen den Vornamen anzeigen.

Wie funktioniert Flash Fill?

Flash Fill sucht nach den Mustern im Datensatz und repliziert das Muster.

Flash Fill ist eine überraschend intelligente Funktion und funktioniert in den meisten Fällen wie erwartet. Aber es scheitert auch in einigen Fällen.

Zum Beispiel, wenn ich eine Liste von Namen habe, die eine Kombination von Namen enthält, wobei einige einen zweiten Vornamen haben und andere nicht.

Wenn ich in einem solchen Fall den zweiten Vornamen extrahiere, gibt Flash Fill fälschlicherweise den Nachnamen zurück, falls kein Vorname vorhanden ist.

Ehrlich gesagt ist das immer noch eine gute Annäherung an den Trend. Es ist jedoch nicht das, was ich wollte.

Aber es ist immer noch ein gutes Werkzeug, um es in Ihrem Arsenal zu behalten und bei Bedarf zu verwenden.

Hier ist ein weiteres Beispiel, bei dem Flash Fill hervorragend funktioniert.

Ich habe eine Reihe von Adressen, aus denen ich schnell die Stadt extrahieren möchte.

Um die Stadt schnell zu erhalten, geben Sie den Stadtnamen für die erste Adresse ein (in diesem Beispiel geben Sie London in Zelle B2 ein) und verwenden Sie das Autofill, um alle Zellen auszufüllen. Verwenden Sie jetzt Flash Fill und erhalten Sie sofort den Namen der Stadt von jeder Adresse.

Ebenso können Sie die Adresse aufteilen und einen beliebigen Teil der Adresse extrahieren.

Beachten Sie, dass die Adresse ein homogener Datensatz mit demselben Trennzeichen (in diesem Fall Komma) sein muss.

Falls Sie versuchen, Flash Fill zu verwenden, wenn kein Muster vorhanden ist, wird ein Fehler wie unten gezeigt angezeigt:

In diesem Tutorial habe ich drei verschiedene Möglichkeiten zum Aufteilen von Zellen in Excel in mehrere Spalten behandelt (mit Text zu Spalten, Formeln und Flash Fill).

Ich hoffe, Sie fanden dieses Excel-Tutorial nützlich.