In sommige gevallen moet u cellen in Excel splitsen.Dit kan zijn dat u gegevens uit een database haalt, gegevens kopieert van internet of gegevens 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 voor- en achternaam.
Of u krijgt het adres' en u wilt het adres splitsen zodat u de stad of het wachtwoord afzonderlijk kunt analyseren.
Cellen splitsen in Excel
In deze zelfstudie leert u hoe u cellen in Excel kunt splitsen met behulp van de volgende technieken:
- Gebruik de functie Tekst naar kolommen.
- Gebruik Excel-tekstfuncties.
- Gebruik Flash Fill (beschikbaar in 2013 en 2016).
Laten we beginnen!
Cellen splitsen in Excel met tekst naar kolom
Hieronder heb ik de namen vermeld van enkele van mijn favoriete fictieve personages die ik graag in afzonderlijke cellen zou willen scheiden. :
Hier zijn de stappen om deze namen op te splitsen in voor- en achternaam:
- Selecteer de cel met de tekst die u wilt splitsen (in dit geval A2:A7).
- Klik op het tabblad Gegevens
- Klik in de groep Gegevenshulpmiddelen op Tekst naar kolommen.
- In de wizard Tekst naar kolommen converteren:
Hierdoor wordt de tekst van de cel onmiddellijk in twee verschillende kolommen gesplitst.
opmerkingen:
- De functie Tekst naar kolommen splitst de inhoud van cellen op basis van scheidingstekens.Hoewel dit goed werkt als u voor- en achternaam wilt scheiden, wordt het in het geval van voor-, middelste en laatste namen in drie delen gesplitst.
- Het resultaat dat wordt verkregen met de functie Tekst naar kolom is statisch.Dit betekent dat als er een wijziging is in de oorspronkelijke gegevens, u het proces moet herhalen om bijgewerkte resultaten te krijgen.
Cellen splitsen in Excel met behulp van tekstfuncties
Excel-tekstfuncties zijn handig wanneer u tekstreeksen wilt snijden en dobbelen.
Hoewel de functie Tekst naar kolom statische resultaten biedt, zijn de resultaten die met de functie worden verkregen dynamisch en worden ze automatisch bijgewerkt als u de oorspronkelijke gegevens wijzigt.
Naam splitsen met voor- en achternaam
Stel dat u dezelfde gegevens als deze heeft:
naam extraheren
Gebruik de volgende formule om voornamen uit deze lijst te krijgen:
=LINKS(A2,ZOEKEN(" ",A2)-1)
Deze formule vindt het eerste spatieteken en retourneert vervolgens alle tekst vóór dat spatieteken:
Deze formule gebruikt de functie ZOEKEN om de positie van het spatieteken te krijgen.In het voorbeeld van Bruce Wayne komt het ruimtekarakter op de 6e plaats.Vervolgens gebruikt het de LEFT-functie om alle tekens links ervan te extraheren.
Achternaam extraheren
Evenzo, om de achternaam te krijgen, gebruikt u de volgende formule:
=RECHTS(A2,LEN(A2)-ZOEKEN(" ",A2))
Deze formule gebruikt de functie ZOEKEN om de locatie van de spatiebalk te vinden met behulp van de functie ZOEKEN.Vervolgens wordt dit getal afgetrokken van de totale lengte van de naam (gegeven door de LEN-functie).Dit geeft het aantal tekens in de achternaam.
Gebruik vervolgens de functie RECHTS om deze achternaam te extraheren.
Opmerking:Deze functies werken mogelijk niet correct als er voorloop-, volg- of dubbele spaties in de naam staan.Klik hier voor meer informatie over het verwijderen van voorloop-/achterloop-/dubbele spaties in Excel.
Namen splitsen met voor-, middelste en achternaam
In sommige gevallen kan het zijn dat u een combinatie van namen krijgt, waarvan sommige ook middelste namen hebben.
De formule is in dit geval een beetje ingewikkeld.
naam extraheren
Om de naam te krijgen:
=LINKS(A2,ZOEKEN(" ",A2)-1)
Dit is dezelfde formule die we gebruiken zonder een tweede naam.Het zoekt alleen naar het eerste spatieteken en retourneert alle tekens vóór de spatie.
tweede 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 met het eerste spatieteken en gebruikt het verschil in de positie van de eerste en tweede spatietekens om de middelste naam te extraheren.
Als er geen tweede naam is, retourneert de MID-functie een fout.Om fouten te voorkomen, is het verpakt in de IFERROR-functie.
Achternaam extraheren
Gebruik de volgende formule om de achternaam te krijgen:
=IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=1,RIGHT(A2,LEN(A2)-SEARCH(" ",A2)),RIGHT(A2,LEN(A2) -SEARCH(" ",A2,SEARCH(" ",A2)+1)))
Deze formule controleert op een tweede naam (door het aantal spatietekens te tellen).Als er slechts 1 spatieteken is, retourneert het gewoon alle tekst rechts van het spatieteken.
Maar als er 2 zijn, vindt het het tweede spatieteken en retourneert het het aantal tekens na de tweede spatie.
Opmerking: deze formules werken goed wanneer u alleen voor- en achternaam hebt, of voor-, midden- en achternaam.Als u echter een combinatie van achtervoegsels of begroetingen heeft, moet u de formule verder aanpassen.
Cellen splitsen in Excel met QuickFill
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 delen uit wilt halen.
Laten we bijvoorbeeld voor- en achternaamgegevens krijgen:
Snel vullen werkt door een patroon te herkennen en naar alle andere cellen te kopiëren.
Zo gebruikt u Flash Fill om namen uit een lijst te extraheren:
Hoe werkt Flash Fill?
Flash Fill vindt een patroon in de dataset en kopieert het.
Flash Fill is een verrassend slimme functie die in de meeste gevallen prima werkt.Maar in sommige gevallen gaat het ook mis.
Als ik bijvoorbeeld een lijst met namen heb met combinaties van namen, hebben sommige een middelste naam en andere niet.
Als ik in dit geval de middelste naam extraheer, retourneert Flash Fill ten onrechte de achternaam als er geen voornaam is.
Eerlijk gezegd is dit nog steeds een goede benadering van de trend.Dit is echter niet wat ik wil.
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 van een snelle vulling die geweldig werkt.
Ik heb een reeks adressen waaruit ik snel steden wil extraheren.
Om de stad snel te krijgen, voert u de plaatsnaam van het eerste adres in (in dit voorbeeld Londen in cel B2) en gebruikt u automatisch aanvullen om alle cellen in te vullen.Gebruik nu Flash Fill en u krijgt direct de plaatsnaam voor elk adres.
Op dezelfde manier kunt u het adres splitsen en elk deel van het adres extraheren.
Merk op dat dit vereist dat de adressen een homogene dataset zijn met hetzelfde scheidingsteken (komma in dit geval).
Als u Flash Fill zonder patroon probeert te gebruiken, wordt een fout als deze 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 Snel invullen)
Ik hoop dat je deze Excel-tutorial nuttig vond.
