Zellen aufteilen in Excel (vollständige Anleitung mit detaillierten Schritten)

In Excel gibt es verschiedene Gründe, warum Sie Zelldaten aufteilen müssen. Beispielsweise können Rohdaten mehrere Informationen in einer einzigen Zelle enthalten, wie etwa Vollständiger Name oder Adressen. Durch das Aufteilen dieser Zellen können Sie unterschiedliche Informationstypen voneinander trennen, wodurch die Daten leichter bereinigt und analysiert werden können. Dieser Artikel dient als umfassender Leitfaden und zeigt verschiedene Möglichkeiten, Zellen aufteilen anhand bestimmter Trennzeichen in Zeilen oder Spalten aufzuteilen.

Zellen aufteilen in Excel in mehrere Spalten aufteilen

• Mit dem Assistenten „Text in Spalten“
• Einfach mit Kutools
• Mit Schnellausfüllung (Excel 2013 und höher)
• Mit Formeln

Zellen aufteilen in Excel in mehrere Zeilen aufteilen

• Mit der TEXTSPLIT-Funktion (Excel 365)
• Einfach mit Kutools
• Mit VBA-Code
• Mit Power Query (Excel 2016 und höher)

Video

Zellen aufteilen in Excel in mehrere Spalten aufteilen

Wie im folgenden Screenshot dargestellt, nehmen wir an, Sie verfügen über eine Liste mit vollständigen Namen und möchten jeden dieser Namen in Vor- und Nachname aufteilen sowie die Daten in separaten Spalten platzieren. In diesem Abschnitt stellen wir Ihnen vier Methoden vor, mit denen Sie diese Aufgabe mühelos bewältigen können.

Zellen aufteilen mit dem Assistenten „Text in Spalten“ in mehrere Spalten aufteilen

Um Zellen anhand eines bestimmten Trennzeichens in mehrere Spalten aufzuteilen, bietet sich die häufig verwendete Methode des Assistenten „Text in Spalten“ in Excel an. Im Folgenden zeige ich Ihnen Schritt für Schritt, wie Sie diesen Assistenten nutzen, um das gewünschte Ergebnis zu erzielen.

Schritt 1: Wählen Sie die aufzuteilenden Zellen aus und öffnen Sie den Assistenten „Text in Spalten“

In diesem Fall wähle ich den Bereich A2:A8, der die vollständigen Namen enthält. Wechseln Sie dann zur Registerkarte Daten und klicken Sie auf Text in Spalten, um den Assistenten „Text in Spalten“ zu öffnen.

Schritt 2: Konfigurieren Sie die Schritte nacheinander im Assistenten

1. Im Schritt 1 von 3 des Assistenten wählen Sie die Option Getrennt und klicken anschließend auf die Schaltfläche Weiter.
   

   

2. Im Schritt 2 von 3 des Assistenten wählen Sie die Trennzeichen für Ihre Daten aus und klicken anschließend auf die Schaltfläche Weiter, um fortzufahren.
   
   Da ich in diesem Fall „Vollständiger Name“ anhand von Leerzeichen in Vor- und Nachname aufteilen möchte, aktiviere ich ausschließlich das Kontrollkästchen Leerzeichen im Bereich Trennzeichen.

   

   Hinweise:

   • Wenn das gewünschte Trennzeichen in diesem Abschnitt nicht angezeigt wird, aktivieren Sie einfach das Kontrollkästchen Andere und geben Ihr eigenes Trennzeichen in das Textfeld ein.
   • Um nach einem Zeilenumbruch zu Zellen aufzuteilen, aktivieren Sie das Kontrollkästchen Andere und drücken gleichzeitig die Tasten Strg + J.
     

     

3. Im letzten Assistenten müssen Sie Folgendes konfigurieren:
   
   1) Geben Sie im Feld Ziel eine Zelle an, in der die aufgeteilten Daten platziert werden sollen. In diesem Beispiel wähle ich Zelle C2. 2) Klicken Sie auf die Schaltfläche Fertig stellen.

   

Ergebnis

Der vollständige Name in den ausgewählten Zellen wird in Vorname und Nachname aufgeteilt und in separaten Spalten platziert.

Bequemes Zellen aufteilen in mehrere Spalten mithilfe von Kutools

Wie Sie sehen können, erfordert der Text in Spalten-Assistent mehrere Schritte, um die Aufgabe abzuschließen. Falls Sie eine einfachere Methode benötigen, empfehlen wir Ihnen dringend die Zellen aufteilen-Funktion von Kutools für Excel. Mit dieser Funktion teilen Sie Zellen bequem in mehrere Spalten oder Zeilen auf – basierend auf einem bestimmten Trennzeichen – und legen alle Einstellungen in einem einzigen Dialogfeld fest.

Nach der Installation von Kutools für Excel wählen Sie Kutools > Vereinigen/Aufteilen > Zellen aufteilen, um das Zellen aufteilen-Dialogfeld zu öffnen.

1. Wählen Sie den Zellbereich mit dem Text aus, den Sie aufteilen möchten.
2. Wählen Sie die Option In Spalten aufteilen.
3. Wählen Sie Leerzeichen (oder ein beliebiges anderes Trennzeichen) aus und klicken Sie auf OK.
   

   

4. Wählen Sie eine Zielzelle aus und klicken Sie auf OK, um alle Daten aufgeteilt zu erhalten.
   

Zellen aufteilen mit Flash Fill in mehrere Spalten aufteilen

Nun kommen wir zur dritten Methode, bekannt als Flash Fill. Flash Fill wurde in Excel 2013 eingeführt und füllt Ihre Daten automatisch aus, sobald ein Muster erkannt wird. In diesem Abschnitt zeige ich Ihnen, wie Sie die Flash-Fill-Funktion nutzen, um Vorname und Nachname aus einem vollständigen Namen in einer einzelnen Spalte zu trennen.

Schritt 1: Geben Sie manuell den ersten Daten aufteilen in die Zelle neben der Originalspalte ein

In diesem Fall teile ich den vollständigen Namen in Spalte A in Vor- und Nachname auf. Der erste vollständige Name befindet sich in Zelle A2, daher wähle ich die benachbarte Zelle B2 aus und gebe den Vornamen ein. Siehe Screenshot:

Schritt 2: Wenden Sie Flash Fill an, um alle Vorname automatisch zu füllen

Geben Sie den zweiten Vornamen in die Zelle unterhalb von B2 (also B3) ein. Excel erkennt dann das Muster und zeigt eine Vorschau für den Rest der Vornamen an. Drücken Sie nun Enter, um die Vorschau zu übernehmen.

Nun sind alle Vornamen aus dem vollständigen Namen in Spalte A in Spalte B aufgeteilt.

Schritt 3: Holen Sie sich Nachname von Vollständiger Name in einer weiteren Spalte

Sie müssen die obigen Schritte 1 und 2 wiederholen, um den Nachnamen aus dem vollständigen Namen in Spalte A in die Spalte neben der Vornamenspalte zu übertragen.

Ergebnis

Zellen aufteilen mithilfe von Formeln in mehrere Spalten aufteilen

Die oben genannten Methoden sind nicht dynamisch – das heißt, sobald sich die Quelldaten ändern, muss derselbe Prozess erneut durchgeführt werden. Nehmen Sie dasselbe Beispiel wie zuvor: Um die vollständige Namensliste in Spalte A automatisch in Vornamen und Nachnamen aufzuteilen und sicherzustellen, dass die Aufteilung sich bei Änderungen der Quelldaten stets aktualisiert, verwenden Sie bitte eine der folgenden Formeln.

Verwenden Sie TEXT-Funktionen, um Zellen aufteilen anhand eines bestimmten Trennzeichens in Spalten aufzuteilen

Die in diesem Abschnitt bereitgestellten Formeln stehen in allen Excel-Versionen zur Verfügung. So wenden Sie sie an:

Schritt 1: Extrahieren Sie den Text vor dem ersten Trennzeichen (Vorname in diesem Fall)

1. Wählen Sie eine Zelle (in diesem Fall C2) aus, um den Vornamen anzuzeigen, geben Sie die folgende Formel ein und drücken Sie Enter, um den Vornamen aus A2 zu extrahieren.
   

   =LEFT(A2,SEARCH(" ",A2)-1)

   

2. Wählen Sie diese Ergebniszelle aus und ziehen Sie das AutoAusfüll-Kästchen nach unten, um die übrigen Vornamen zu erhalten.
   

   

Schritt 2: Extrahieren Sie den Text nach dem ersten Trennzeichen (Nachname in diesem Fall)

1. Wählen Sie eine Zelle (in diesem Fall D2) aus, um den Nachnamen anzuzeigen, geben Sie die folgende Formel ein und drücken Sie Enter, um den Nachnamen aus A2 zu extrahieren.
   

   =RIGHT(A2,LEN(A2)-SEARCH(" ",A2))

   

2. Wählen Sie diese Ergebniszelle aus und ziehen Sie das AutoAusfüll-Kästchen nach unten, um die übrigen Nachnamen zu erhalten.
   

   

Verwenden Sie die TEXTSPLIT-Funktion, um Zellen aufteilen anhand eines bestimmten Trennzeichens in Spalten aufzuteilen

Wenn Sie Excel für Microsoft 365 verwenden, empfehlen wir die TEXTSPLIT-Funktion. Gehen Sie wie folgt vor.

Schritt 1: Wählen Sie eine Zelle aus, um das Ergebnis anzuzeigen – hier beispielsweise Zelle C2.

Schritt 2: Geben Sie die folgende Formel ein und drücken Sie Enter

=TEXTSPLIT(A2," ")

Sie sehen, dass der gesamte durch Leerzeichen getrennte Text aus Zelle A2 auf verschiedene Spalten verteilt wird.

Schritt 3: Ziehen Sie die Formel, um alle Ergebnisse zu erhalten

Wählen Sie die Ergebniszellen in derselben Zeile aus und ziehen Sie anschließend den AutoAusfüll-Griff nach unten, um sämtliche Ergebnisse zu erhalten.

Zellen aufteilen in Excel in mehrere Zeilen aufteilen

Wie im folgenden Screenshot gezeigt, enthält der Bereich A2:A4 eine Liste mit Bestelldetails, die mithilfe eines Schrägstrichs aufgeteilt werden müssen, um verschiedene Informationen wie Artikel, Menge, Stückpreis und Datum zu extrahieren. In diesem Abschnitt stellen wir Ihnen dazu drei effektive Methoden vor.

Zellen aufteilen mithilfe der TEXTSPLIT-Funktion in mehrere Zeilen aufteilen

Wenn Sie Excel für Microsoft 365 verwenden, unterstützt Sie die TEXTSPLIT-Funktion ganz einfach. Gehen Sie wie folgt vor:

Schritt 1: Wählen Sie eine Zelle aus, in der das Ergebnis angezeigt werden soll – hier beispielsweise Zelle B6.

Schritt 2: Geben Sie die folgende Formel ein und drücken Sie Enter

=TEXTSPLIT(A2,,"/")

Der gesamte Text in A2 wird anhand des Trennzeichens „Schrägstrich“ in separate Zeilen aufgeteilt.

Um die Daten in den Zellen A3 und A4 basierend auf Schrägstrichen in einzelne Zeilen aufzuteilen, wiederholen Sie einfach die Schritte 1 und 2 mit den unten stehenden entsprechenden Formeln.

Formel in C6:

=TEXTSPLIT(A3,,"/")

Formel in D6:

=TEXTSPLIT(A4,,"/")

Ergebnis

Bequemes Zellen aufteilen in mehrere Zeilen mithilfe von Kutools

Obwohl die TEXTSPLIT-Funktion von Excel äußerst nützlich ist, steht sie ausschließlich Benutzern von Excel für Microsoft 365 zur Verfügung. Zudem müssen Sie bei mehreren aufzuteilenden Zellen in einer Spalte jeweils unterschiedliche Formeln einzeln auf jede Zelle anwenden, um die gewünschten Ergebnisse zu erhalten. Im Gegensatz dazu funktioniert die Kutools für Excel-Zellen aufteilen-Funktion in allen Excel-Versionen und bietet eine unkomplizierte, effiziente Lösung, um mehrere Zellen gleichzeitig mit nur wenigen Klicks in Zeilen oder Spalten aufzuteilen.

Nach der Installation von Kutools für Excel klicken Sie auf Kutools > Vereinigen/Aufteilen > Zellen aufteilen, um das Zellen aufteilen-Dialogfeld zu öffnen.

1. Wählen Sie den Zellbereich mit dem Text aus, den Sie aufteilen möchten.
2. Wählen Sie die Option In Zeilen aufteilen.
3. Wählen Sie ein benötigtes Trennzeichen aus (hier wähle ich die Option)Andere und gebe einen Schrägstrich ein) und klicken Sie dann auf OK.
   

   

4. Wählen Sie eine Zielzelle aus und klicken Sie auf OK, um alle Daten aufgeteilt zu erhalten.
   

Zellen aufteilen mithilfe von VBA-Code in mehrere Zeilen aufteilen

Dieser Abschnitt stellt einen VBA-Code bereit, mit dem Sie Zellen ganz einfach in mehrere Zeilen in Excel aufteilen können. Gehen Sie dazu wie folgt vor:

Schritt 1: Öffnen Sie das Fenster Microsoft Visual Basic for Applications

Drücken Sie die Tastenkombination Alt+F11, um dieses Fenster zu öffnen.

Schritt 2: Fügen Sie ein Modul ein und geben Sie den VBA-Code ein

Klicken Sie auf Einfügen > Modul und kopieren Sie anschließend den folgenden VBA-Code in das Modul (Code)-Fenster.

VBA-Code: Zellen aufteilen in mehrere Zeilen in Excel aufteilen

Option Explicit

Sub SplitCellsToRows()
'Updated by Extendoffice 20230727
    Dim inputRng As Range
    Dim outputRng As Range
    Dim cell As Range
    Dim splitValues() As String
    Dim delimiter As String
    Dim i As Long
    Dim columnOffset As Long
    On Error Resume Next
    
    Set inputRng = Application.InputBox("Please select the input range", "Kutools for Excel", Type:=8) ' Ask user to select input range
    If inputRng Is Nothing Then Exit Sub ' If the user clicked Cancel or entered nothing, exit the sub
    Set outputRng = Application.InputBox("Please select the output range", "Kutools for Excel", Type:=8) ' Ask user to select output range
    If outputRng Is Nothing Then Exit Sub ' If the user clicked Cancel or entered nothing, exit the sub
    delimiter = Application.InputBox("Please enter the delimiter to split the cell contents", "Kutools for Excel", Type:=2) ' Ask user for delimiter
    If delimiter = "" Then Exit Sub ' If the user clicked Cancel or entered nothing, exit the sub
    If delimiter = "" Or delimiter = "False" Then Exit Sub ' If the user clicked Cancel or entered nothing, exit the sub
    
    Application.ScreenUpdating = False
    
    columnOffset = 0
    For Each cell In inputRng
        If InStr(cell.Value, delimiter) > 0 Then
            splitValues = Split(cell.Value, delimiter)
            For i = LBound(splitValues) To UBound(splitValues)
                outputRng.Offset(i, columnOffset).Value = splitValues(i)
            Next i
            columnOffset = columnOffset + 1
        Else
            outputRng.Offset(0, columnOffset).Value = cell.Value
            columnOffset = columnOffset + 1
        End If
    Next cell
    
    Application.ScreenUpdating = True
End Sub

Schritt 3: Führen Sie den VBA-Code aus

Drücken Sie die Taste F5, um den Code auszuführen. Danach nehmen Sie bitte die folgenden Einstellungen vor.

1. Es erscheint ein Dialogfeld, in dem Sie die Zellen mit den aufzuteilenden Daten auswählen sollen (hier wähle ich den Bereich A2:A4). Klicken Sie nach der Auswahl auf OK.
   

   

2. Im zweiten erscheinenden Dialogfeld wählen Sie den Platzierungsbereich der Liste aus (hier: Zelle B6) und klicken dann auf OK.
   

   

3. Geben Sie im letzten Dialogfeld das Trennzeichen ein, mit dem der Zellinhalt aufgeteilt werden soll (hier: ein Schrägstrich), und klicken Sie anschließend auf die Schaltfläche OK.
   

   

Ergebnis

Ausgewählte Zellen im Bereich werden gleichzeitig in mehrere Zeilen aufgeteilt.

Zellen aufteilen mithilfe von Power Query in mehrere Zeilen aufteilen

Eine weitere Methode, um Zellen anhand eines bestimmten Trennzeichens in mehrere Zeilen aufzuteilen, ist die Verwendung von Power Query. Damit lassen sich die Daten dynamisch aufteilen – sie passen sich automatisch an Änderungen in den Quelldaten an. Der Nachteil dieser Methode: Sie erfordert mehrere Schritte zur Umsetzung. Sehen wir uns an, wie sie funktioniert.

Schritt 1: Wählen Sie die Zellen aus, die Sie in mehrere Zeilen aufteilen möchten, Daten auswählen > Aus Tabelle/Bereich

Schritt 2: Wandeln Sie die ausgewählten Zellen in eine Tabelle um

Wenn die ausgewählten Zellen nicht im Excel-Tabellenformat vorliegen, erscheint das Dialogfeld Tabelle erstellen. In diesem Dialogfeld müssen Sie lediglich prüfen, ob Excel Ihren ausgewählten Zellbereich korrekt erfasst hat, angeben, ob Ihre Tabelle eine Überschrift enthält, und anschließend auf die Schaltfläche OK klicken.
Wenn die ausgewählten Zellen bereits eine Excel-Tabelle sind, fahren Sie mit Schritt 3 fort.

Schritt 3: Wählen Sie Spalte nach Trennzeichen teilen

Ein Tabelle – Power Query-Editor-Fenster wird geöffnet. Klicken Sie unter der Registerkarte Start auf Spalte teilen > Nach Trennzeichen.

Schritt 4: Konfigurieren Sie das Dialogfeld „Spalte nach Trennzeichen teilen“

1. Im Bereich Trennzeichen auswählen oder eingebenwählen Sie das Trennzeichen „Trennzeichen angeben“, um den Text aufzuteilen (hier wähle ich)Benutzerdefiniert und gebe im Textfeld einen Schrägstrich / ein).
2. Erweitern Sie den Bereich Erweiterte Optionen (der standardmäßig ausgeblendet ist) und wählen Sie die Option Zeilen.
3. Wählen Sie im Bereich Anführungszeichenunter Dropdown-Liste die Option Keineaus;
4. Klicken Sie auf OK.
   

   

Schritt 5: Speichern und laden Sie das Daten aufteilen

1. Da ich in diesem Fall ein benutzerdefiniertes Ziel für meine geteilten Daten festlegen möchte, klicke ich auf Schließen & Laden > Schließen & Laden nach.
   

   

   Tipp: Um die geteilten Daten in einem neuen Arbeitsblatt zu laden, wählen Sie die Option Schließen & Laden.
2. Wählen Sie im Dialogfeld Daten importieren die Option Vorhandenes Arbeitsblatt aus, legen Sie eine Zelle für die Platzierung der Daten fest und klicken Sie anschließend auf OK.
   

   

Ergebnis

Anschließend werden alle Zellen im ausgewählten Bereich anhand des angegebenen Trennzeichens in separate Zeilen innerhalb derselben Spalte aufgeteilt.

Zusammenfassend hat dieser Artikel verschiedene Methoden vorgestellt, um Zellen in mehrere Spalten oder Zeilen in Excel aufzuteilen. Unabhängig davon, welchen Ansatz Sie wählen – die Beherrschung dieser Techniken steigert Ihre Effizienz bei der Arbeit mit Excel-Daten erheblich. Bleiben Sie neugierig: Sie finden garantiert die Methode, die am besten zu Ihnen passt.

Verwandte Artikel