Er kunnen situaties zijn waarin u cellen in Excel moet splitsen. Dit kan zijn wanneer u de gegevens uit een database haalt, of u deze van internet kopieert of van een collega krijgt.

Een eenvoudig voorbeeld waarbij u cellen in Excel moet splitsen, is wanneer u volledige namen en je wilt deze opsplitsen in voornaam en achternaam.

Of je krijgt adres ‘en je wilt het adres splitsen zodat je de steden of de pincode apart kunt analyseren.

Hoe je cellen splitst in Excel

In deze tutorial leer je hoe je cellen in Excel met behulp van de volgende technieken:

• De functie Tekst naar kolom gebruiken.
• Excel-tekstfuncties gebruiken.
• Flash-opvulling gebruiken (beschikbaar in 2013 en 2016).

Laten we beginnen!

Cellen splitsen 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 aparte cellen .:

Hier zijn de stappen om te spli t deze namen 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 ‘Gegevenshulpmiddelen’ op ‘Tekst naar kolommen’.
• In de wizard Tekst naar kolommen converteren:
  • Stap 1 van 3 van de wizard Tekst naar kolommen: zorg ervoor dat Delimited is geselecteerd (dit is de standaardselectie). Hierdoor kunt u de voornaam en de achternaam scheiden op basis van een opgegeven scheidingsteken (in dit geval spatiebalk).
  • 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 Gegevensvoorbeeldgedeelte 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 zo Algemeen houden, aangezien ik tekstgegevens moet splitsen. De standaardbestemming is A2 en als u hiermee doorgaat, wordt de originele dataset vervangen. Als u de originele gegevens intact wilt houden, selecteert u een andere cel als bestemming. In dit geval is B2 geselecteerd.
  • Klik op Voltooien.

Hierdoor wordt de tekst van de cel onmiddellijk in twee verschillende kolommen opgesplitst.

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 voor-, midden- en achternaam in drie delen opgesplitst.
• Het resultaat dat u krijgt door de De functie Tekst naar kolom 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 behulp van tekstfuncties

Excel-tekstfuncties zijn geweldig wanneer u tekstreeksen wilt splitsen 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 wijzig de oorspronkelijke gegevens.

Namen splitsen die een voornaam en achternaam hebben

Stel dat u dezelfde gegevens heeft als hieronder:

De voornaam extraheren

Gebruik de volgende formule om de voornaam uit deze lijst te halen:

=LEFT(A2,SEARCH(" ",A2)-1)

Deze formule zou de eerste spatie vinden en dan alle tekst voor die spatie teruggeven:

Deze formule gebruikt de SEARCH-functie om de positie van de spatie op te halen. In het geval van Bruce Wayne staat de spatie op de 6e positie. Vervolgens worden alle tekens links ervan geëxtraheerd met behulp van de LEFT-functie.

De achternaam extraheren

Gebruik op dezelfde manier de volgende formule om de achternaam te krijgen:

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2))

Deze formule gebruikt de zoekfunctie om de positie van de spatiebalk te vinden met de ZOEK-functie. Het trekt dat getal vervolgens af 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 door de functie RECHTS te gebruiken.

Opmerking: deze functies werken mogelijk niet goed als u voorloop-, achterliggende of dubbele spaties in de namen. Klik hier om te leren hoe u voorloop / volg / dubbele spaties in Excel verwijdert.

Namen splitsen die een voornaam, tweede naam en achternaam hebben

Er kunnen gevallen zijn waarin u verkrijg een combinatie van namen 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:

=LEFT(A2,SEARCH(" ",A2)-1)

Dit is dezelfde formule die we gebruikten toen er geen middelste naam was. Het zoekt gewoon naar de eerste spatie 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)),"")

MID-functie begint vanaf het eerste spatie-teken en extraheert de middelste naam door het verschil tussen de positie van het eerste en het tweede spatie-teken te gebruiken.

In gevallen die er zijn geen middelste naam, de MID-functie retourneert een fout. Om de fout te vermijden, is het verpakt in de IFERROR-functie.

De achternaam extraheren

Gebruik de onderstaande 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 of er een middelste naam is of niet (door het aantal spaties te tellen). Als er slechts 1 spatie is, wordt gewoon alle tekst rechts van de spatie teruggegeven.

Maar als er 2 zijn, wordt de tweede spatie weergegeven en wordt het aantal tekens na de tweede spatie.

Opmerking: deze formule werkt goed als je namen hebt die ofwel de eerste naam en alleen de achternaam hebben, of de eerste, middelste en achternaam. Als u echter een mix heeft met achtervoegsels of aanhef, dan moet u de formules verder aanpassen.

Cellen splitsen in Excel met behulp van Flash Fill

Flash Fill is een nieuwe functie geïntroduceerd in Excel 2013.

Het kan erg handig zijn als je een patroon hebt en je wilt er snel een deel uit halen.

Laten we bijvoorbeeld de voornaam nemen en de achternaamgegevens:

Flash-opvulling werkt door patronen te identificeren en deze te repliceren voor alle andere cellen.

Hier is hoe u de voornaam uit de lijst kunt extraheren met behulp van Flash Fill:

• Voer in cel B2 de voornaam in voor Bruce Wayne (dwz Bruce).
• Als de cel is geselecteerd, ziet u een klein vierkantje aan de rechterkant van de celselectie. Dubbelklik erop. Hierdoor wordt in alle cellen dezelfde naam gevuld.
• Als de cellen zijn gevuld, zie je rechtsonder het pictogram Opties voor automatisch aanvullen. Klik erop.
• Selecteer Flash Fill in de lijst.
• Zoals zodra je Flash Fill selecteert, zul je merken dat alle cellen zichzelf updaten en nu de voornaam voor elke naam tonen.

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 zoals verwacht in de meeste gevallen. Maar het mislukt in sommige gevallen ook.

Bijvoorbeeld, als ik een lijst met namen heb die een combinatie van namen heeft, 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 voor 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 hulpmiddel dat goed genoeg is om in je arsenaal te houden en te gebruiken wanneer dat nodig is.

Hier is nog een voorbeeld waarin Flash Fill werkt briljant.

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 Londen in in cel B2 in dit voorbeeld) en gebruik de functie voor automatisch aanvullen om alle cellen te vullen. Gebruik nu Flash Fill en u krijgt direct de naam van de stad vanaf elk adres.

Op dezelfde manier kunt u het adres splitsen en elk deel van het adres extraheren.

Merk op dat dit zou het adres nodig hebben om een homogene dataset te zijn met hetzelfde scheidingsteken (in dit geval komma).

In het geval dat u Flash Fill probeert te gebruiken terwijl er geen patroon is, zal het u een fout laten zien zoals weergegeven hieronder:

In deze tutorial 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-tutorials ook leuk:

• Hoe om cellen snel te combineren in Excel.
• Hoe u een substring in Excel kunt extraheren met behulp van formules.
• Hoe u cellen kunt tellen die tekstreeksen bevatten.
• Gebruikersnamen extraheren uit E-mail-ID’s in Excel.
• Meerdere regels in een cel splitsen in afzonderlijke cellen / kolommen.