Excel: tekst met scheidingstekens splitsen in rijen

Laten we zeggen dat hier een tabel is met drie kolommen in een werkblad, de eerste geeft de prijsniveaus weer, de tweede geeft fruitnamen weer met komma's gescheiden in cellen, en de derde kolom geeft de prijzen van het fruit weer. De taak is om de gescheiden fruitnamen in rijen te splitsen en de gerelateerde prijsniveaus en prijsrijen te herhalen, zoals hieronder afgebeeld:

Hier introduceren twee methoden om deze taak op te lossen.

Splits tekst met scheidingstekens in rijen met behulp van VBA

Splits gescheiden tekst in rijen met behulp van Power Query

Hier is een VBA-code die snel gescheiden tekst in rijen kan splitsen.

1. druk op anders + F11 toetsen om het Microsoft Visual Basic voor toepassingen venster.

2. klikken Invoegen tabblad> Module om een ​​nieuwe lege module te maken.

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

VBA: tekst met scheidingstekens splitsen in rijen

Public Sub SplitTextInCellsToRows()
'UpdatebyExtendoffice20220622
    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)
        Debug.Print xRg.Address
        xArr = Split(xRg, xSplitChar)
        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(xFFNum)
        Next
        xRg.EntireRow.Delete
    Next
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

4. druk op F5 toets om op de knop Uitvoeren te klikken  om de code te activeren, verschijnt er een dialoogvenster waarin u de kolom kunt selecteren die de koptekst uitsluit die de afgebakende tekst bevat die in rijen moet worden opgesplitst. Klik OK.

5. Vervolgens verschijnt het tweede dialoogvenster om het scheidingsteken dat u gebruikt in te voeren. Hier typt u een komma (,). Klik OK.

Nu zijn de afgebakende teksten opgesplitst in rijen en zijn andere gerelateerde kolommen herhaald.

  De VBA zal de originele gegevens wijzigen, u kunt er beter eerst een kopie van opslaan.

Als u zich in Excel 2013 of latere versies bevindt, is de Power Query zal een goede keuze zijn voor het oplossen van deze klus.

1. Selecteer het gegevensbereik om op te klikken Data > Van tafel/bereik.

2. In het knallen Tabel maken dialoogvenster, vink aan Mijn tabel heeft kopteksten zoals je nodig hebt, en klik op OK.

Dan de Power Query editor venster wordt weergegeven en de gegevens zijn erin weergegeven.

3. Klik op de kolom waarvan u de gescheiden teksten in rijen wilt splitsen en klik op Transformeren tabblad> Kolom splitsen > Door scheidingsteken.

Of u kunt ook de kolom selecteren en met de rechtermuisknop op de kolomkop klikken om deze functie in het contextmenu te kiezen.

4. In de Kolom splitsen op scheidingsteken venster, kiest u het scheidingsteken dat u nodig hebt uit de Selecteer of voer scheidingsteken in sectie en kies Elk voorkomen van het scheidingsteken optie, en dan uitbreiden geavanceerde opties sectie om te kiezen Rijen keuze. Klik OK.

Nu zijn de gescheiden teksten opgesplitst in rijen.

5. Sluit vervolgens het venster, er verschijnt een dialoogvenster, klik op Houden knop.

Vervolgens wordt er een nieuw blad gemaakt met de tabel.

---

De beste tools voor kantoorproductiviteit

Kutools voor Excel lost de meeste van uw problemen op en verhoogt uw productiviteit met 80%

• Super Formula-balk (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken in gefilterd bereik...
• Voeg cellen / rijen / kolommen samen en het bewaren van gegevens; Gespleten cellen inhoud; Combineer dubbele rijen en som / gemiddelde... Voorkom dubbele cellen; Vergelijk Ranges...
• Selecteer Dupliceren of Uniek Rijen; Selecteer lege rijen (alle cellen zijn leeg); Super zoeken en fuzzy zoeken in veel werkboeken; Willekeurige selectie ...
• Exacte kopie Meerdere cellen zonder de formuleverwijzing te wijzigen; Maak automatisch verwijzingen naar meerdere bladen; Plaats kogels, Selectievakjes en meer ...
• Favoriete formules en snel invoegen, Bereiken, grafieken en afbeeldingen; Versleutel cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
• Extraheer tekst, Tekst toevoegen, Verwijderen op positie, Ruimte verwijderen; Paging-subtotalen maken en afdrukken; Converteren tussen celinhoud en opmerkingen...
• Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerd sorteren per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
• Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
• Draaitabel groeperen op weeknummer, dag van de week en meer ... Toon ontgrendelde, vergrendelde cellen door verschillende kleuren; Markeer cellen met formule / naam...

Lees verder... Alle functies Gratis proefversie van 30 dagen... Aankoop... 

---

Office-tabblad - brengt interface met tabbladen naar Office en maakt uw werk veel gemakkelijker

• Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
• Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
• Verhoogt uw productiviteit met 50% en vermindert honderden muisklikken voor u elke dag!

Lees verder... Alle functies Gratis proefversie van 30 dagen... Aankoop...