Splits tekststrings door scheidingsteken in meerdere rijen - 3 snelle trucs
Normaal gesproken kunt u de functie Tekst naar kolom gebruiken om de celinhoud op te splitsen in meerdere kolommen met een specifiek scheidingsteken, zoals een komma, punt, puntkomma, schuine streep, enz. Maar soms moet u de gescheiden celinhoud opsplitsen in meerdere rijen en herhaal de gegevens uit andere kolommen zoals onderstaand screenshot getoond. Heeft u goede manieren om met deze taak in Excel om te gaan? Deze zelfstudie introduceert enkele effectieve methoden om deze taak in Excel te voltooien.
In dit gedeelte zal ik twee VBA-codes introduceren om de celinhoud te splitsen die wordt gescheiden door een bepaald scheidingsteken.
Om de tekstreeksen te splitsen die worden gescheiden door een normaal scheidingsteken, zoals komma, spatie, puntkomma, schuine streep, enz., kan de volgende code u een plezier doen. Volg de onderstaande stappen:
Opmerking:: Deze code niet mail via ongedaan maken, kunt u beter een back-up maken van de gegevens voordat u deze code toepast.
Stap 1: Open de VBA-module-editor en kopieer de code
1. Activeer het blad dat je wilt gebruiken. En druk dan op Alt + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.
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 met een 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. Druk na het plakken van de code op F5 toets om deze code uit te voeren. Vervolgens verschijnt er een promptvenster om u eraan te herinneren dat u de cellen selecteert die de gescheiden tekst bevatten die u wilt splitsen, zie screenshot:
2. Klik vervolgens op OK, verschijnt er een ander promptvenster om u eraan te herinneren het scheidingsteken te typen waarop u de gegevens wilt splitsen. Hier typ ik een komma en een spatie (, ), zie screenshot:
3. Klik ten slotte op OK knop. Nu zult u zien dat de geselecteerde tekstreeksen worden opgesplitst in rijen op basis van de komma en andere relatieve kolomgegevens worden herhaald zoals hieronder getoonde schermafbeeldingen:
Als uw celinhoud wordt gescheiden door regeleinden, om ze in meerdere rijen te splitsen, is hier nog een VBA-code die u kan helpen.
Opmerking:: Deze code niet mail via ongedaan maken u kunt beter een back-up van de gegevens maken voordat u deze code toepast.
Stap 1: Open de VBA-module-editor en kopieer de code
1. druk op Alt + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.
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. Druk na het plakken van de code op F5 toets om deze code uit te voeren. Selecteer in het uitgeklapte vak de cellen die u wilt splitsen, zie screenshot:
2. Klik vervolgens op OK knop, worden de gegevens in de geselecteerde cellen opgesplitst in rijen zoals onderstaand screenshot getoond:
Als je hebt geïnstalleerd Kutools for Excel, Met Gegevens splitsen in rijen functie, kunt u de tekenreeksen splitsen in meerdere rijen met elk scheidingsteken dat u hebt opgegeven. Gelieve te doen met de volgende stappen:
Stap 1: Selecteer de functie Gegevens splitsen in rijen
Klik Kutools > Samenvoegen en splitsen > Gegevens splitsen in rijen, zie screenshot:
Stap 2: Specificeer de gegevenscellen en het scheidingsteken voor het splitsen
Gebruik de volgende opties in het uitgeklapte dialoogvenster:
Nu zijn de geselecteerde cellen met gescheiden tekenreeksen geconverteerd naar meerdere rijen op basis van het specifieke scheidingsteken, zie screenshot:
Tips: Als u de oorspronkelijke gegevens wilt herstellen, hoeft u alleen maar op te drukken Ctrl + Z voor ongedaan maken.
Makkelijk te gebruiken? Interesse in deze functie, alstublieft klik om te downloaden voor een gratis proefperiode van 30 dagen.
Als u Office 365 of Excel 2016 en latere versies gebruikt, Power Query is een krachtige tool die u kan helpen om gescheiden tekst op te splitsen in meerdere rijen of kolommen. Dit is handig als u wilt dat de gesplitste gegevens worden vernieuwd wanneer uw oorspronkelijke gegevens veranderen. Voer de volgende stappen uit om het te voltooien:
Stap 1: Haal de gegevenstabel op in Power Query
1. Selecteer het gegevensbereik dat u wilt gebruiken en klik vervolgens op Data > Van tafel, zie screenshot:
Tips: Klik in Excel 2019 en Office 365 op Data > Van tafel/bereik.
2. In de pop-out Tabel maken dialoogvenster, klik OK knop om een tabel te maken, zie screenshot:
3. Nu de Power Query editor venster wordt weergegeven met de gegevens, zie screenshot:
Stap 2: Voer de transformaties uit in Power Query
1. Selecteer de kolom die u wilt splitsen. En klik dan home > Kolom splitsen > Door scheidingsteken, zie screenshot:
2. In de Kolom splitsen op scheidingsteken dialoog venster:
Ga als volgt te werk om de tekenreeksen te splitsen door komma's, spaties, puntkomma's, enz.:
Ga als volgt te werk om tekststrings in meerdere rijen op te splitsen door middel van een koppelingsonderbreking:
3. Nu zijn de geselecteerde gegevens opgesplitst in meerdere rijen, zoals onderstaand screenshot laat zien:
Stap 3: Voer de Power Query uit naar een Excel-tabel
1. En dan moet u de gegevens in uw werkblad uitvoeren. Klik alstublieft home > Sluiten en laden > Sluiten en laden / Sluiten en laden naar, (hier, ik zal klikken Sluiten en laden), zie screenshot:
Tips: Klik Sluiten en laden zal de gegevens uitvoeren naar een nieuw werkblad; Klik Sluiten en laden naar optie, worden de gegevens uitgevoerd naar elk ander blad dat u nodig hebt.
2. Ten slotte worden de gegevens in een nieuw werkblad geladen, zie screenshot:
Tips: Als u uw gegevens in de oorspronkelijke tabel regelmatig moet bijwerken, hoeft u zich geen zorgen te maken. U hoeft alleen maar met de rechtermuisknop op de resultatentabel te klikken en op verversen om het nieuwe resultaat dynamisch te krijgen.