Voornaam, tussenvoegsel en achternaam uit volledige naam halen in Excel
Stel dat je een lijst met gebruikersnamen hebt, en nu wil je de volledige namen splitsen in voornaam, tussenvoegsel en achternaam in afzonderlijke kolommen, zoals in de onderstaande schermafbeelding te zien is. In dit artikel introduceer ik enkele formules om deze taak in Excel uit te voeren.
- Voornaam uit volledige naam halen in Excel
- Achternaam uit volledige naam halen in Excel
- Tussenvoegsel uit volledige naam halen in Excel
- Voornaam, tussenvoegsel en achternaam uit volledige naam halen met een geweldige functie
Voornaam uit volledige naam halen in Excel
Om de voornamen uit volledige namen te halen, kunnen de functies LINKS en ZOEKEN je helpen. De algemene syntaxis is:
- naam: De volledige naam of celverwijzing waaruit je de voornaam wilt halen.
Kopieer of voer de volgende formule in een lege cel in waar je het resultaat wilt krijgen:
Sleep vervolgens de vulhendel naar beneden naar de cellen waarop je deze formule wilt toepassen, en alle voornamen zijn geëxtraheerd zoals in de onderstaande schermafbeelding te zien is:
Uitleg van de formule:
ZOEKEN(" ",A2)-1: Deze ZOEKEN-functie wordt gebruikt om de positie van het eerste spatiekarakter te vinden. Aftrekken van 1 betekent dat het aantal tekens dat je wilt één minder is dan de positie van de spatie. Dit levert het resultaat: 5.
LINKS(A2,ZOEKEN(" ",A2)-1)= LINKS("Jerry T. Stone",5): Deze LINKS-functie zal de tekst in de volledige naam “Jerry T. Stone” vanaf links extraheren met 5 tekens die worden geretourneerd door de ZOEKEN-functie.
Achternaam uit volledige naam halen in Excel
Om de achternamen uit volledige namen te halen, kan de combinatie van de functies RECHTS, LENGTE, VIND.ALLES, LENGTE en VERVANGEN je helpen. De algemene syntaxis is:
- naam: De volledige naam of celverwijzing waaruit je de achternaam wilt halen.
Kopieer of voer de onderstaande formule in een lege cel in waar je de achternamen wilt krijgen:
Sleep vervolgens de vulhendel naar beneden naar de cellen om deze formule toe te passen, en alle achternamen zijn geëxtraheerd uit de volledige namen, zie onderstaande schermafbeelding:
Uitleg van de formule:
1. LENGTE(A2)-LENGTE(VERVANGEN(A2," ","")): Dit deel wordt gebruikt om het aantal spaties in cel A2 te krijgen.
- LENGTE(A2): Deze LENGTE-functie retourneert het totale aantal tekens in cel A2. Het zal teruggeven: 14.
- VERVANGEN(A2," ",""): Deze VERVANGEN-functie wordt gebruikt om alle spaties te vervangen door niets. En je krijgt dit resultaat: “JerryT.Stone”.
- LENGTE(VERVANGEN(A2," ",""): Krijg de totale lengte van de volledige naam in cel A2 zonder de spaties.
- LENGTE(A2)-LENGTE(VERVANGEN(A2,"-","")): Trek de lengte van de volledige naam zonder spaties af van de totale lengte van de volledige naam om het aantal spaties te krijgen, en dit levert 2 op.
2. VERVANGEN(A2," ","*",LENGTE(A2)-LENGTE(VERVANGEN(A2," ","")))= VERVANGEN(A2," ","*",2): Deze VERVANGEN-functie wordt gebruikt om de laatste spatie die wordt geretourneerd door de eerste deelformule te vervangen door een *-teken. En je krijgt dit resultaat: “Jerry T.*Stone”.
3. VIND.ALLES("*",VERVANGEN(A2," ","*",LENGTE(A2)-LENGTE(VERVANGEN(A2," ",""))))= VIND.ALLES("*","Jerry T.*Stone" ): De VIND.ALLES-functie zal de positie van het *-teken in de tekststring retourneren die wordt geretourneerd door de VERVANGEN-functie, en je krijgt het getal 9.
4. LENGTE(A2)-VIND.ALLES("*",VERVANGEN(A2," ","*",LENGTE(A2)-LENGTE(VERVANGEN(A2," ",""))))= LENGTE(A2)-9: Dit deel van de formule zal berekenen hoeveel tekens er na de laatste spatie staan. Dit levert het getal 5 op. Dit wordt herkend als het num_chars-argument in de RECHTS-functie.
5. RECHTS(A2,LENGTE(A2)-VIND.ALLES("*",VERVANGEN(A2," ","*",LENGTE(A2)-LENGTE(VERVANGEN(A2," ","")))))=RECHTS(A2, 5): Tot slot wordt de RECHTS-functie gebruikt om 5 tekens te extraheren die worden geretourneerd door de formule in stap 4 vanaf de rechterkant van de tekststring in cel A2.
Opmerking: Als je lijst met volledige namen alleen voornaam en achternaam bevat, kun je de volgende eenvoudige formule gebruiken om de achternaam te krijgen zoals je nodig hebt:
Tussenvoegsel uit volledige naam halen in Excel
Als je de tussenvoegsels uit de volledige namen moet halen, is deze formule gemaakt met behulp van de functies MID en ZOEKEN. De algemene syntaxis is:
- naam: De volledige naam of celverwijzing waaruit je het tussenvoegsel wilt halen.
Voer of kopieer de volgende formule in een lege cel in om het resultaat te tonen:
Sleep vervolgens de vulhendel naar beneden naar de cellen om deze formule toe te passen, en alle tussenvoegsels zijn ook geëxtraheerd, zie onderstaande schermafbeelding:
Uitleg van de formule:
Om het tussenvoegsel te extraheren, moet je eerst de positie van beide spaties tussen de volledige naam vinden, en vervolgens de MID-functie gebruiken om de tekst in het midden van de tekststring te extraheren.
1. ZOEKEN(" ", A2) + 1: Deze ZOEKEN-functie zal de positie van de eerste spatie krijgen, optellen van 1 betekent om de extractie te starten vanaf het volgende teken. Dit levert het getal 7 op. Dit deel wordt herkend als het start_num-argument binnen de MID-functie.
2. ZOEKEN(" ", A2, ZOEKEN(" ", A2)+1) - ZOEKEN(" ", A2)-1: Dit deel wordt herkend als het num_chars-argument binnen de MID-functie.
- ZOEKEN(" ", A2, ZOEKEN(" ", A2)+1): Dit deel van de formule wordt gebruikt om de positie van de tweede spatie te krijgen, het levert het getal 9 op.
- ZOEKEN(" ", A2): Deze ZOEKEN-functie zal de positie van de eerste spatie in cel A2 krijgen. Het levert het getal 6 op.
- ZOEKEN(" ", A2, ZOEKEN(" ", A2)+1) - ZOEKEN(" ", A2)-1=9-6-1: Trek de positie van de eerste spatie af van de positie van de tweede spatie, en trek vervolgens 1 af van het resultaat om een voorloopspatie te verwijderen. En het resultaat is 2.
3. MID(A2, ZOEKEN(" ", A2) + 1, ZOEKEN(" ", A2, ZOEKEN(" ", A2)+1) - ZOEKEN(" ", A2)-1)=MID(A2,7,2): Tot slot zal de MID-functie 2 tekens in het midden van cel A2 extraheren die starten bij het zevende teken.
Opmerking: Als er alleen voornaam en achternaam in de volledige namen staan, zal de bovenstaande formule een foutwaarde opleveren zoals in de onderstaande schermafbeelding te zien is:
Om dit probleem op te lossen, moet je de bovenstaande formule in de ALS.FOUT-functie plaatsen, pas de volgende formule toe:
Voornaam, tussenvoegsel en achternaam uit volledige naam halen met een geweldige functie
Als je moeite hebt met de bovenstaande formules, raad ik hier een gemakkelijk hulpmiddel aan - Kutools voor Excel, met de functie Naam splitsen, kun je alle voornamen, tussenvoegsels en achternamen tegelijkertijd in afzonderlijke cellen extraheren. Klik om Kutools voor Excel gratis te downloaden voor een proefversie!
Gerelateerde functies gebruikt:
- LINKS:
- De LINKS-functie haalt een bepaald aantal tekens vanaf de linkerkant van een gegeven tekstreeks.
- RECHTS:
- De RECHTS-functie wordt gebruikt om een specifiek aantal tekens vanaf de rechterkant van de tekststring te extraheren.
- MID:
- De MID-functie wordt gebruikt om een specifiek aantal tekens te vinden en terug te geven uit het midden van een gegeven tekststring.
- LENGTE:
- De LENGTE-functie retourneert het aantal tekens in een tekststring.
- VIND.ALLES:
- De VIND.ALLES-functie wordt gebruikt om een string binnen een andere string te vinden en retourneert de startpositie van de string binnen een andere.
- ZOEKEN:
- De ZOEKEN-functie helpt je de positie van een specifiek teken of deelstring te vinden vanuit de gegeven tekststring.
- VERVANGEN:
- De Excel VERVANGEN-functie vervangt tekst of karakters binnen een tekststring door andere tekst of karakters.
Meer artikelen:
- Eerste initiaal en achternaam uit volledige naam halen
- Stel dat je een lijst met volledige namen hebt, en nu wil je de eerste initiaal en achternaam of voornaam en eerste letter van de achternaam uit de volledige naam halen zoals in de onderstaande schermafbeeldingen te zien is. Hoe zou je deze taken in een Excel-werkmap kunnen aanpakken?
- Volgorde van voornaam en achternaam omkeren in Excel-lijst
- Als je een lijst met volledige namen hebt die zijn opgemaakt als achternaam en voornaam, en je wilt de achternaam en voornaam omkeren naar voornaam en achternaam zoals in de onderstaande schermafbeelding te zien is. In dit artikel introduceer ik enkele formules om deze taak in Excel uit te voeren.
- Meerdere regels uit een cel halen
- Als je een lijst met tekststrings hebt die gescheiden zijn door regeleinden (die ontstaan door op Alt + Enter te drukken tijdens het invoeren van tekst), en je wilt deze regels tekst extraheren naar meerdere cellen zoals in de onderstaande schermafbeelding te zien is. Hoe zou je dit met een formule in Excel kunnen oplossen?
- N-de woord uit tekststring halen in Excel
- Als je een lijst met tekststrings of zinnen hebt, en je wilt het specifieke n-de woord uit de lijst halen zoals in de onderstaande schermafbeelding te zien is. In dit artikel introduceer ik enkele methoden om deze taak in Excel uit te voeren.
De Beste Office-productiviteitstools
Kutools voor Excel - Helpt U Om Uit Te Blinken In De Menigte
Kutools voor Excel Beschikt Over Meer Dan 300 Functies, Waardoor Wat U Nodig Hebt Maar Een Klik Verwijderd Is...
Office Tab - Schakel Tabbladgestuurd Lezen en Bewerken in Microsoft Office (inclusief Excel)
- Eén seconde om te schakelen tussen tientallen open documenten!
- Verminder honderden muisklikken voor u elke dag, zeg vaarwel tegen muisarm.
- Verhoogt uw productiviteit met 50% bij het bekijken en bewerken van meerdere documenten.
- Brengt Efficiënte Tabbladen naar Office (inclusief Excel), Net Als Chrome, Edge en Firefox.