Er kunnen situaties zijn waarin u cellen in Excel moet splitsen. Dit kan zijn wanneer u de gegevens uit een database haalt of u kopieert deze van internet of krijgt deze van een collega.
Een eenvoudig voorbeeld waarbij u cellen in Excel moet splitsen, is wanneer u volledige namen heeft en u 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.
Deze zelfstudie behandelt:
Hoe cellen in Excel te splitsen
In deze zelfstudie leert u hoe u cellen in Excel kunt splitsen met behulp van de volgende technieken:
- De ... gebruikenFunctie Tekst naar kolommen.
- Excel-tekstfuncties gebruiken.
- Flash Fill gebruiken (beschikbaar in 2013 en 2016).
Laten we beginnen!
Splits cellen in Excel met behulp van 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 op te splitsen in de voornaam en de achternaam:
- Selecteer de cellen waarin je de tekst hebt die je wilt splitsen (in dit geval A2:A7).
- Klik op het tabblad Gegevens
- Klik in de groep 'Gegevenstools' 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). Hierdoor 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 Spatie 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 op Algemeen houden omdat ik tekstgegevens moet splitsen. De standaardbestemming is A2 en als u hiermee doorgaat, wordt de originele dataset vervangen. Als u de oorspronkelijke 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). Hierdoor 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. Terwijl dit goed werkt als je dat wiltscheid de voornaam en de achternaam, in het geval van voornaam, middelste naam en achternaam wordt deze in drie delen gesplitst.
- Het resultaat dat u krijgt als u de functie Tekst naar kolom gebruikt, is statisch. Dit betekent dat als er wijzigingen zijn in de oorspronkelijke gegevens, u het proces moet herhalen om bijgewerkte resultaten te krijgen.
Splits cellen in Excel met behulp van TextFunctions
Excel-tekstfunctieszijn geweldig als je tekstreeksen wilt knippen en dobbelen.
Hoewel de functie Tekst naar kolom een statisch resultaat geeft, is het resultaat van het gebruik van functies dynamisch en wordt het automatisch bijgewerkt wanneer u de oorspronkelijke gegevens wijzigt.
Namen splitsen die een voornaam en achternaam hebben
Stel dat u dezelfde gegevens heeft als hieronder weergegeven:
De voornaam extraheren
Gebruik de volgende formule om de voornaam uit deze lijst te halen:
=LINKS(A2,ZOEKEN(" ",A2)-1)Deze formule zou het eerste spatieteken herkennen en vervolgens alle tekst voor dat spatieteken retourneren:
Deze formule gebruikt de functie ZOEK om de positie van het spatieteken te krijgen. In het geval van Bruce Wayne staat het spatieteken op de 6e positie. Vervolgens worden alle tekens links ervan geëxtraheerd met behulp van de functie LINKS.
De achternaam extraheren
Gebruik op dezelfde manier de volgende formule om de achternaam te krijgen:
=RECHTS(A2,LENGTE(A2)-ZOEKEN(" ",A2))
Deze formule gebruikt de zoekfunctie om de positie van de spatiebalk te vinden met behulp van deZOEKOPDRACHTfunctie. Vervolgens wordt dat getal afgetrokken van de totale lengte van de naam (die wordt gegeven door deLENfunctie). Dit geeft het aantal tekens in de achternaam.
Deze achternaam wordt vervolgens geëxtraheerd met behulp van deRECHTSfunctie.
Opmerking:Deze functies werken mogelijk niet goed als u voorloop-, volg- of dubbele spaties in de namen heeft.Klik hiervoor meer informatie over het verwijderen van voorloop-/achterloop-/dubbele spaties in Excel.
Namen splitsen die een voornaam, tweede naam en achternaam hebben
Er kunnen gevallen zijn waarin u een combinatie van namen krijgt waarbij sommige namen ook een middelste 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 gewoon naar het eerste spatieteken en retourneert alle tekens vóór de spatie.
De middelste naam extraheren
Om de middelste naam te krijgen:
=ALS FOUT(MIDDEN(A2,ZOEKEN(" ",A2)+1,ZOEKEN(" ",A2,ZOEKEN(" ",A2)+1)-ZOEKEN(" ",A2)),"")De MID-functie begint bij het eerste spatieteken en haalt de middelste naam eruit door gebruik te maken van het verschil tussen de positie van het eerste en het tweede spatieteken.
Als er geen middelste naam is, retourneert de MID-functie een fout. Om de fout te voorkomen, is het verpakt in deIFOUTfunctie.
De achternaam extraheren
Gebruik de onderstaande formule om de achternaam te krijgen:
=IF(LENGTE(A2)-LENGTE(VERVANGEN(A2," ",""))=1,RECHTS(A2,LENGTE(A2)-ZOEKEN(" ",A2)),RECHTS(A2,LENGTE(A2) -ZOEKEN(" ",A2,ZOEKEN(" ",A2)+1)))Deze formule controleert of er een middelste naam is of niet (doortellen van het aantal spaties). Als er slechts 1 spatie is, wordt alle tekst rechts van de spatie geretourneerd.
Maar als er 2 zijn, wordt het tweede spatieteken gevonden en wordt het aantal tekens na de tweede spatie geretourneerd.
Opmerking: deze formule werkt goed als u namen hebt met alleen de voornaam en achternaam, of de voor-, middelste en achternaam. Als je echter een mix hebt met achtervoegsels of aanhef, dan moet je de formules verder aanpassen.
Splits cellen in Excel met behulp van Flash Fill
Flits vullingis een nieuwe functie die is geïntroduceerd in Excel 2013.
Het kan heel handig zijn als je een patroon hebt en je wilt er snel een deel van extraheren.
Laten we bijvoorbeeld de voornaam en de achternaamgegevens nemen:
Flash fill werkt door patronen te identificeren en deze te repliceren voor alle andere cellen.
Hier ziet u hoe u de voornaam uit de lijst kunt halen met behulp van Flash Fill:
- Voer in cel B2 de voornaam voor 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 vult dezelfde naam in alle cellen.
- Wanneer de cellen zijn gevuld, ziet u rechtsonder het pictogram Opties voor automatisch aanvullen. Klik erop.
- Selecteer Flash Fill in de lijst.
- Zodra je Flash Fill selecteert, zul je merken dat alle cellen zichzelf bijwerken en nu de voornaam voor elke naam tonen.
Hoe Flash Fill werkt?
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 het mislukt ook in sommige gevallen.
Als ik bijvoorbeeld een lijst met namen heb met een combinatie van namen waarvan sommige een middelste naam hebben en sommige niet.
Als ik in zo'n geval de middelste naam extraheer, zal Flash Fill dit ten onrechte doengeef de achternaam terugvoor het geval er geen voornaam is.
Om eerlijk te zijn, dat is 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 waarin Flash Fill briljant werkt.
Ik heb een reeks adressen waaruit ik snel de stad wil extraheren.
Om snel de stad te krijgen, voert u de stadsnaam in voor het eerste adres (voer in dit voorbeeld Londen in cel B2 in) en gebruikt u 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.
Merk op dat het adres hiervoor een homogene dataset moet zijn met hetzelfde scheidingsteken (komma in dit geval).
Als u Flash Fill probeert te gebruiken terwijl er geen patroon is, krijgt u een foutmelding 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-tutorial nuttig vond.
Misschien vind je de volgende Excel-zelfstudies ook leuk:
- Snel cellen combineren in Excel
- Hoe een subtekenreeks in Excel te extraheren met behulp van formules.
- Cellen tellen die tekststrings bevatten
- Gebruikersnamen extraheren uit e-mail-id's in Excel [2 methoden].
- Meerdere regels in een cel splitsen in afzonderlijke cellen/kolommen.