Tekststrings splitsen op scheidingsteken in meerdere rijen - 3 snelle trucs

Normaal gesproken kun je de functie Tekst naar Kolommen gebruiken om celinhoud te splitsen in meerdere kolommen met een specifiek scheidingsteken, zoals komma, punt, puntkomma, slash, enz. Maar soms moet je mogelijk de door scheidingstekens gescheiden celinhoud splitsen in meerdere rijen en de gegevens uit andere kolommen herhalen zoals in onderstaande schermafbeelding te zien is. Heb je goede manieren om deze taak in Excel aan te pakken? Deze handleiding introduceert enkele effectieve methoden om deze taak in Excel uit te voeren.

Video: Tekststrings splitsen op scheidingsteken in meerdere rijen

Methode A: Gescheiden tekst splitsen in meerdere rijen met VBA-code

In deze sectie introduceer ik twee VBA-codes om celinhoud die gescheiden is door een bepaald scheidingsteken te helpen splitsen.

Tekst gescheiden door komma, spatie, puntkomma, enz.

Om tekststrings te splitsen die gescheiden zijn door normale scheidingstekens, zoals komma, spatie, puntkomma, slash, enz., kan de volgende code u helpen. Volg de onderstaande stappen:

 Opmerking: Deze code ondersteunt geen Ongedaan maken, het is beter om de gegevens te backuppen voordat u deze code toepast.

Stap 1: Open de VBA-module-editor en kopieer de code

1. Activeer het werkblad dat u wilt gebruiken. Druk vervolgens op "Alt" + "F11" om het venster "Microsoft Visual Basic for Applications" te openen.

2. Klik in het geopende venster op "Invoegen" > "Module" om een nieuwe lege module te maken.

3. Kopieer en plak vervolgens de onderstaande code in de lege module.

VBA-code: Tekst splitsen op specifiek scheidingsteken (komma, punt, spatie, enz.)

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   

Stap 2: Voer de code uit om het resultaat te krijgen

1. Nadat u de code hebt geplakt, drukt u op de "F5"-toets om deze code uit te voeren. Er verschijnt een pop-upvenster waarin u wordt gevraagd de cellen te selecteren die de te splitsen tekst bevatten, zie onderstaande schermafbeelding:

2. Klik vervolgens op "OK", er verschijnt een ander pop-upvenster waarin u wordt gevraagd het scheidingsteken in te voeren waarmee u de gegevens wilt splitsen. Hier typ ik een komma en een spatie (, ), zie onderstaande schermafbeelding:

3. Klik ten slotte op de knop "OK". Nu ziet u dat de geselecteerde tekststrings zijn gesplitst in rijen op basis van de komma en dat de gegevens uit andere relevante kolommen worden herhaald zoals in onderstaande schermafbeeldingen te zien is:

Tekst gescheiden door regeleinde

Als uw celinhoud gescheiden is door regeleinden, kunt u ze splitsen in meerdere rijen met behulp van een andere VBA-code.

 Opmerking: Deze code ondersteunt geen Ongedaan maken, het is beter om de gegevens te backuppen voordat u deze code toepast.

Stap 1: Open de VBA-module-editor en kopieer de code

1. Druk op "Alt" + "F11" om het venster "Microsoft Visual Basic for Applications" te openen.

2. Klik in het geopende venster op "Invoegen" > "Module" om een nieuwe lege module te maken.

3. Kopieer en plak vervolgens de onderstaande code in de lege module.

VBA-code: Tekst splitsen op regeleinde

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 

Stap 2: Voer de code uit om het resultaat te krijgen

1. Nadat u de code hebt geplakt, drukt u op de "F5"-toets om deze code uit te voeren. Selecteer in het pop-upvenster de cellen die u wilt splitsen, zie onderstaande schermafbeelding:

2. Klik vervolgens op de knop "OK", de gegevens in de geselecteerde cellen worden gesplitst in rijen zoals in onderstaande schermafbeelding te zien is:

Methode B: 10 seconden om gescheiden tekst te splitsen in meerdere rijen met Kutools voor Excel

Als u "Kutools voor Excel" heeft geïnstalleerd, kunt u met de functie "Splitsen in meerdere regels" tekststrings splitsen in meerdere rijen door elk scheidingsteken dat u opgeeft. Volg de volgende stappen:

Stap 1: Selecteer de functie Splitsen in meerdere regels

Klik op "Kutools" > "Samenvoegen & Splitsen" > "Splitsen in meerdere regels", zie onderstaande schermafbeelding:

Stap 2: Specificeer de gegevenscellen en scheidingsteken voor splitsen

Gebruik in het pop-upvenster de volgende opties:

• 1). Selecteer de lijst met cellen die u wilt splitsen in het tekstvak "Bereik (enkele kolom)";
• 2). Kies vervolgens het scheidingsteken dat uw gegevens scheidt, hier kies ik "Aangepast" en typ een komma en een spatie (, ) in het tekstvak;
• 3). Klik ten slotte op de knop "OK".

Nu zijn de geselecteerde cellen met gescheiden tekststrings omgezet in meerdere rijen op basis van het specifieke scheidingsteken, zie onderstaande schermafbeelding:

 Tips: Als u de oorspronkelijke gegevens wilt herstellen, hoeft u alleen maar Ctrl + Z te drukken om ongedaan te maken.

Gemakkelijk te gebruiken? Geïnteresseerd in deze functie, klik om een gratis proefversie van 30 dagen te downloaden.

Methode C: Gescheiden tekst splitsen in meerdere rijen met Power Query

Als u Office 365 of Excel 2016 en latere versies gebruikt, is "Power Query" een krachtig hulpmiddel dat u kan helpen om gescheiden tekst te splitsen in meerdere rijen of kolommen. Het is handig als u wilt dat de gesplitste gegevens worden vernieuwd wanneer uw originele gegevens veranderen. Volg de volgende stappen om dit te voltooien:

Stap 1: Haal de gegevenstabel op in Power Query

1. Selecteer het gegevensbereik dat u wilt gebruiken, klik vervolgens op "Gegevens" > "Van Tabel", zie onderstaande schermafbeelding:

 Tips: In Excel 2019 en Office 365, klik op Gegevens > Van Tabel/Bereik.

2. Klik in het pop-upvenster "Tabel maken" op de knop "OK" om een tabel te maken, zie onderstaande schermafbeelding:

3. Nu wordt het venster "Power Query Editor" weergegeven met de gegevens, zie onderstaande schermafbeelding:

Stap 2: Voer transformaties uit in Power Query

1. Selecteer de kolom die u wilt splitsen. Klik vervolgens op "Start" > "Kolom splitsen" > "Op Scheidingsteken", zie onderstaande schermafbeelding:

2. In het dialoogvenster "Kolom splitsen op scheidingsteken":

Om tekststrings te splitsen op komma, spatie, puntkomma, enz., doe dan het volgende:

• 1). Kies het scheidingsteken waardoor uw gegevens gescheiden zijn uit de vervolgkeuzelijst "Selecteer of voer scheidingsteken in";
• 2). Selecteer "Elke keer dat het scheidingsteken voorkomt" in de sectie "Splitsen bij";
• 3). Klik vervolgens op "Geavanceerde opties" om de sectie uit te vouwen en selecteer "Rijen" onder "Splitsen in";
• 4). Klik ten slotte op "OK" om dit dialoogvenster te sluiten.

Om tekststrings in meerdere rijen te splitsen op regeleinde, doe dan het volgende:

• 1). Kies "Aangepast" uit de vervolgkeuzelijst "Selecteer of voer scheidingsteken in";
• 2). Klik in de vervolgkeuzelijst "Speciale tekens invoegen" op "Regeleinde", en de tekens "#(lf)" worden automatisch ingevoegd in het tekstvak onder "Aangepast";
• 3). Selecteer "Elke keer dat het scheidingsteken voorkomt" in de sectie "Splitsen bij";
• 4). Klik vervolgens op "Geavanceerde opties" om de sectie uit te vouwen en selecteer "Rijen" onder "Splitsen in";
• 5). Klik ten slotte op "OK" om dit dialoogvenster te sluiten.

3. Nu is de geselecteerde gegevens gesplitst in meerdere rijen zoals in onderstaande schermafbeelding te zien is:

Stap 3: Output de Power Query naar een Excel-tabel

1. U moet de gegevens nu outputten naar uw werkblad. Klik op "Start" > "Sluiten & Laden" > "Sluiten & Laden / Sluiten & Laden Naar", (hier klik ik op "Sluiten & Laden"), zie onderstaande schermafbeelding:

 Tips: Klik op Sluiten & Laden om de gegevens naar een nieuw werkblad te outputten; Klik op Sluiten & Laden Naar-optie, de gegevens worden naar elk ander blad dat u nodig hebt geoutput.

2. Ten slotte wordt de gegevens geladen naar een nieuw werkblad, zie onderstaande schermafbeelding:

 Tips: Als u uw gegevens in de originele tabel frequent moet bijwerken, maak u zich geen zorgen, u hoeft alleen met de rechtermuisknop op de resultaattabel te klikken en op "Vernieuwen" te klikken om dynamisch het nieuwe resultaat te krijgen.

Gerelateerde artikelen: