Hoe data te splitsen met een scheidingsteken en deze in een kolom te herschikken in Excel?
Bij het werken met Excel is het gebruikelijk om cellen tegen te komen die meerdere waarden bevatten, gescheiden door komma's, puntkomma's of andere scheidingstekens. Als u een bereik van dergelijke cellen moet splitsen op basis van een specifiek scheidingsteken en alle resulterende waarden in één enkele kolom wilt samenvoegen zoals in de onderstaande schermafbeelding wordt getoond, zijn er efficiënte methoden om dit te bereiken voor een betere gegevensorganisatie en analyse.
Gegevens splitsen met een scheidingsteken en in een kolom herschikken
Gegevens splitsen met een scheidingsteken en in een kolom herschikken met een formule (Excel 365)
In Excel 365 kunnen we deze transformatie dynamisch uitvoeren met behulp van moderne functies zoals TEXTSPLIT en TEXTJOIN functies.
Klik op de cel waar u het resultaat wilt plaatsen, voer de onderstaande formule in en druk op Enter-toets. De cellen met door komma's gescheiden tekstreeksen worden direct omgezet in een lijst. Zie schermafbeelding:
=TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ", TRUE, A2:A5), ", "))
- TEXTJOIN(",", TRUE, A2:A5): Combineert alle waarden in A2:A5 tot één tekststring, gescheiden door komma's.
- TEXTSPLIT(..., ","): Splits de samengevoegde tekst in afzonderlijke waarden telkens wanneer er een komma staat.
- TRANSPOSE(...): Verandert de resulterende rij van waarden in een verticale kolom.
Gegevens splitsen met een scheidingsteken en in een kolom herschikken met Kutools voor Excel
Voor gebruikers die werken met oudere versies van Excel (2016/2019 etc. zonder TEXTSPLIT-functie), biedt de functie Gegevens opsplitsen naar rijen van Kutools voor Excel een ideale oplossing. Deze functie stelt u in staat om snel celinhoud op te splitsen in één kolom op basis van een specifiek scheidingsteken, wat u tijd en moeite bespaart.
Na het installeren van Kutools voor Excel, volg de volgende stappen:
- Klik Kutools > Samenvoegen & Opsplitsen > Gegevens opsplitsen naar rijen, zie schermafbeelding:
- In de Gegevens opsplitsen naar rijen dialoogvenster, geef de volgende bewerkingen op:
- Selecteer de cellen die u wilt splitsen;
- In de sectie Scheidingsteken, kies Anders, typ vervolgens uw scheidingsteken (bijv., een komma en een spatie) om te definiëren hoe de celinhoud moet worden gesplitst;
- Ten slotte, klik OK knop.
Resultaat: De geselecteerde cellen zijn gesplitst in een kolom op basis van de opgegeven scheidingstekens.
Gegevens splitsen met een scheidingsteken en in een kolom herschikken met VBA-code
Hier zullen we u stap voor stap laten zien hoe u VBA kunt gebruiken om celinhoud te splitsen op basis van een scheidingsteken en de resultaten in één kolom te herschikken.
1. Druk op Alt + F11 toetsen om het Microsoft Visual Basic for Applications-venster te openen.
2. Klik Invoegen > Module, en plak de onderstaande code in het nieuwe Module-venster.
VBA: Splitsen en Transponeren
Sub Vertical()
'Updateby Extendoffice
Dim i As Long, strTxt As String
Dim startP As Range
Dim xRg As Range, yRg As Range
On Error Resume Next
Set xRg = Application.InputBox _
(Prompt:="Range Selection...", _
Title:="Kutools For Excel", Type:=8)
i = 1
Application.ScreenUpdating = False
For Each yRg In xRg
If i = 1 Then
strTxt = yRg.Text
i = 2
Else
strTxt = strTxt & "," & yRg.Text
End If
Next
Application.ScreenUpdating = True
Set startP = Application.InputBox _
(Prompt:="paste range...", _
Title:="Kutools For Excel", Type:=8)
ary = Split(strTxt, ",")
i = 1
Application.ScreenUpdating = False
For Each a In ary
startP(i, 1).Value = a
i = i + 1
Next a
Application.ScreenUpdating = True
End Sub3. Druk F5 toets om de code uit te voeren, een dialoogvenster verschijnt waarin u een bereik kunt selecteren om te splitsen en te transponeren.
4. Klik OK. Een dialoogvenster verschijnt om een locatie te selecteren om de gegevens te plaatsen.
5. Klik OK, en de selectie is gesplitst en getransponeerd door middel van een komma.
🔚 Conclusie
Alle methoden lossen effectief het kernprobleem van het splitsen van gegevens met scheidingstekens op, waarbij de optimale keuze afhangt van uw technische comfortniveau en specifieke vereisten.
- Voor de meeste Excel 365-gebruikers biedt de formulebenadering de beste balans tussen kracht en onderhoudbaarheid.
- Voor frequente, complexe transformaties door niet-programmeurs is Kutools ideaal.
- Voor aangepaste, geautomatiseerde oplossingen in bedrijfsomgevingen biedt VBA de meeste controle.
Kies de methode op basis van uw specifieke behoeften. Als u geïnteresseerd bent om meer Excel-tips en -trucs te verkennen, onze website biedt duizenden tutorials om u te helpen Excel onder de knie te krijgen.
Beste productiviteitstools voor Office
Verbeter je Excel-vaardigheden met Kutools voor Excel en ervaar ongeëvenaarde efficiëntie. Kutools voor Excel biedt meer dan300 geavanceerde functies om je productiviteit te verhogen en tijd te besparen. Klik hier om de functie te kiezen die je het meest nodig hebt...
Office Tab brengt een tabbladinterface naar Office en maakt je werk veel eenvoudiger
- Activeer tabbladbewerking en -lezen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
- Verhoog je productiviteit met50% en bespaar dagelijks honderden muisklikken!
Alle Kutools-invoegtoepassingen. Eén installatieprogramma
Kutools for Office-suite bundelt invoegtoepassingen voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro, ideaal voor teams die werken met Office-toepassingen.
- Alles-in-één suite — invoegtoepassingen voor Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Eén installatieprogramma, één licentie — in enkele minuten geïnstalleerd (MSI-ready)
- Werkt beter samen — gestroomlijnde productiviteit over meerdere Office-toepassingen
- 30 dagen volledige proef — geen registratie, geen creditcard nodig
- Beste prijs — bespaar ten opzichte van losse aanschaf van invoegtoepassingen