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.
Methode A: Splits gescheiden tekst in meerdere rijen met VBA-code
In dit gedeelte zal ik twee VBA-codes introduceren om de celinhoud te splitsen die wordt gescheiden door een bepaald scheidingsteken.
Gesplitste tekst gescheiden door komma's, spaties, puntkomma's, enz.
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:
Note: Deze code niet ondersteuning 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:
Splits tekst gescheiden door een regeleinde
Als uw celinhoud wordt gescheiden door regeleinden, om ze in meerdere rijen te splitsen, is hier nog een VBA-code die u kan helpen.
Note: Deze code niet ondersteuning 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:
Methode B: 10s om gescheiden tekst in meerdere rijen te splitsen door Kutools voor Excel
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:
- 1). Selecteer de lijst met cellen waaruit u wilt splitsen Bereik (enkele kolom) tekstvak;
- 2). Kies vervolgens het scheidingsteken dat uw gegevens scheidt, hier zal ik kiezen Overige en typ een komma en een spatie (, ) in het tekstvak;
- 3). Klik ten slotte OK knop.
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.
Methode C: Splits gescheiden tekst in meerdere rijen door Power Query
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 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 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.:
- 1). Kies het scheidingsteken waarmee uw gegevens worden gescheiden door de Selecteer of voer scheidingsteken in keuzelijst;
- 2). Selecteer Elk voorkomen van het scheidingsteken van het Splits op sectie;
- 3). Dan klikken geavanceerde opties om de sectie uit te vouwen en selecteer Rijen onder de Splitsen in;
- 4). Klik ten slotte OK om dit dialoogvenster te sluiten.
Ga als volgt te werk om tekststrings in meerdere rijen op te splitsen door middel van een koppelingsonderbreking:
- 1). Kiezen Eigen van het Selecteer of voer scheidingsteken in keuzelijst;
- 2). Van de Speciale tekens invoegen naar beneden, klik Regelfeed, en de personages #(lf) wordt ingevoegd in het tekstvak onder de Eigen automatisch;
- 3). Selecteer Elk voorkomen van het scheidingsteken van de Split at sectie;
- 4). Dan klikken geavanceerde opties om de sectie uit te vouwen en selecteer Rijen onder de Splitsen in;
- 5). Klik ten slotte OK om dit dialoogvenster te sluiten.
3. Nu zijn de geselecteerde gegevens opgesplitst in meerdere rijen, zoals onderstaand screenshot laat zien:
Stap 3: Voer het uit Power Query 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.
Gerelateerde artikelen:
- Splits celwaarden in meerdere kolommen
- Als u in Excel een lijst met celwaarden in meerdere kolommen wilt splitsen met bepaalde scheidingstekens, zoals komma, spatie, punt, nieuwe regel, enz., kan normaal gesproken de ingebouwde functie Tekst naar kolommen u helpen om deze taakstap te voltooien stap voor stap. In dit artikel zal ik het hebben over enkele goede trucs waarmee u de celwaarden eenvoudig en snel in meerdere kolommen of rijen kunt splitsen.
- Splits een cel in meerdere rijen of kolommen
- Stel dat u een enkele cel heeft met meerdere inhoud gescheiden door een specifiek teken, bijvoorbeeld puntkomma, en u wilt deze lange cel opsplitsen in meerdere rijen of kolommen op basis van de puntkomma, in dit geval heeft u snelle manieren om het op te lossen in Excel?
- Splits tekststring per hoofdlettergebruik
- In Excel splitsen we tekst meestal op met een vaste breedte of scheidingsteken met de functie Tekst naar kolommen, maar heb je ooit geprobeerd tekst te splitsen in hoofdletters en kleine letters? U hebt bijvoorbeeld een lijst met gegevens en u moet deze op basis van het hoofdlettergebruik in twee kolommen splitsen: de ene kolom bevat een tekenreeks in kleine letters en de andere bevat een tekenreeks in hoofdletters, zoals onderstaand screenshot. De functie Tekst naar kolommen ondersteunt deze bewerking niet, maar ik kan trucs introduceren om snel tekststring voor geval in Excel te splitsen.
- Nummer in afzonderlijke cijfers splitsen
- Stel dat u een nummer moet opsplitsen of splitsen in afzonderlijke cijfers zoals hieronder afgebeeld, wat kunt u dan doen om dit te bereiken? Dit artikel biedt u twee methoden.
Beste Office-productiviteitstools
Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie als nooit tevoren. Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en tijd te besparen. Klik hier om de functie te krijgen die u het meest nodig heeft...
Office-tabblad Brengt een 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!
Inhoudsopgave
- Video: tekstreeksen splitsen op scheidingsteken
- Methode A: met VBA-code
- Tekst gescheiden door komma's, spaties, puntkomma's, enz.
- Tekst gescheiden door regeleinde
- Methode B: 10s door Kutools voor Excel
- Methode C: Door Power Query
- Gerelateerde artikelen
- De beste tools voor kantoorproductiviteit
- Heb je vragen? Stel ze hier.