Er kunnen situaties zijn waarin u cellen in Excel moet splitsen. Dit kan zijn wanneer u de gegevens uit een database haalt of deze van internet kopieert of van een collega krijgt.
Een eenvoudig voorbeeld waarbij u cellen in Excel moet splitsen, is wanneer u volledige namen hebt en deze wilt splitsen in voornaam en achternaam.
Of je krijgt adres’ en je wilt het adres splitsen zodat je de steden of de pincode apart kunt analyseren.
Cellen splitsen in Excel
In deze zelfstudie leert u hoe u cellen in Excel kunt splitsen met behulp van de volgende technieken:
- De functie Tekst naar kolom gebruiken.
- Excel-tekstfuncties gebruiken.
- Flash Fill gebruiken (beschikbaar in 2013 en 2016).
Laten we beginnen!
Cellen splitsen in Excel met tekst naar kolom
Hieronder heb ik een lijst met namen van enkele van mijn favoriete fictieve personages en ik wil deze namen opsplitsen in afzonderlijke cellen:
Hier zijn de stappen om deze namen te splitsen in de voornaam en de achternaam:
- Selecteer de cellen waarin u de tekst hebt die u wilt splitsen (in dit geval A2:A7).
- Klik op het tabblad Gegevens
- Klik in de groep ‘Data Tools’ op ‘Tekst naar kolommen’.
- In de wizard Tekst naar kolommen converteren:
- Stap 1 van 3 van de wizard Tekst naar kolommen: Zorg ervoor dat Gescheiden is geselecteerd (dit is de standaardselectie). Hiermee kunt u de voornaam en de achternaam scheiden op basis van een opgegeven scheidingsteken (spatiebalk in dit geval).
- Klik op Volgende.
- Stap 2 van 3 Wizard Tekst naar kolommen: Selecteer Ruimte als scheidingsteken en deselecteer al het andere. U kunt zien hoe uw resultaat eruit zou zien in het gedeelte Gegevensvoorbeeld van het dialoogvenster.
- Klik op Volgende.
- Stap 3 van 3 Wizard Tekst naar kolommen: In deze stap kunt u het gegevensformaat specificeren en waar u het resultaat wilt hebben. Ik zal het gegevensformaat als Algemeen houden, omdat ik tekstgegevens heb om te splitsen. De standaardbestemming is A2 en als u hiermee doorgaat, vervangt deze de oorspronkelijke dataset. Als u de originele gegevens intact wilt houden, selecteert u een andere cel als bestemming. In dit geval wordt B2 geselecteerd.
- Klik op Voltooien.
- Stap 1 van 3 van de wizard Tekst naar kolommen: Zorg ervoor dat Gescheiden is geselecteerd (dit is de standaardselectie). Hiermee kunt u de voornaam en de achternaam scheiden op basis van een opgegeven scheidingsteken (spatiebalk in dit geval).
Hierdoor wordt de tekst van de cel onmiddellijk in twee verschillende kolommen gesplitst.
Opmerking:
- De functie Tekst naar kolom splitst de inhoud van de cellen op basis van het scheidingsteken. Hoewel dit goed werkt als u de voornaam en de achternaam wilt scheiden, wordt deze in het geval van de voor-, midden- en achternaam in drie delen gesplitst.
- Het resultaat dat u krijgt door de functie Tekst naar kolom te gebruiken, is statisch. Dit betekent dat als er wijzigingen zijn in de oorspronkelijke gegevens, u het proces moet herhalen om bijgewerkte resultaten te krijgen.
Cellen splitsen in Excel met tekstfuncties
Excel-tekstfuncties zijn geweldig als u tekstreeksen wilt snijden en dobbelen.
Hoewel de functie Tekst naar kolom een statisch resultaat geeft, is het resultaat dat u krijgt door het gebruik van functies dynamisch en wordt het automatisch bijgewerkt wanneer u de oorspronkelijke gegevens wijzigt.
Namen splitsen met een voornaam en achternaam
Stel dat u dezelfde gegevens heeft als hieronder weergegeven:
De voornaam extraheren
Gebruik de volgende formule om de voornaam uit deze lijst te krijgen:
=LINKS(A2,ZOEKEN(" ",A2)-1)
Deze formule zou het eerste spatieteken herkennen en vervolgens alle tekst vóór dat spatieteken retourneren:
Deze formule gebruikt de functie ZOEKEN om de positie van het spatieteken te krijgen. In het geval van Bruce Wayne staat het spatiekarakter op de 6e positie. Vervolgens worden alle tekens links ervan geëxtraheerd met behulp van de LEFT-functie.
De achternaam extraheren
Evenzo, om de achternaam te krijgen, gebruikt u de volgende formule:
=RECHTS(A2,LEN(A2)-ZOEKEN(" ",A2))
Deze formule gebruikt de zoekfunctie om de positie van de spatiebalk te vinden met de functie ZOEKEN. Vervolgens wordt dat getal afgetrokken van de totale lengte van de naam (die wordt gegeven door de LEN-functie). Dit geeft het aantal tekens in de achternaam.
Deze achternaam wordt vervolgens geëxtraheerd met behulp van de RECHTS-functie.
Opmerking: Deze functies werken mogelijk niet goed als u voorloop-, volg- of dubbele spaties in de namen hebt. Klik hier voor meer informatie over het verwijderen van voorloop-/achterloop-/dubbele spaties in Excel.
Namen splitsen met een voornaam, tweede naam en achternaam
Er kunnen gevallen zijn waarin u een combinatie van namen krijgt waarbij sommige namen ook een tweede naam hebben.
De formule is in dergelijke gevallen een beetje ingewikkeld.
De voornaam extraheren
Om de voornaam te krijgen:
=LINKS(A2,ZOEKEN(" ",A2)-1)
Dit is dezelfde formule die we gebruikten toen er geen tweede naam was. Het zoekt eenvoudig naar het eerste spatieteken en retourneert alle tekens vóór de spatie.
De middelste naam extraheren
Om de middelste naam te krijgen:
=IFERROR(MID(A2,SEARCH(" ",A2)+1,SEARCH(" ",A2,SEARCH(" ",A2)+1)-SEARCH(" ",A2)),"")
De MID-functie begint vanaf het eerste spatieteken en extraheert de middelste naam door het verschil van de positie van het eerste en het tweede spatieteken te gebruiken.
Als er geen tweede naam is, retourneert de MID-functie een fout. Om de fout te voorkomen, is het verpakt in de IFERROR-functie.
De achternaam extraheren
Gebruik de onderstaande formule om de achternaam te krijgen:
=IF(LEN(A2)-LEN(VERVANG(A2," ",""))=1,RIGHT(A2,LEN(A2)-SEARCH(" ",A2)),RIGHT(A2,LEN(A2) -ZOEKEN(" ",A2,ZOEKEN(" ",A2)+1)))
Deze formule controleert of er een tweede naam is of niet (door het aantal spatietekens te tellen). Als er maar 1 spatieteken is, retourneert het gewoon alle tekst rechts van het spatieteken.
Maar als er 2 zijn, ziet het het tweede spatieteken en retourneert het het aantal tekens na de tweede spatie.
Opmerking: deze formule werkt goed als u namen hebt die alleen de eerste naam en achternaam hebben, of de voor-, middelste en achternaam. Als u echter een mix heeft met achtervoegsels of begroetingen, moet u de formules verder aanpassen.
Cellen splitsen in Excel met Flash Fill
Flash Fill is een nieuwe functie die is geïntroduceerd in Excel 2013.
Het kan erg handig zijn als je een patroon hebt en er snel een deel uit wilt halen.
Laten we bijvoorbeeld de voornaam en de achternaamgegevens nemen:
Flash-fill werkt door patronen te identificeren en deze voor alle andere cellen te repliceren.
Hier leest u hoe u de voornaam uit de lijst kunt extraheren met Flash Fill:
- Voer in cel B2 de voornaam van Bruce Wayne in (d.w.z. Bruce).
- Als de cel is geselecteerd, ziet u een klein vierkantje aan de rechterkant van de celselectie. Dubbelklik erop. Dit zal dezelfde naam in alle cellen vullen.
- Wanneer de cellen zijn gevuld, ziet u rechtsonder het pictogram Opties voor automatisch aanvullen. Klik erop.
- Selecteer Flash Fill in de lijst.
- Zodra u Flash Fill selecteert, zult u merken dat alle cellen zichzelf bijwerken en nu de voornaam voor elke naam weergeven.
Hoe werkt Flash Fill?
Flash Fill zoekt naar de patronen in de dataset en repliceert het patroon.
Flash Fill is een verrassend slimme functie en werkt in de meeste gevallen zoals verwacht. Maar ook in sommige gevallen mislukt het.
Als ik bijvoorbeeld een lijst met namen heb met een combinatie van namen, waarvan sommige een tweede naam hebben en andere niet.
Als ik in zo'n geval de middelste naam extraheer, zal Flash Fill ten onrechte de achternaam retourneren als er geen voornaam is.
Eerlijk gezegd is dat nog steeds een goede benadering van de trend. Het is echter niet wat ik wilde.
Maar het is nog steeds een goed genoeg hulpmiddel om in je arsenaal te houden en te gebruiken wanneer dat nodig is.
Hier is nog een voorbeeld waarbij Flash Fill briljant werkt.
Ik heb een reeks adressen waaruit ik snel de stad wil halen.
Om snel de stad te krijgen, voert u de plaatsnaam in voor het eerste adres (voer in dit voorbeeld Londen in cel B2 in) en gebruik automatisch aanvullen om alle cellen te vullen. Gebruik nu Flash Fill en u krijgt direct de naam van de stad van elk adres.
Op dezelfde manier kunt u het adres splitsen en elk deel van het adres extraheren.
Houd er rekening mee dat het adres hiervoor een homogene gegevensset moet zijn met hetzelfde scheidingsteken (in dit geval een komma).
Als u Flash Fill probeert te gebruiken wanneer er geen patroon is, wordt er een fout weergegeven zoals hieronder weergegeven:
In deze zelfstudie heb ik drie verschillende manieren behandeld om cellen in Excel in meerdere kolommen te splitsen (met behulp van Tekst naar kolommen, formules en Flash Fill)
Ik hoop dat je deze Excel-zelfstudie nuttig vond.
