In einigen Fällen müssen Sie Zellen in Excel teilen.Dies können Sie sein, wenn Sie Daten aus einer Datenbank abrufen, Daten aus dem Internet kopieren oder Daten von einem Kollegen abrufen.
Ein einfaches Beispiel, wo Sie Zellen in Excel aufteilen müssen, ist, wenn Sie vollständige Namen haben und diese in Vor- und Nachnamen aufteilen möchten.
Oder Sie erhalten die Adresse und möchten die Adresse aufteilen, um die Stadt oder das Passwort separat zu analysieren.
So teilen Sie Zellen in Excel
In diesem Lernprogramm erfahren Sie, wie Sie Zellen in Excel mit den folgenden Techniken teilen:
- Verwenden Sie die Funktion „Text in Spalten“.
- Verwenden Sie Excel-Textfunktionen.
- Verwenden Sie Flash Fill (verfügbar in 2013 und 2016).
Lasst uns beginnen!
Teilen Sie Zellen in Excel mit Text in Spalte
Unten habe ich die Namen einiger meiner Lieblingsfiguren aufgelistet, die ich gerne in separate Zellen aufteilen möchte. :
Hier sind die Schritte, um diese Namen in Vor- und Nachnamen aufzuteilen:
- Wählen Sie die Zelle mit dem Text aus, 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:
Dadurch wird der Text der Zelle sofort in zwei verschiedene Spalten aufgeteilt.
Anmerkungen:
- Die Funktion „Text in Spalten“ teilt den Inhalt von Zellen basierend auf Trennzeichen.Während dies gut funktioniert, wenn Sie Vor- und Nachnamen trennen möchten, wird es im Fall von Vor-, Mittel- und Nachnamen in drei Teile geteilt.
- Das mit der Funktion „Text in Spalte“ erhaltene Ergebnis ist statisch.Das bedeutet, dass Sie bei einer Änderung der Originaldaten den Vorgang wiederholen müssen, um aktualisierte Ergebnisse zu erhalten.
Teilen Sie Zellen in Excel mithilfe von Textfunktionen
Excel-Textfunktionen sind nützlich, wenn Sie Textzeichenfolgen schneiden und würfeln möchten.
Während die Funktion „Text in Spalte“ statische Ergebnisse liefert, sind die mit der Funktion erzielten Ergebnisse dynamisch und werden automatisch aktualisiert, wenn Sie die Originaldaten ändern.
Name mit Vor- und Nachname aufteilen
Angenommen, Sie haben dieselben Daten wie diese:
Namen extrahieren
Um Vornamen aus dieser Liste zu erhalten, verwenden Sie die folgende Formel:
=LINKS(A2,SUCHE(" ",A2)-1)
Diese Formel findet das erste Leerzeichen und gibt dann den gesamten Text vor diesem Leerzeichen zurück:
Diese Formel verwendet die SEARCH-Funktion, um die Position des Leerzeichens abzurufen.In Bruce Waynes Beispiel kommt das Leerzeichen auf Platz 6.Dann verwendet es die LEFT-Funktion, um alle Zeichen links davon zu extrahieren.
Nachnamen extrahieren
Um den Nachnamen zu erhalten, verwenden Sie die folgende Formel:
=RECHTS(A2,LEN(A2)-SUCHE(" ",A2))
Diese Formel verwendet die SEARCH-Funktion, um die Position der Leertaste mithilfe der SEARCH-Funktion zu finden.Anschließend subtrahiert es diese Zahl von der Gesamtlänge des Namens (angegeben durch die LEN-Funktion).Dies gibt die Anzahl der Zeichen im Nachnamen an.
Verwenden Sie dann die RIGHT-Funktion, um diesen Nachnamen zu extrahieren.
Hinweis:Diese Funktionen funktionieren möglicherweise nicht richtig, wenn der Name führende, nachgestellte oder doppelte Leerzeichen enthält.Klicken Sie hier, um zu erfahren, wie Sie führende/nachgestellte/doppelte Leerzeichen in Excel entfernen.
Teilen Sie Namen mit Vor-, Mittel- und Nachnamen auf
In einigen Fällen erhalten Sie möglicherweise eine Kombination von Namen, von denen einige auch einen zweiten Vornamen haben.
Die Formel ist in diesem Fall etwas kompliziert.
Namen extrahieren
Um den Namen zu erhalten:
=LINKS(A2,SUCHE(" ",A2)-1)
Dies ist die gleiche Formel, die wir ohne einen zweiten Vornamen verwenden.Es sucht nur nach dem ersten Leerzeichen und gibt alle Zeichen vor dem Leerzeichen zurück.
zweiten Vornamen extrahieren
So erhalten Sie den zweiten Vornamen:
=IFERROR(MID(A2,SUCHE(" ",A2)+1,SUCHE(" ",A2,SUCHE(" ",A2)+1)-SUCHE(" ",A2)),"")
Die MID-Funktion beginnt mit dem ersten Leerzeichen und verwendet den Unterschied in der Position des ersten und zweiten Leerzeichens, um den zweiten Vornamen zu extrahieren.
Wenn kein zweiter Vorname vorhanden ist, gibt die MID-Funktion einen Fehler zurück.Um Fehler zu vermeiden, wird es in die Funktion IFERROR eingeschlossen.
Nachnamen extrahieren
Verwenden Sie die folgende Formel, um den Nachnamen zu erhalten:
=IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=1,RIGHT(A2,LEN(A2)-SEARCH(" ",A2)),RIGHT(A2,LEN(A2) -SEARCH(" ",A2,SEARCH(" ",A2)+1)))
Diese Formel sucht nach einem zweiten Vornamen (indem die Anzahl der Leerzeichen gezählt wird).Wenn nur 1 Leerzeichen vorhanden ist, wird nur der gesamte Text rechts vom Leerzeichen zurückgegeben.
Aber wenn es 2 gibt, findet es das zweite Leerzeichen und gibt die Anzahl der Zeichen nach dem zweiten Leerzeichen zurück.
Hinweis: Diese Formeln funktionieren gut, wenn Sie nur Vor- und Nachnamen oder Vor-, Mittel- und Nachnamen haben.Wenn Sie jedoch eine Kombination aus Suffixen oder Anreden haben, müssen Sie die Formel weiter modifizieren.
Teilen Sie Zellen in Excel mit QuickFill
Flash Fill ist eine neue Funktion, die in Excel 2013 eingeführt wurde.
Es kann sehr praktisch sein, wenn Sie ein Muster haben und Teile davon schnell extrahieren möchten.
Lassen Sie uns zum Beispiel Daten zum Vor- und Nachnamen abrufen:
Quick Fill funktioniert, indem es ein Muster erkennt und es in alle anderen Zellen kopiert.
So verwenden Sie Flash Fill, um Namen aus einer Liste zu extrahieren:
Wie funktioniert Flash Fill?
Flash Fill findet ein Muster im Datensatz und kopiert es.
Flash Fill ist eine überraschend intelligente Funktion, die in den meisten Fällen gut funktioniert.Aber es scheitert in manchen Fällen auch.
Wenn ich zum Beispiel eine Namensliste mit Namenskombinationen habe, haben einige einen zweiten Vornamen und andere nicht.
Wenn ich in diesem Fall den zweiten Vornamen extrahiere, gibt Flash Fill fälschlicherweise den Nachnamen zurück, falls kein Vorname vorhanden ist.
Ehrlich gesagt ist dies immer noch eine gute Annäherung an den Trend.Dies ist jedoch nicht das, was ich möchte.
Aber es ist immer noch ein gut genug Werkzeug, um es in Ihrem Arsenal zu behalten und bei Bedarf zu verwenden.
Hier ist ein weiteres Beispiel für eine schnelle Füllung, die hervorragend funktioniert.
Ich habe eine Reihe von Adressen, aus denen ich schnell Städte extrahieren möchte.
Um die Stadt schnell zu erhalten, geben Sie den Stadtnamen der ersten Adresse ein (in diesem Beispiel London in Zelle B2) und verwenden Sie die automatische Vervollständigung, um alle Zellen auszufüllen.Verwenden Sie jetzt Flash Fill und Sie erhalten sofort den Namen der Stadt für jede Adresse.
Ebenso können Sie die Adresse aufteilen und einen beliebigen Teil der Adresse extrahieren.
Beachten Sie, dass die Adressen dazu ein homogener Datensatz mit demselben Trennzeichen (in diesem Fall Komma) sein müssen.
Wenn Sie versuchen, Flash Fill ohne Muster zu verwenden, wird ein Fehler wie dieser angezeigt:
In diesem Lernprogramm habe ich drei verschiedene Möglichkeiten zum Aufteilen von Zellen in Excel in mehrere Spalten behandelt (mithilfe von „Text in Spalten“, „Formeln“ und „Quick Fill“).
Ich hoffe, Sie fanden dieses Excel-Tutorial hilfreich.
