Textzeichenfolgen nach Trennzeichen in mehrere Zeilen aufteilen – 3 Schnelltipps

Normalerweise können Sie die Funktion „Text in Spalten“ verwenden, um Zellinhalte anhand eines bestimmten Trennzeichens – beispielsweise Komma, Punkt, Semikolon, Schrägstrich usw. – in mehrere Spalten aufzuteilen. Manchmal müssen Sie jedoch die durch Trennzeichen getrennten Zellinhalte in mehrere Zeilen aufteilen und dabei die Daten aus anderen Spalten entsprechend wiederholen, wie im folgenden Screenshot dargestellt. Kennen Sie eine gute Methode, um diese Aufgabe in Excel zu bewältigen? In diesem Tutorial werden einige effektive Methoden vorgestellt, um diese Aufgabe in Excel zu erledigen.

Video: Textzeichenfolgen nach Trennzeichen in mehrere Zeilen aufteilen

Methode A: Getrennten Text mithilfe von VBA-Code in mehrere Zeilen aufteilen

In diesem Abschnitt stelle ich Ihnen zwei VBA-Codes vor, mit denen Sie Zellinhalte ganz einfach anhand eines bestimmten Trennzeichens aufteilen können.

Text aufteilen, der durch Kommas, Leerzeichen, Semikolons usw. getrennt ist

Um Textzeichenfolgen aufzuteilen, die durch gängige Trennzeichen wie Komma, Punkt, Leerzeichen, Semikolon, Schrägstrich usw. getrennt sind, können Sie den folgenden Code nutzen – so geht’s:

Hinweis: Dieser Code unterstützt keine Rückgängig-Funktion. Sichern Sie Ihre Daten daher unbedingt, bevor Sie den Code ausführen.

Schritt 1: Öffnen Sie den VBA-Modul-Editor und kopieren Sie den Code

1. Aktivieren Sie das gewünschte Arbeitsblatt, und drücken Sie anschließend die Tastenkombination „Alt“ + „F11“, um das Fenster „Microsoft Visual Basic for Applications“ zu öffnen.

2. Klicken Sie im geöffneten Fenster auf „Einfügen“ > „Modul“, um ein neues, leeres Modul zu erstellen.

3. Kopieren Sie anschließend den folgenden Code und fügen Sie ihn in das leere Modul ein.

VBA-Code: Text nach einem bestimmten Trennzeichen (Komma, Punkt, Leerzeichen usw.) aufteilen

Sub SplitTextIntoRows()
'UpdatebyExtendoffice
    Dim xSRg, xIptRg, xCrRg, xRg As Range
    Dim xSplitChar As String
    Dim xArr As Variant
    Dim xFNum, xFFNum, xRow, xColumn, xNum As Integer
    Dim xWSh As Worksheet
    Set xSRg = Application.InputBox("Select a range:", "Kutools for Excel", , , , , , 8)
    If xSRg Is Nothing Then Exit Sub
    xSplitChar = Application.InputBox("Type delimiter:", "Kutools for Excel", , , , , , 2)
    If xSplitChar = "" Then Exit Sub
    Application.ScreenUpdating = False
    xRow = xSRg.Row
    xColumn = xSRg.Column
    Set xWSh = xSRg.Worksheet
    For xFNum = xSRg.Rows.Count To 1 Step -1
        Set xRg = xWSh.Cells.Item(xRow + xFNum - 1, xColumn)
        xArr = Split(xRg, xSplitChar)
        xIndex = UBound(xArr)
        For xFFNum = LBound(xArr) To UBound(xArr)
            xRg.EntireRow.Copy
            xRg.Offset(1, 0).EntireRow.Insert Shift:=xlShiftDown
            xRg.Worksheet.Cells(xRow + xFNum, xColumn) = xArr(xIndex)
            xIndex = xIndex - 1
        Next
        xRg.EntireRow.Delete
    Next
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub   

Schritt 2: Führen Sie den Code aus, um das Ergebnis zu erhalten

1. Nachdem Sie den Code eingefügt haben, drücken Sie „F5“, um ihn auszuführen. Anschließend erscheint ein Eingabefeld, in dem Sie die Zellen auswählen, die den zu teilenden Text enthalten. Siehe Screenshot:

2. Klicken Sie anschließend auf „OK“. Es erscheint ein weiteres Eingabefeld, in dem Sie das Trennzeichen eingeben, nach dem die Daten getrennt werden sollen. Hier gebe ich ein Komma gefolgt von einem Leerzeichen (, ) ein. Siehe Screenshot:

3. Klicken Sie abschließend auf die Schaltfläche „OK“. Anschließend sehen Sie, dass die ausgewählten Textzeichenfolgen anhand des Kommas in mehrere Zeilen aufgeteilt wurden und die zugehörigen Daten aus anderen Spalten entsprechend wiederholt wurden – wie in den folgenden Screenshots dargestellt:

Text aufteilen, der durch Zeilenumbruch getrennt ist

Wenn Ihre Zellinhalte durch Zeilenumbrüche getrennt sind und Sie diese auf mehrere Zeilen aufteilen möchten, steht Ihnen hier ein weiterer VBA-Code zur Verfügung, der Ihnen dabei hilft.

Hinweis: Dieser Code unterstützt keine Rückgängig-Funktion. Sichern Sie Ihre Daten daher vor der Anwendung des Codes.

Schritt 1: Öffnen Sie den VBA-Modul-Editor und kopieren Sie den Code

1. Drücken Sie die Tastenkombination „Alt“ + „F11“, um das Fenster „Microsoft Visual Basic for Applications“ zu öffnen.

2. Klicken Sie im geöffneten Fenster auf „Einfügen“ > „Modul“, um ein neues leeres Modul zu erstellen.

3. Kopieren Sie anschließend den folgenden Code und fügen Sie ihn in das leere Modul ein.

VBA-Code: Text nach Zeilenumbruch aufteilen

Sub SplitTextIntoRows()
'UpdatebyExtendoffice
    Dim xSRg, xIptRg, xCrRg, xRg As Range
    Dim xSplitChar As String
    Dim xArr As Variant
    Dim xFNum, xFFNum, xRow, xColumn, xNum As Integer
    Dim xWSh As Worksheet
    Set xSRg = Application.InputBox("Select a range:", "Kutools for Excel", , , , , , 8)
    If xSRg Is Nothing Then Exit Sub
    xSplitChar = Chr(10)
    Application.ScreenUpdating = False
    xRow = xSRg.Row
    xColumn = xSRg.Column
    Set xWSh = xSRg.Worksheet
    For xFNum = xSRg.Rows.Count To 1 Step -1
        Set xRg = xWSh.Cells.Item(xRow + xFNum - 1, xColumn)
        xArr = Split(xRg, xSplitChar)
        xIndex = UBound(xArr)
        For xFFNum = LBound(xArr) To UBound(xArr)
            xRg.EntireRow.Copy
            xRg.Offset(1, 0).EntireRow.Insert Shift:=xlShiftDown
            xRg.Worksheet.Cells(xRow + xFNum, xColumn) = xArr(xIndex)
            xIndex = xIndex - 1
        Next
        xRg.EntireRow.Delete
    Next
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub 

Schritt 2: Führen Sie den Code aus, um das Ergebnis zu erhalten

1. Nachdem Sie den Code eingefügt haben, drücken Sie die Taste „F5“, um ihn auszuführen. Wählen Sie im erscheinenden Dialogfeld „Bitte auswählen“ die Zellen aus, die Sie aufteilen möchten. Siehe Screenshot:

2. Klicken Sie anschließend auf die Schaltfläche „OK“. Die Daten in den ausgewählten Zellen werden nun – wie im folgenden Screenshot dargestellt – in mehrere Zeilen aufgeteilt:

Methode B: In 10 Sekunden getrennten Text mithilfe von Kutools für Excel in mehrere Zeilen aufteilen

Wenn Sie „Kutools für Excel“ installiert haben, können Sie mithilfe der Funktion „In mehrere Zeilen aufteilen“ Textzeichenfolgen ganz einfach nach einem beliebigen, von Ihnen festgelegten Trennzeichen in mehrere Zeilen aufteilen. Gehen Sie dazu wie folgt vor:

Schritt 1: Wählen Sie die Funktion In mehrere Zeilen aufteilen aus

Klicken Sie auf „Kutools“ > „Vereinigen/Aufteilen“ > „In mehrere Zeilen aufteilen“. Siehe Screenshot:

Schritt 2: Geben Sie die Datenzellen und das Trennzeichen für die Aufteilung an

Im erscheinenden Dialogfeld verwenden Sie die folgenden Optionen:

• 1). Wählen Sie im Textfeld „Bereich (einzige Spalte)“ die Liste der Zellen aus, die Sie aufteilen möchten;
• 2). Wählen Sie anschließend das Trennzeichen aus, mit dem Ihre Daten getrennt sind. Hier wähle ich „Andere“ und gebe ein Komma gefolgt von einem Leerzeichen (, ) in das Textfeld ein.
• 3). Klicken Sie zum Abschluss auf die Schaltfläche „OK“.

Die ausgewählten Zellen mit getrennten Textzeichenfolgen wurden nun anhand des angegebenen Trennzeichens in mehrere Zeilen aufgeteilt. Siehe Screenshot:

Tipp: Möchten Sie die Originaldaten wiederherstellen, drücken Sie einfach Strg + Z, um den Vorgang rückgängig zu machen.

Einfach zu bedienen? Interessiert an dieser Funktion? Dann klicken Sie hier, um Ihre kostenlose 30-tägige Testversion herunterzuladen.

Methode C: Getrennten Text mithilfe von Power Query in mehrere Zeilen aufteilen

Wenn Sie Office 365 oder Excel 2016 bzw. eine neuere Version nutzen, bietet Ihnen „Power Query“ ein leistungsstarkes Werkzeug, um getrennte Texte automatisch in mehrere Zeilen oder Spalten aufzuteilen – ideal, wenn Ihre aufgeteilten Daten stets synchron mit den Originaldaten bleiben sollen. Gehen Sie dazu wie folgt vor:

Schritt 1: Tabelle in Power Query laden

1. Wählen Sie den gewünschten Datenbereich aus und klicken Sie anschließend auf „Daten“ > „Aus Tabelle“. Siehe Screenshot:

Tipp: Klicken Sie in Excel 2019 und Office 365 auf „Daten“ > „Aus Tabelle/Bereich“.

2. Klicken Sie im erscheinenden Dialogfeld „Tabelle erstellen“ auf „OK“, um die Tabelle zu erstellen. Siehe Screenshot:

3. Das Fenster „Power Query-Editor“ wird nun mit Ihren Daten angezeigt. Siehe Screenshot:

Schritt 2: Transformationen in Power Query durchführen

1. Wählen Sie die Spalte aus, die Sie aufteilen möchten, und klicken Sie anschließend auf „Start“ > „Spalte teilen“ > „Nach Trennzeichen“. Siehe Screenshot:

2. Im Dialogfeld „Spalte nach Trennzeichen teilen“:

Um Textzeichenfolgen nach Komma, Leerzeichen, Semikolon oder ähnlichen Trennzeichen zu teilen, gehen Sie wie folgt vor:

• 1). Wählen Sie aus der Dropdown-Liste „Trennzeichen auswählen oder eingeben“ dasjenige Trennzeichen aus, nach dem Ihre Daten getrennt sind;
• 2). Wählen Sie im Abschnitt „Aufteilen bei“ die Option „Jedes Vorkommen des Trennzeichens“.
• 3). Klicken Sie dann auf „Erweiterte Optionen“, um den Abschnitt zu erweitern, und wählen Sie unter „Aufteilen in“ die Option „Zeilen“ aus.
• 4). Klicken Sie abschließend auf „OK“, um das Dialogfeld zu schließen.

Um Textzeichenfolgen nach Zeilenumbruch in mehrere Zeilen aufzuteilen, gehen Sie wie folgt vor:

• 1). Wählen Sie „Benutzerdefiniert“ aus der Dropdown-Liste „Trennzeichen auswählen oder eingeben“.
• 2). Klicken Sie in der Dropdown-Liste „Sonderzeichen einfügen“ auf „Zeilenumbruch“. Daraufhin wird automatisch die Zeichenfolge „#(lf)“ in das Textfeld unter „Benutzerdefiniert“ eingefügt.
• 3). Wählen Sie im Abschnitt „Aufteilen bei“ die Option „Jedes Vorkommen des Trennzeichens“.
• 4). Klicken Sie anschließend auf „Erweiterte Optionen“, um den Abschnitt zu erweitern, und wählen Sie unter „Split into“ die Option „Rows“ aus.
• 5). Klicken Sie abschließend auf „OK“, um das Dialogfeld zu schließen.

3. Die Datenauswahl wurde nun in mehrere Zeilen aufgeteilt, wie im folgenden Screenshot dargestellt:

Schritt 3: Power Query-Ergebnis als Excel-Tabelle ausgeben

1. Anschließend geben Sie die Daten in Ihr Arbeitsblatt aus, indem Sie auf „Start“ > „Schließen & Laden“ > „Schließen & Laden“ bzw. „Schließen & Laden nach“ klicken (hier wähle ich „Schließen & Laden“). Siehe Screenshot:

Tipp: Klicken Sie auf „Schließen & Laden“, werden die Daten in ein neues Arbeitsblatt ausgegeben; wählen Sie „Schließen & Laden nach“, landen sie stattdessen in einem beliebigen anderen Blatt Ihrer Wahl.

2. Schließlich werden die Daten in ein neues Arbeitsblatt geladen. Siehe Screenshot:

Tipp: Wenn Sie Ihre Daten in der Originaltabelle häufig aktualisieren müssen, machen Sie sich keine Sorgen – klicken Sie einfach mit der rechten Maustaste auf die Ergebnistabelle und wählen Sie „Aktualisieren“, um das neue Ergebnis dynamisch zu erhalten.

Verwandte Artikel: