Scheid voor- en achternaam in Excel (namen splitsen met formules)

Bekijk video - Splits namen in Excel (in voornaam, middelste en achternaam)

Excel is een geweldig hulpmiddel als het gaat om het snijden en in blokjes snijden van tekstgegevens.

Er zijn zoveel handige formules en functionaliteiten die u kunt gebruiken om met tekstgegevens in Excel te werken.

Een van de veel voorkomende vragen die ik krijg over het manipuleren van tekstgegevens is - "Hoe voor- en achternaam (of voor-, middelste en achternaam) in Excel te scheiden?“.

Er zijn een aantal eenvoudige manieren om namen in Excel te splitsen. De methode die u kiest, hangt af van hoe uw gegevens zijn gestructureerd en of u wilt dat het resultaat statisch of dynamisch is.

Laten we dus aan de slag gaan en verschillende manieren bekijken om namen in Excel te splitsen.

Namen splitsen met tekst naar kolommen

Met de functie Tekst naar kolommen in Excel kunt u snel tekstwaarden splitsen in afzonderlijke cellen op een rij.

Stel dat u de gegevensset hebt zoals hieronder weergegeven en dat u de voor- en achternaam wilt scheiden en deze in afzonderlijke cellen wilt krijgen.

Hieronder staan ​​de stappen om de voor- en achternaam te scheiden met Tekst naar kolommen:

1. Selecteer alle namen in de kolom (A2:A10 in dit voorbeeld)
2. Klik op het tabblad 'Gegevens'
3. Klik in de groep 'Gegevenshulpmiddelen' op de optie 'Tekst naar kolommen'.
4. Breng de volgende wijzigingen aan in de wizard Tekst naar kolom converteren:
   1. Stap 1 van 3: Selecteer Gescheiden (hiermee kunt u spatie gebruiken als scheidingsteken) en klik op Volgende
   2. Stap 2 van 3: Selecteer de optie Ruimte en klik op Volgende
   3. Stap 3 van 3: Stel B2 in als de bestemmingscel (anders worden de bestaande gegevens overschreven)
5. Klik op Voltooien

De bovenstaande stappen zouden de namen onmiddellijk splitsen in voor- en achternaam (met voornamen in kolom B en achternaam in kolom C).

Opmerking: in het geval dat er al gegevens in de cellen staan ​​(degenen waar de uitvoer van tekst naar kolommen wordt verwacht), geeft Excel een waarschuwing weer om u te laten weten dat er al gegevens in de cellen staan. U kunt ervoor kiezen om de gegevens te overschrijven of Tekst naar kolommen te annuleren en deze eerst handmatig te verwijderen.

Als u klaar bent, kunt u desgewenst de volledige naamgegevens verwijderen.

Een paar dingen die u moet weten wanneer u Tekst naar kolommen gebruikt om de voor- en achternaam in Excel te scheiden:

1. Het resultaat hiervan is statisch. Dit betekent dat als u nieuwe gegevens heeft of als de oorspronkelijke gegevens enkele wijzigingen hebben, u dit helemaal opnieuw moet doen om de namen te splitsen.
2. Als u alleen de Voornaam of alleen de Achternaam wilt, kunt u de kolommen die u niet wilt overslaan in stap 3 van het dialoogvenster 'Wizard Tekst naar Kolom'. Selecteer hiervoor de kolom in het voorbeeld die u wilt overslaan en selecteer vervolgens de optie 'Kolom niet importeren (overslaan)'.
3. Als u de doelcel niet opgeeft, zal Tekst naar kolom de huidige kolom overschrijven

De optie Tekst naar kolommen is het meest geschikt als u consistente gegevens hebt (bijvoorbeeld alle namen hebben alleen voor- en achternaam of alle namen hebben een voor-, tussen- en achternaam).

In dit voorbeeld heb ik u laten zien hoe u namen met spatie als scheidingsteken kunt scheiden. Als het scheidingsteken een komma of een combinatie van komma en spatie is, kunt u nog steeds dezelfde stappen gebruiken. In dit geval kunt u het scheidingsteken opgeven in stap 2 van 3 van de wizard. Er is al een optie om de komma als scheidingsteken te gebruiken, of u kunt de optie 'Overig' selecteren en ook een aangepast scheidingsteken opgeven.

Hoewel Tekst naar kolommen een snelle en efficiënte manier is om namen te splitsen, is het alleen geschikt als u wilt dat de uitvoer een statisch resultaat is. Als u een dataset heeft die kan uitbreiden of wijzigen, kunt u beter formules gebruiken om de namen te scheiden.

Scheid voor-, midden- en achternaam met formules

Met formules kunt u de tekstgegevens in stukken snijden en in blokjes snijden en extraheren wat u wilt.

In dit gedeelte zal ik verschillende formules delen die u kunt gebruiken om naamgegevens te scheiden (op basis van hoe uw gegevens zijn gestructureerd).

Hieronder staan ​​de drie formules die u kunt gebruiken om de voor-, middelste en achternaam van elkaar te scheiden (wordt later in de volgende secties in detail uitgelegd).

Formule om de voornaam te krijgen:

=LINKS(A2,ZOEKEN(" ",A2)-1)

Formule om de middelste naam te krijgen:

=MID(A2,SEARCH(" ",A2)+1,SEARCH(" ",VERVANG(A2," ","@",1))-SEARCH(" ",A2))

Formule om de achternaam te krijgen:

=RIGHT(A15,LEN(A15)-ZOEKEN("@",VERVANG(A15," ","@",LEN(A15)-LEN(VERVANG(A15," ","")))))

Krijg de voornaam

Stel dat u de dataset heeft zoals hieronder getoond en u wilt snel de voornaam in één cel en achternaam in één cel scheiden.

De onderstaande formule geeft je de voornaam:

=LINKS(A2,ZOEKEN(" ",A2)-1)

De bovenstaande formule gebruikt de functie ZOEKEN om de positie van het spatieteken tussen de voor- en achternaam te krijgen. De LEFT-functie gebruikt vervolgens dit spatiepositienummer om alle tekst ervoor te extraheren.

Dit is een vrij eenvoudig gebruik van het extraheren van een deel van de tekstwaarde. Omdat we alleen de positie van het eerste spatieteken hoeven te identificeren, maakt het niet uit of de naam een ​​tweede naam heeft of niet. De bovenstaande formule gaat prima werken.

Laten we nu een beetje geavanceerder worden met elk voorbeeld.

Krijg de achternaam

Laten we zeggen dat je dezelfde dataset hebt en deze keer moet je de achternaam krijgen.

De onderstaande formule haalt de achternaam uit de bovenstaande dataset:

=RECHTS(A2,LEN(A2)-ZOEKEN(" ",A2))

Nogmaals, vrij rechttoe rechtaan.

Deze keer vinden we eerst de positie van het spatieteken, die vervolgens wordt gebruikt om het aantal tekens te achterhalen dat overblijft na de spatie (wat de achternaam zou zijn).

Dit wordt bereikt door de positiewaarde van het spatieteken af ​​te trekken van het totale aantal tekens in de naam.

Dit nummer wordt vervolgens gebruikt in de RECHTS-functie om al deze tekens rechts van de naam op te halen.

Hoewel deze formule geweldig werkt als er alleen de voor- en achternaam zijn, zou het niet werken als je ook een tweede naam hebt. Dit komt omdat we slechts één spatie hebben gebruikt (tussen voor- en achternaam). Het hebben van een middelste naam voegt meer spatietekens toe aan de naam.

Gebruik de onderstaande formule om de achternaam op te halen als u ook een tweede naam heeft:

=RIGHT(A15,LEN(A15)-ZOEKEN("@",VERVANG(A15," ","@",LEN(A15)-LEN(VERVANG(A15," ","")))))

Nu begint dit een beetje ingewikkeld te worden … is het niet?

Laat me uitleggen hoe dit werkt.

De bovenstaande formule vindt eerst het totale aantal spatietekens in de naam. Dit wordt gedaan door de lengte van de naam met en zonder het spatieteken te krijgen en vervolgens die zonder spatie af te trekken van die met spatie. Dit geeft het totale aantal spatietekens.

De SUBSTITUTE-functie wordt vervolgens gebruikt om het laatste spatieteken te vervangen door een '@'-symbool (u kunt elk symbool gebruiken - iets dat waarschijnlijk niet voorkomt als onderdeel van de naam).

Als het @-symbool eenmaal is vervangen in plaats van het laatste spatieteken, kun je gemakkelijk de positie van dit @-symbool vinden. Dit wordt gedaan met behulp van de ZOEK-functie.

Nu hoef je alleen nog maar alle tekens rechts van dit @-symbool te extraheren. Dit wordt gedaan met behulp van de RECHTS-functie.

Verkrijg de middelste naam

Stel dat je de dataset hebt zoals hieronder weergegeven en dat je de tweede naam wilt extraheren.

De volgende formule doet dit:

=MID(A2,SEARCH(" ",A2)+1,SEARCH(" ",VERVANG(A2," ","@",1))-SEARCH(" ",A2))

De bovenstaande formule gebruikt de MID-functie, waarmee u een startpositie kunt specificeren en het aantal tekens dat uit die positie moet worden gehaald.

De startpositie is eenvoudig te vinden met de ZOEK-functie.

Het moeilijkste is om uit te vinden hoeveel karakters je moet extraheren na deze startpositie. Om dit te krijgen, moet u bepalen hoeveel tekens er zijn vanaf de startpositie tot het laatste spatieteken.

Dit kan door de SUBSTITUTE-functie te gebruiken en het laatste spatieteken te vervangen door een ‘@’-symbool. Zodra dit is gebeurd, kunt u eenvoudig de functie ZOEKEN gebruiken om de positie van dit laatste spatieteken te vinden.

Nu je de startpositie en de positie van de laatste spatie hebt, kun je eenvoudig de middelste naam ophalen met behulp van de MID-functie.

Een van de voordelen van het gebruik van een formule om de namen te scheiden, is dat het resultaat dynamisch is. Dus in het geval dat uw dataset uitbreidt en er meer namen aan worden toegevoegd of als sommige namen veranderen in de originele dataset, hoeft u zich geen zorgen te maken over de resulterende gegevens.

Namen scheiden met Zoeken en vervangen

Ik ben dol op de flexibiliteit die hoort bij 'Zoeken en vervangen' - omdat je er jokertekens in kunt gebruiken.

Laat me eerst uitleggen wat een jokerteken is.

Een jokerteken is iets dat u in plaats van tekst kunt gebruiken. U kunt bijvoorbeeld een asterisk-symbool (*) gebruiken en dit vertegenwoordigt een willekeurig aantal tekens in Excel. Om u een voorbeeld te geven, als ik alle namen wil vinden die beginnen met het alfabet A, kan ik A* gebruiken in zoeken en vervangen. Hiermee worden alle cellen gevonden en geselecteerd waar de naam begint met een A.

Maak je geen zorgen als je nog steeds niet duidelijk bent. Blijf lezen en de volgende paar voorbeelden zullen duidelijk maken wat jokertekens zijn en hoe u deze kunt gebruiken om snel namen te scheiden (of eventuele tekstwaarden in Excel).

Zorg ervoor dat u in alle onderstaande voorbeelden een reservekopie van de dataset maakt. Zoeken en vervangen wijzigt de gegevens waarop het wordt gebruikt. U kunt het beste eerst de gegevens kopiëren en plakken en vervolgens Zoeken en vervangen op de gekopieerde gegevensset gebruiken.

Krijg de voornaam

Stel dat u een dataset heeft zoals hieronder weergegeven en u wilt alleen de voornaam.

Hieronder staan ​​de stappen om dit te doen:

1. Kopieer de naamgegevens in kolom A en plak deze in kolom B.
2. Klik met de gegevens in kolom B geselecteerd op het tabblad Start
3. Klik in de groep Bewerken op Zoeken & selecteren.
4. Klik op Vervangen. Hierdoor wordt het dialoogvenster 'Zoeken en vervangen' geopend.
5. Voer in het dialoogvenster 'Zoeken en vervangen' het volgende in:
   1. Zoek wat: * (spatie gevolgd door het sterretje)
   2. Vervang door: laat dit leeg
6. Klik op Alles vervangen.

De bovenstaande stappen geven u de voornaam en verwijderen alles na de voornaam.

Dit werkt zelfs als je namen hebt met een tweede naam.

Pro-tip: De sneltoets om het dialoogvenster Zoeken en vervangen te openen is Controle + H (houd de control-toets ingedrukt en druk vervolgens op de H-toets).

Krijg de achternaam

Stel dat u een dataset heeft zoals hieronder weergegeven en u wilt alleen de achternaam.

Hieronder staan ​​de stappen om dit te doen:

1. Kopieer de naamgegevens in kolom A en plak deze in kolom B.
2. Klik met de gegevens in kolom B geselecteerd op het tabblad Start
3. Klik in de groep Bewerken op Zoeken & selecteren.
4. Klik op Vervangen. Hierdoor wordt het dialoogvenster 'Zoeken en vervangen' geopend.
5. Voer in het dialoogvenster 'Zoeken en vervangen' het volgende in:
   1. Zoek wat: * (sterretje gevolgd door een spatie)
   2. Vervang door: laat dit leeg
6. Klik op Alles vervangen.

De bovenstaande stappen geven u de achternaam en verwijderen alles vóór de voornaam.

Dit werkt zelfs als je namen hebt met een tweede naam.

Verwijder de middelste naam

Als u alleen de middelste naam wilt verwijderen en alleen de voor- en achternaam wilt hebben, kunt u dat doen met behulp van Zoeken en vervangen.

Stel je hebt een dataset zoals hieronder weergegeven en je wilt de tweede naam hiervan verwijderen.

Hieronder staan ​​de stappen om dit te doen:

1. Kopieer de naamgegevens in kolom A en plak deze in kolom B.
2. Klik met de gegevens in kolom B geselecteerd op het tabblad Start
3. Klik in de groep Bewerken op Zoeken & selecteren.
4. Klik op Vervangen. Hierdoor wordt het dialoogvenster 'Zoeken en vervangen' geopend.
5. Voer in het dialoogvenster 'Zoeken en vervangen' het volgende in:
   1. Zoek wat: * (spatie gevolgd door het sterretje gevolgd door de spatie)
   2. Vervang door: (plaats hier gewoon een spatie)
6. Klik op Alles vervangen.

De bovenstaande stappen zouden de middelste naam uit een volledige naam verwijderen. Als sommige namen geen tweede naam hebben, worden ze niet gewijzigd.

Namen scheiden met Flash Fill

Flash Fill is geïntroduceerd in Excel 2013 en maakt het heel eenvoudig om een ​​tekstgegevensset aan te passen of op te schonen.

En als het gaat om het scheiden van naamgegevens, zit het precies in het steegje van Flash Fill.

Het belangrijkste om te weten bij het gebruik van Flash Fill is dat er een patroon nodig is dat Flash Fill kan identificeren. Zodra het het patroon heeft geïdentificeerd, zal het u gemakkelijk helpen namen in Excel te splitsen (u krijgt hier meer duidelijkheid over wanneer u een paar voorbeelden hieronder doorneemt).

Haal de voor- of achternaam uit de volledige naam

Stel dat u een dataset heeft zoals hieronder weergegeven en u wilt alleen de voornaam.

1. Typ in de aangrenzende cel handmatig de voornaam van de volledige naam. In dit voorbeeld zou ik Rick typen.
2. Typ in de tweede cel handmatig de voornaam van de naam van de aangrenzende cel. Terwijl u typt, ziet u dat Flash Fill automatisch een lijst met de voornaam toont (in grijs).
3. Als je de namen in het grijs ziet, kijk er dan snel doorheen om er zeker van te zijn dat de juiste namen worden weergegeven. Als deze juist zijn, drukt u op de enter-toets en Flash Fill vult automatisch de rest van de cellen met de voornaam.

Flash Fill heeft je nodig om het een patroon te geven dat het kan volgen wanneer het je de gewijzigde gegevens geeft. Wanneer u in ons voorbeeld de voornaam in de eerste cel typt, kan Flash Fill het patroon niet achterhalen.

Maar zodra u de Voornaam in de tweede cel begint in te voeren, begrijpt Flash Fill het patroon en toont u een suggestie. Als de suggestie correct is, drukt u gewoon op de enter-toets.

En als het niet correct is, kunt u proberen nog een paar cellen handmatig in te voeren en te controleren of Flash Fill het patroon kan onderscheiden of niet.

Soms ziet u het patroon mogelijk niet in grijs (zoals weergegeven in stap 2 hierboven). Als dat het geval is, volgt u de onderstaande stappen om het Flash Fill-resultaat te krijgen:

1. Voer de tekst handmatig in twee cellen in.
2. Selecteer deze beide cellen
3. Beweeg de cursor over de rechterbenedenhoek van de selectie. U zult merken dat de cursor verandert in een pluspictogram
4. Dubbelklik erop (muis linkermuisknop). Hiermee worden alle cellen gevuld. Op dit moment zijn de resultaten waarschijnlijk onjuist en niet wat u had verwacht.
5. Rechtsonder in de resulterende gegevens ziet u een klein pictogram voor automatisch aanvullen. Klik op dit pictogram voor automatisch aanvullen
6. Klik op Flash Fill

De bovenstaande stappen geven u het resultaat van Flash Fill (op basis van het patroon dat het heeft afgeleid).

U kunt Flash Fill ook gebruiken om de achternaam of de middelste naam te krijgen. Voer in de eerste twee cellen de achternaam (of de middelste naam) in en Flash Fill zal het patroon kunnen begrijpen

Naam herschikken met Flash Fill

Flash Fill is een slimme tool en kan ook enigszins complexe patronen ontcijferen

Stel dat u een gegevensset heeft zoals hieronder weergegeven en u wilt de naam herschikken van Rick Novak naar Novak, Rick (waarbij de achternaam eerst komt, gevolgd door een komma en vervolgens de voornaam).

Hieronder staan ​​de stappen om dit te doen:

1. Typ handmatig in de aangrenzende cel Nova, Rick
2. Typ in de tweede cel handmatig Connor, Susan. Terwijl u typt, ziet u Flash Fill een lijst met namen in hetzelfde formaat (in grijs).
3. Als je de namen in het grijs ziet, kijk er dan snel doorheen om er zeker van te zijn dat de juiste namen worden weergegeven. Als deze juist zijn, drukt u op de enter-toets en Flash Fill zal automatisch de rest van de cellen vullen met de namen in hetzelfde formaat.

Verwijder de middelste naam (of haal gewoon de middelste naam)

Je kunt Flash Fill ook gebruiken om de middelste naam te verwijderen of alleen de middelste naam te krijgen.

Stel dat u een gegevensset heeft zoals hieronder weergegeven en dat u alleen de voor- en achternaam wilt krijgen en niet de middelste naam.

Hieronder staan ​​de stappen om dit te doen:

1. Typ handmatig in de aangrenzende cel Rick Nova
2. Typ in de tweede cel handmatig Susan Connor. Terwijl u typt, ziet u Flash Fill een lijst met namen in hetzelfde formaat (in grijs).
3. Als je de namen in het grijs ziet, kijk er dan snel doorheen om er zeker van te zijn dat de juiste namen worden weergegeven. Als deze juist zijn, drukt u op de enter-toets en Flash Fill vult automatisch de rest van de cellen met de namen zonder de middelste naam.

Evenzo, als u alleen de middelste namen wilt krijgen, typt u de middelste naam in de eerste twee cellen en gebruikt u Flash Fill om de middelste naam van alle resterende namen te krijgen.

De voorbeelden die in deze zelfstudie worden getoond, gebruiken namen bij het manipuleren van de tekstgegevens. U kunt dezelfde concepten gebruiken om ook met andere gegevensformaten te werken (zoals adressen, productnamen, enz.)