In Excel kom je vaak situaties tegen waarin gegevens in één cel staan die je liever in meerdere kolommen wilt hebben. Denk bijvoorbeeld aan een kolom met volledige namen, zoals “de Vries, Lucas”, die je wilt opsplitsen in “Lucas” en “de Vries”. In Excel wordt in het Engels gesproken van “Separate Strings”, oftewel het scheiden of splitsen van tekststrings.
Om te beginnen zijn er verschillende mogelijkheden om dit te doen: je kunt een formule gebruiken, de optie Snel aanvullen (Flash Fill) of de functionaliteit Tekst naar kolommen. Kortom, er is niet één beste optie; de keuze hangt af van de brondata en van de persoonlijke voorkeur van de gebruiker.
In dit voorbeeld is de brondata consequent opgebouwd in hetzelfde formaat: eerst de achternaam, gevolgd door een komma, en daarna de voornaam.
Aan de hand van verschillende 4 verschillende voorbeelden wordt getoond hoe vrijwel dezelfde uitkomsten kunnen worden bereikt. Uiteindelijk is het gewenste resultaat altijd dat achternaam en voornaam van elkaar worden gescheiden.
- Voorbeeld 1: Tekst splitsen met formules
- Voorbeeld 2: Tekst splitsen met “Snel aanvullen”
- Voorbeeld 3: Tekst splitsen met “Tekst naar kolommen”
- Voorbeeld 4: TEKST.SPLITSEN() functie
Voorbeeld 1: Tekst splitsen met formules
In dit eerste voorbeeld gebruiken we de rechts() functie en de links() functie.
Voornaam met RECHTS() functie
Je wilt alleen het deel rechts van de komma, dus “Lucas”. Dat bereiken we met de volgende formule:
=RECHTS(B3; LENGTE(B3) - VIND.SPEC(",";B3) - 1)Uitleg formule
- Vind de positie van de komma
Eerst moet Excel weten waar de komma staat. Dat doet de functie VIND.SPEC(“,”,B3). In dit voorbeeld staat de komma na “de Vries”, dus op positie 9. - Bepaal de lengte van de hele tekst
Vervolgens moet Excel weten hoe lang de hele tekst is. Dat doet de functie LENGTE(B3). In dit voorbeeld is dat 15 tekens. - Bereken hoeveel tekens je rechts van de komma wilt
Nu wil je alles rechts van de komma hebben. Daarvoor gebruik je RECHTS(B3; …). Excel moet weten hoeveel tekens het van rechts moet pakken. Dat is de totale lengte minus de positie van de komma, minus 1 (want je wilt de komma zelf niet meenemen).
De formule is de volgende som geworden:
=RECHTS(B3; 15 - 9 - 1)Verder ingekort:
=RECHTS(B3; 5)Als we de formule nu uitschrijven staat: Geef als resultaat de 5 karakters vanaf rechts uit de waarde van cel B3. Het resultaat is: Lucas:
Kortom:
– De formule kijkt eerst waar de komma staat
– Bepaalt hoeveel tekens er nog over zijn na de komma
– Pakt die tekens van rechts en geeft ze als resultaat, zodat je alleen de tekst achter de komma overhoudt.
Achternaam met LINKS() functie
Ook de achternaam kun je ophalen met een formule.
=LINKS(B3;VIND.SPEC(",";B3)-1)Uitleg formule
- VIND.SPEC(“,”;B3)
Deze functie zoekt in de tekst waar de komma staat.
In dit geval staat de komma na “de Vries”, dus op positie 9. - VIND.SPEC(“,”;B3)-1
Omdat we de komma zelf niet willen meenemen, trekken we er 1 vanaf.
Dus: 9 – 1 = 8. - LINKS(B3;8)
De functie LINKS() pakt de eerste 8 tekens van de tekst.
Dat levert op: “de Vries”.
De formule is de volgende som geworden:
=LINKS(B3; 9 - 1)Verder ingekort:
=LINKS(B3; 8)Als we de formule nu uitschrijven staat: Geef de 8 karakters vanaf links uit de waarde van cel B3. Het resultaat is: de Vries:
Selecteer cel C3:D3 en trek de formule door over de hele tabel.
Voorbeeld 2: Tekst splitsen met “Snel aanvullen”
Typ in de cel C3 handmatig de voornaam in.
Klik in het lint op het tabblad Gegevens in de groep Hulpmiddelen voor gegevens op Snel aanvullen of gebruik de sneltoets CTRL + E.
Resultaat is dat Excel in de kolom kopieert wat je in cel C3 hebt ingevoerd, de waarde achter de komma:
Typ nu handmatig de achternaam “de Vries” in cel D3:
Klik in het lint op het tabblad Gegevens in de groep Hulpmiddelen voor gegevens op Snel aanvullen of gebruik de sneltoets CTRL + E.
Resultaat is dat Excel in de kolom kopieert wat je in cel D3 hebt ingevoerd, de waarde vóór de komma:
Het nadeel van Snel aanvullen is dat de waarden in kolom C (voornaam) en D (achternaam) niet automatisch worden aangepast als in kolom B iets wordt gewijzigd. Als je dat wel wilt zal je de formule moeten gebruiken uit voorbeeld 1.
Voorbeeld 3: Tekst splitsen met “Tekst naar kolommen”
Om de functionaliteit Tekst naar kolommen te gebruiken is het noodzakelijk dat de bronwaarde consequent in ingevoerd.
Selecteer de data in kolom B die je wilt scheiden:
Klik in het lint op het tabblad Gegevens in de groep Hulpmiddelen voor gegevens op Tekst naar kolommen.
In stap 1 van de wizard kies je voor Gescheiden.
In stap 2 kies je voor komma.
In stap 3 pas je de bestemming aan naar $C$3 en klik op voltooien.
Er kan een melding verschijnen dat er al gegevens bevinden en of je die wilt vervangen. Deze melding kun je op OK klikken.
In het resultaat zie je dat de achternaam, vóór de komma, in kolom C is gekopieerd. De voornaam, achter de komma, is naar kolom D gekopieerd.
Voorbeeld 4: TEKST.SPLITSEN() functie
Sinds Excel 365 is de nieuwe functie TEKST.SPLITSEN() beschikbaar. Met behulp van deze functie kun je tekst scheiden in kolommen of rijen en daarbij zelf kiezen welk scheidingsteken wordt gebruikt.
Ten eerste is het belangrijk de verschillende methoden voor het splitsen van tekst in Excel te combineren. Daarnaast moet rekening worden gehouden met de opbouw van de brondata, zodat uiteindelijk het gewenste resultaat wordt behaald: achternaam en voornaam netjes gescheiden.
- Formules bieden volledige dynamiek en passen zich automatisch aan bij wijzigingen.
- Snel aanvullen werkt snel voor eenmalige splitsingen.
- Tekst naar kolommen is handig bij grote datasets.
- TEKST.SPLITSEN() maakt gebruik van de nieuwste mogelijkheden in Excel 365.
Kortom, Excel biedt voor elke situatie een geschikte oplossing om tekst effectief en overzichtelijk te scheiden, waardoor het analyseren en verwerken van gegevens een stuk eenvoudiger wordt.