Tekstreeksen splitsen op scheidingsteken in meerdere rijen – 3 Snelle trucs

Normaal gesproken gebruikt u de functie **Tekst naar kolommen** om celinhoud te splitsen in meerdere kolommen op basis van een specifiek scheidingsteken, zoals een komma, punt, puntkomma, slash, enzovoort. Maar soms wilt u de inhoud van cellen met scheidingstekens juist splitsen over meerdere **rijen**, waarbij de gegevens uit de andere kolommen worden herhaald — zoals in de afbeelding hieronder wordt getoond. Weet u een handige manier om dit in Excel voor elkaar te krijgen? In deze handleiding leest u over enkele effectieve methoden om deze taak soepel uit te voeren in Excel.

Video: Tekstreeksen splitsen op scheidingsteken in meerdere rijen

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

In dit gedeelte presenteer ik twee VBA-codes die u helpen celinhoud te splitsen op basis van een specifiek scheidingsteken.

Tekst splitsen gescheiden door komma, spatie, puntkomma, enzovoort

Om tekstreeksen te splitsen die zijn gescheiden door een standaard scheidingsteken—zoals een komma, punt, spatie, slash, enzovoort—helpt de volgende code u verder. Volg de onderstaande stappen:

Opmerking: Deze code ondersteunt geen ongedaanmaken. Maak daarom altijd eerst een back-up van uw gegevens voordat u de code toepast.

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

1. Activeer het werkblad dat u wilt gebruiken en druk daarna op de toetsencombinatie 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 aan te maken.

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

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

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 verkrijgen

1. Nadat u de code hebt geplakt, drukt u op F5 om deze uit te voeren. Vervolgens verschijnt een meldingsvenster waarin u wordt gevraagd de cellen te selecteren met de gescheiden tekst die u wilt splitsen – zie afbeelding:

2. Klik vervolgens op 'OK'; er verschijnt nog een meldingsvenster waarin u wordt gevraagd het scheidingsteken in te voeren waarmee u de gegevens wilt splitsen. Typ hier een komma gevolgd door een spatie (, ), zoals te zien is in de afbeelding:

3. Klik ten slotte op de knop „OK". U ziet nu dat de geselecteerde tekstreeksen zijn opgesplitst in rijen op basis van de komma, terwijl de overige kolomgegevens zijn herhaald, zoals in onderstaande afbeeldingen wordt getoond:

Tekst splitsen gescheiden door regeleinden

Als de inhoud van uw cel is gescheiden door regeleinden en u deze wilt splitsen over meerdere rijen, dan helpt de volgende VBA-code u daarbij.

Opmerking: Deze code ondersteunt geen ongedaanmaken. Maak daarom altijd eerst een back-up van uw gegevens voordat u de code toepast.

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

1. Druk op de toetsencombinatie 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 aan te maken.

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

VBA-code: Tekst splitsen op regeleinden

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 verkrijgen

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

2. Klik daarna op de knop „OK" – de gegevens in de geselecteerde cellen zijn nu netjes opgesplitst in rijen, zoals u in de afbeelding hieronder ziet:

Methode B: Splits gescheiden tekst in meerdere rijen binnen 10 seconden met Kutools voor Excel

Als u Kutools voor Excel hebt geïnstalleerd, kunt u met de functie ‘Splitsen in meerdere regels’ tekstreeksen eenvoudig splitsen in meerdere rijen op basis van elk gewenst scheidingsteken. Volg hiervoor de onderstaande stappen:

Stap 1: Selecteer de functie Splitsen in meerdere regels

Klik op "Kutools" > "Samenvoegen en splitsen" > „Splitsen in meerdere regels", zie afbeelding:

Stap 2: Geef de gegevenscellen en het scheidingsteken op voor het splitsen

Gebruik in het pop-updialoogvenster de volgende opties:

• 1). Selecteer de lijst met cellen die u wilt splitsen in het tekstvak „Bereik (enkele kolom)".
• 2). Kies vervolgens het scheidingsteken waarmee uw gegevens zijn gescheiden. Hier selecteer ik 'Overig' en typ een komma gevolgd door een spatie (, ) in het tekstvak.
• 3). Klik ten slotte op de knop 'OK'.

De geselecteerde cellen met gescheiden tekstreeksen zijn nu geconverteerd naar meerdere rijen op basis van het opgegeven scheidingsteken, zie afbeelding:

Tip: Wilt u de oorspronkelijke gegevens terugzetten? Druk dan gewoon op Ctrl + Z om de actie ongedaan te maken.

Eenvoudig in gebruik? Geïnteresseerd in deze functie?Klik dan hier 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 of nieuwere versies gebruikt, is „Power Query" een krachtig hulpmiddel waarmee u gescheiden tekst kunt splitsen in meerdere rijen of kolommen. Dit is handig als u wilt dat de Gegevens opsplitsen automatisch wordt vernieuwd wanneer uw oorspronkelijke gegevens wijzigen. Volg de volgende stappen om dit uit te voeren:

Stap 1: Haal de gegevenstabel op in Power Query

1. Selecteer het gegevensbereik dat u wilt gebruiken en klik op **Gegevens** > **Uit tabel**, zie afbeelding:

Tip: In Excel 2019 en Office 365 klikt u op Gegevens > Uit tabel/bereik.

2. Klik in het pop-updialoogvenster 'Tabel maken' op de knop 'OK' om direct een tabel aan te maken – zie afbeelding:

3. Het venster 'Power Query-editor' wordt nu weergegeven met de gegevens – zie afbeelding:

Stap 2: Voer transformaties uit in Power Query

1. Selecteer de kolom die u wilt splitsen en klik vervolgens op **Start** > **Kolom splitsen** > **Op scheidingsteken** (zie afbeelding).

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

Om tekstreeksen te splitsen op komma, spatie, puntkomma, enzovoort, gaat u als volgt te werk:

• 1). Kies in de vervolgkeuzelijst 'Selecteer of voer een scheidingsteken in' het teken waarmee uw gegevens zijn gescheiden;
• 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 kies 'Rijen' onder 'Splitsen in';
• 4). Klik ten slotte op 'OK' om het dialoogvenster te sluiten.

Om tekstreeksen te splitsen in meerdere rijen op regeleinden, gaat u als volgt te werk:

• 1). Kies 'Aangepast' in de vervolgkeuzelijst 'Selecteer of voer een scheidingsteken in';
• 2). Klik in de vervolgkeuzelijst 'Speciale tekens invoegen' op 'Nieuwe regel', zodat automatisch de tekens '#(lf)' in het tekstvak onder 'Aangepast' worden ingevoegd;
• 3). Selecteer 'Elke keer dat het scheidingsteken voorkomt' in de sectie 'Splitsen bij';
• 4). Vervolgens klikt u op 'Geavanceerde opties' om de sectie uit te vouwen en selecteert u 'Rijen' onder 'Splitsen in'.
• 5). Klik tot slot op 'OK' om dit dialoogvenster te sluiten.

3. De gegevensselectie is nu onderverdeeld in meerdere rijen, zoals in de onderstaande afbeelding wordt weergegeven:

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

1. Vervolgens exporteert u de gegevens naar uw werkblad door te klikken op **Start** > **Sluiten en laden** > **Sluiten en laden** / **Sluiten en laden naar** (in dit voorbeeld kies ik **Sluiten en laden**), zie afbeelding:

Tip: Met ‘Sluiten en laden’ worden de gegevens geëxporteerd naar een nieuw werkblad; met ‘Sluiten en laden naar’ kunt u de gegevens exporteren naar elk ander gewenst werkblad.

2. De gegevens worden uiteindelijk geladen in een nieuw werkblad, zie afbeelding:

Tip: Moet u uw gegevens in de oorspronkelijke tabel regelmatig bijwerken? Geen zorgen nodig! Klik gewoon met de rechtermuisknop op de resultaattabel en kies ‘Vernieuwen’ om direct het meest recente resultaat te krijgen.

Gerelateerde artikelen: