Ga naar hoofdinhoud

Ontdek de nieuwe, gratis versie van Kutools voor Outlook vandaag!

 

Extraheer de eerste midden- en achternaam uit de volledige naam in Excel

Auteur: Xiaoyang Laatst gewijzigd: 2020-11-06

Stel dat u een lijst met gebruikersnamen heeft, nu wilt u de volledige namen splitsen in voor-, midden- en achternaam in aparte kolommen, zoals in het volgende screenshot. In dit artikel zal ik enkele formules introduceren voor het omgaan met deze taak in Excel.

Extraheer of verkrijg voornamen uit volledige namen in Excel

Om de voornamen uit volledige namen te extraheren of te krijgen, kunnen de LEFT- en SEARCH-functie u helpen, de generieke syntaxis is:

=LEFT(name,FIND(" ",name)-1)
  • name: De volledige naam of celverwijzing waaruit u als eerste wilt extraheren.

Kopieer of typ de volgende formule in een lege cel waar u het resultaat wilt krijgen:

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

Sleep vervolgens de vulgreep naar de cellen waarop u deze formule wilt toepassen en alle voornamen zijn geëxtraheerd zoals onderstaand screenshot laat zien:

Verklaring van de formule:

SEARCH ("", A2) -1: Deze SEARCH-functie wordt gebruikt om de positie van de eerste spatie op te halen. 1 aftrekken betekent dat het aantal tekens dat u wilt één minder is dan de positie van de spatie. Dit krijgt het resultaat: 5.

LEFT (A2, SEARCH ("", A2) -1) = LEFT ("Jerry T. Stone", 5): Deze LEFT-functie haalt de tekst in de volledige naam "Jerry T. Stone" vanaf de linkerkant met 5 tekens die worden geretourneerd door de SEARCH-functie.

Extraheer of verkrijg achternamen uit volledige namen in Excel

Om de achternaam uit volledige namen te halen, kan de combinatie van de RECHTER-, LEN-, FIND-, LEN- en SUBSTITUTE-functies je een plezier doen. De generieke syntaxis is:

=RIGHT(name,LEN(name)-FIND("*",SUBSTITUTE(name," ","*",LEN(name)-LEN(SUBSTITUTE(name," ","")))))
  • name: De volledige naam of celverwijzing waaruit u de achternaam wilt extraheren.

Kopieer of typ de onderstaande formule in een lege cel waar u de achternaam wilt krijgen:

=RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

En sleep vervolgens de vulgreep naar de cellen om deze formule te vullen, en alle achternamen zijn uit de volledige namen gehaald, zie screenshot:

Verklaring van de formule:

1. LEN (A2) -LEN (SUBSTITUTE (A2; "", "")): Dit deel wordt gebruikt om het aantal spaties in cel A2 te krijgen.

  • LEN (A2): Deze functie LEN retourneert het totale aantal tekens in cel A2. Het zal terugkeren: 14.
  • VERVANGEN (A2; "", ""): Deze SUBSTITUTE-functie wordt gebruikt om alle spaties door niets te vervangen. En je krijgt het resultaat als volgt: "JerryT.Stone'.
  • LEN (SUBSTITUUT (A2; "", ""): Verkrijg de totale lengte van de volledige naam in cel A2 zonder de spaties.
  • LEN (A2) -LEN (SUBSTITUTE (A2; "-", "")): Trek de lengte van de volledige naam zonder spatie af van de totale lengte van de volledige naam om het nummer van de spatie te krijgen, en dit krijgt 2.

2. SUBSTITUTE (A2, "", "*", LEN (A2) -LEN (SUBSTITUTE (A2, "", ""))) = SUBSTITUTE (A2, "", "*", 2): Deze SUBSTITUTE-functie wordt gebruikt om de laatste instantie van de spatie die is geretourneerd door de eerste deelformule te vervangen door een * -teken. En je krijgt dit resultaat: "Jerry T. * Stone'.

3. FIND ("*", SUBSTITUTE (A2, "", "*", LEN (A2) -LEN (SUBSTITUTE (A2, "", "")))) = FIND ("*", "Jerry T. *Steen" ): De FIND-functie retourneert de positie van het * -teken in de tekstreeks die wordt geretourneerd door de SUBSTITUTE-functie, en je krijgt het nummer 9.

4. LEN (A2) -FIND ("*", SUBSTITUTE (A2, "", "*", LEN (A2) -LEN (SUBSTITUTE (A2, "", "")))) = LEN (A2) - 9: Dit deel van de formule geeft aan hoeveel tekens er na de laatste spatie staan. Dit krijgt het nummer 5. Dit wordt herkend als het argument num_chars in de functie RECHTS.

5. RIGHT (A2, LEN (A2) -FIND ("*", SUBSTITUTE (A2, "", "*", LEN (A2) -LEN (SUBSTITUTE (A2, "", ""))))) = RECHTS (A2; 5): Eindelijk wordt de functie RECHTS gebruikt om 5 tekens te extraheren die door de formule in stap 4 worden geretourneerd vanaf de rechterkant van de tekstreeks in cel A2.

Note: Als uw lijst met volledige namen alleen de voor- en achternaam bevat, kunt u de volgende eenvoudige formule toepassen om de achternaam te krijgen zoals u nodig hebt:

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

Extraheer of haal middelste namen uit volledige namen in Excel

Als u de middelste namen uit de volledige namen moet extraheren, is deze formule gemaakt door de MID- en SEARCH-functies. De generieke syntaxis is:

=MID(name, SEARCH(" ", name) + 1, SEARCH(" ", name, SEARCH(" ", name)+1) - SEARCH(" ", name)-1)
  • name: De volledige naam of celverwijzing waaruit u de middelste naam wilt extraheren.

Voer de volgende formule in of kopieer deze naar een lege cel om het resultaat uit te voeren:

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

Sleep vervolgens de vulgreep naar de cellen om deze formule toe te passen, en alle middelste namen worden ook geëxtraheerd, zie screenshot:

Verklaring van de formule:

Om de middelste naam te extraheren, moet u eerst de positie van beide spaties tussen de volledige naam zoeken en vervolgens de MID-functie toepassen om de tekst in het midden van de tekstreeks te extraheren.

1. ZOEKEN (""; A2) + 1: Deze SEARCH-functie krijgt de positie van de eerste spatie, waarbij 1 wordt toegevoegd om de extractie vanaf het volgende teken te starten. Dit krijgt het nummer 7. Dit deel wordt herkend als het argument start_num binnen de MID-functie.

2. SEARCH ("", A2, SEARCH ("", A2) +1) - SEARCH ("", A2) -1: Dit deel wordt herkend als het num_chars-argument binnen de MID-functie.

  • SEARCH ("", A2, SEARCH ("", A2) +1): Dit deel van de formule wordt gebruikt om de positie van de tweede spatie te bepalen, het krijgt het nummer 9.
  • SEARCH (""; A2): Deze SEARCH-functie krijgt de positie van de eerste spatie in cel A2. Het krijgt het nummer 6.
  • SEARCH ("", A2, SEARCH ("", A2) +1) - SEARCH ("", 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, SEARCH ("", A2) + 1, SEARCH ("", A2, SEARCH ("", A2) +1) - SEARCH ("", A2) -1) = MIDDEN (A2,7,2 , XNUMX): Eindelijk haalt de MID-functie 2 tekens uit het midden van cel A2, die begint bij het zevende teken.

Note: Als er alleen voor- en achternaam in de volledige namen staan, krijgt de bovenstaande formule een foutwaarde zoals onderstaand screenshot:

Om dit probleem op te lossen, moet u de bovenstaande formule opnemen in de IFERROR-functie, pas de volgende formule toe:

=IFERROR(MID(A2, SEARCH(" ", A2) + 1, SEARCH(" ", A2, SEARCH(" ", A2)+1) - SEARCH(" ", A2)-1),"")

Extraheer of verkrijg voor-, midden- en achternaam van volledige namen met een geweldige functie

Als je pijnlijk bent met de bovenstaande formules, zal ik hier een eenvoudig hulpmiddel aanbevelen - Kutools for Excel, Met Namen splitsen functie, kunt u alle voor-, midden- en achternaam in één keer in afzonderlijke cellen uitpakken.  Klik om Kutools voor Excel gratis uit te proberen!

Gebruikte relatieve functies:

  • LEFT:
  • De LEFT-functie haalt het opgegeven aantal tekens uit de linkerkant van een opgegeven string.
  • RIGHT:
  • De functie RIGHT wordt gebruikt om een ​​bepaald aantal tekens uit de rechterkant van de tekstreeks te halen
  • MID:
  • De MID-functie wordt gebruikt om een ​​bepaald aantal tekens uit het midden van een gegeven tekstreeks te zoeken en terug te sturen.
  • LEN:
  • De functie LEN retourneert het aantal tekens in een tekstreeks.
  • FIND:
  • De functie FIND wordt gebruikt om een ​​string binnen een andere string te vinden, en geeft de startpositie van de string in een andere string terug.
  • SEARCH:
  • De SEARCH-functie kan u helpen om de positie van een specifiek teken of substring uit de opgegeven tekstreeks te vinden
  • SUBSTITUTE:
  • De Excel SUBSTITUTE-functie vervangt tekst of tekens binnen een tekstreeks door een andere tekst of tekens.

Meer artikelen:

  • Pak de eerste voor- en achternaam uit de volledige naam
  • Stel dat u een lijst met volledige namen heeft, dan wilt u de eerste voor- en achternaam of voornaam en het eerste teken van de achternaam uit de volledige naam halen, zoals hieronder wordt getoond. Hoe zou u met deze taken in een Excel-werkmap kunnen omgaan?
  • Omdraaien of omkeren voor- en achternaam in Excel-lijst
  • Als u een lijst met volledige namen heeft die zijn opgemaakt als achternaam en voornaam, wilt u nu de achternaam en voornaam omdraaien naar voor- en achternaam, zoals hieronder wordt getoond. In dit artikel zal ik enkele formules introduceren voor het omgaan met deze taak in Excel.
  • Extraheer meerdere regels uit een cel
  • Als je een lijst met tekstreeksen hebt die zijn gescheiden door regeleinden (dat gebeurt door op Alt + Enter te drukken bij het invoeren van de tekst), en nu wil je deze regels tekst extraheren in meerdere cellen, zoals onderstaand screenshot. Hoe zou je het kunnen oplossen met een formule in Excel?
  • Extraheer N-de woord uit tekststring in Excel
  • Als u een lijst met tekstreeksen of zinnen heeft, wilt u nu het specifieke n-de woord uit de lijst extraheren, zoals hieronder wordt getoond. In dit artikel zal ik enkele methoden introduceren om deze taak in Excel op te lossen.

De beste tools voor kantoorproductiviteit

Kutools for Excel - Helpt u zich te onderscheiden van de menigte

🤖 Kutools AI-assistent: Een revolutie teweegbrengen in de data-analyse op basis van: Intelligente uitvoering   |  Genereer code  |  Aangepaste formules maken  |  Analyseer gegevens en genereer grafieken  |  Roep Kutools-functies aan...
Populaire functies: Zoek, markeer of identificeer duplicaten  |  Verwijder lege rijen  |  Combineer kolommen of cellen zonder gegevens te verliezen  |  Ronde zonder formule ...
Super VLookup: Meerdere criteria  |  Meerdere waarde  |  Over meerdere vellen  |  Fuzzy opzoeken...
Gev. Keuzelijst: Gemakkelijke vervolgkeuzelijst  |  Afhankelijke vervolgkeuzelijst  |  Multi-select vervolgkeuzelijst...
Kolom Beheerder: Voeg een specifiek aantal kolommen toe  |  Kolommen verplaatsen  |  Schakel de zichtbaarheidsstatus van verborgen kolommen in  |  Vergelijk Kolommen met Selecteer dezelfde en verschillende cellen ...
Uitgelichte functies: Raster focus  |  Ontwerpweergave  |  Grote formulebalk  |  Werkmap- en bladbeheer | resource Library (Auto-tekst)  |  Datumkiezer  |  Combineer werkbladen  |  Cellen coderen/decoderen  |  Stuur e-mails per lijst  |  Super filter  |  Speciaal filter (filter vet/cursief/doorhalen...) ...
Top 15 gereedschapsets:  12 Tekst Tools (toe te voegen tekst, Tekens verwijderen ...)  |  50+ tabel Types (Gantt Chart ...)  |  40+ Praktisch Formules (Bereken leeftijd op basis van verjaardag ...)  |  19 Invoeging Tools (QR-code invoegen, Afbeelding invoegen vanaf pad ...)  |  12 Camper ombouw Tools (Getallen naar woorden, Currency Conversion ...)  |  7 Samenvoegen en splitsen Tools (Geavanceerd Combineer rijen, Excel-cellen splitsen ...)  |  ... en meer

Kutools voor Excel beschikt over meer dan 300 functies, Ervoor zorgen dat wat u nodig heeft slechts één klik verwijderd is...

Office-tabblad - Schakel lezen en bewerken met tabbladen in Microsoft Office in (inclusief Excel)

  • Een seconde om te schakelen tussen tientallen geopende documenten!
  • Verminder elke dag honderden muisklikken voor u, zeg maar dag tegen muishand.
  • 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.