Extrahera för- och efternamn från fullständigt namn i Excel
Om du antar att du har en lista med användarnamn, nu vill du dela hela namnen till för-, mellan- och efternamn i separata kolumner enligt följande skärmdump. Den här artikeln kommer jag att presentera några formler för att hantera detta jobb i Excel.
- Extrahera eller få förnamn från fullständiga namn i Excel
- Extrahera eller få efternamn från fullständiga namn i Excel
- Extrahera eller få mellannamn från fullständiga namn i Excel
- Extrahera eller få för-, mellan- och efternamn från fullständiga namn med en fantastisk funktion
Extrahera eller få förnamn från fullständiga namn i Excel
För att extrahera eller få förnamnen från fullständiga namn kan VÄNSTER och SÖK-funktionen hjälpa dig, den generiska syntaxen är:
- name: Det fullständiga namnet eller cellreferensen som du vill extrahera först från.
Vänligen kopiera eller skriv in följande formel i en tom cell där du vill få resultatet:
Dra sedan fyllningshandtaget ner till cellerna du vill använda den här formeln, och alla förnamn har extraherats enligt nedanstående skärmdump:
Förklaring av formeln:
SÖK ("", A2) -1: Denna SÖK-funktion används för att få positionen för det första mellanslagsteckenet, subtrahera 1 betyder att antalet tecken som du vill ha är ett mindre än platsens plats. Detta får resultatet: 5.
VÄNSTER (A2, SÖK ("", A2) -1) = VÄNSTER ("Jerry T. Stone", 5): Denna VÄNSTER-funktion extraherar texten i det fullständiga namnet "Jerry T. Stone" från vänster sida med 5 tecken som returneras av SÖK-funktionen.
Extrahera eller få efternamn från fullständiga namn i Excel
För att extrahera efternamnen från fullständiga namn kan kombinationen av RIGHT, LEN, FIND, LEN och SUBSTITUTE-funktionerna göra dig en tjänst. Den generiska syntaxen är:
- name: Det fullständiga namnet eller cellreferensen som du vill extrahera efternamn från.
Kopiera eller skriv in formeln nedan i en tom cell där du vill få efternamnen:
Och dra sedan påfyllningshandtaget ner till cellerna för att fylla denna formel, och alla efternamn har extraherats från de fullständiga namnen, se skärmdump:
Förklaring av formeln:
1. LENN (A2) -LEN (SUBSTITUT (A2, "", "")): Denna del används för att få antalet mellanslagstecken i cell A2.
- LENN (A2): Denna LEN-funktion returnerar det totala antalet tecken i cell A2. Det kommer att återvända: 14.
- SUBSTITUT (A2, "", ""): Denna SUBSTITUTE-funktion används för att ersätta alla mellanslagstecken med ingenting. Och du får resultatet som detta: ”JerryT. Sten".
- LENN (SUBSTITUT (A2, "", ""): Få den totala längden på hela namnet i cell A2 utan mellanslag.
- LENN (A2) -LEN (SUBSTITUT (A2, "-", "")): Subtrahera längden på det fullständiga namnet utan mellanslag från den totala längden på hela namnet för att få utrymmets nummer, och detta får 2.
2. SUBSTITUTE (A2, "", "*", LENN (A2) -LEN (SUBSTITUTE (A2, "", ""))) = SUBSTITUT (A2, "", "*", 2): Denna SUBSTITUTE-funktion används för att ersätta den sista förekomsten av utrymmet som returneras av den första delformeln med ett * tecken. Och du får det här resultatet: “Jerry T. * Stone".
3. HITTA ("*", SUBSTITUTE (A2, "", "*", LENN (A2) -LEN (SUBSTITUTE (A2, "", "")))) = HITTA ("*", "Jerry T. * Sten "): HITTA-funktionen returnerar positionen för * -tecknet i textsträngen som returneras av SUBSTITUTE-funktionen och du får siffran 9.
4. LENN (A2) -FIND ("*", SUBSTITUT (A2, "", "*", LENN (A2) -LEN (SUBSTITUT (A2, "", "")))) = LENN (A2) - 9: Denna del av formeln får hur många tecken som finns efter det sista mellanslaget. Detta får siffran 5. Detta kommer att erkännas som argumentet num_chars i funktionen HÖGER.
5. HÖGER (A2, LENN (A2) -FIND ("*", SUBSTITUTE (A2, "", "*", LENN (A2) -LEN (SUBSTITUTE (A2, "", ""))))) = HÖGER (A2, 5): Äntligen används RIGHT-funktionen för att extrahera 5 tecken som returneras med formeln i steg 4 från höger sida av textsträngen i cell A2.
Anmärkningar: Om din lista med fullständiga namn bara innehåller för- och efternamn kan du använda följande enkla formel för att få efternamnet som du behöver:
Extrahera eller få mellannamn från fullständiga namn i Excel
Om du behöver extrahera mellannamnen från de fullständiga namnen, skapas denna formel av funktionerna MID och SÖK. Den generiska syntaxen är:
- name: Det fullständiga namnet eller cellreferensen som du vill extrahera mellannamn från.
Ange eller kopiera följande formel till en tom cell för att mata ut resultatet:
Dra sedan fyllningshandtaget ner till cellerna för att tillämpa denna formel, och alla mellannamn extraheras också, se skärmdump:
Förklaring av formeln:
För att extrahera mittnamnet bör du först hitta positionen för båda mellanslagen mellan hela namnet och sedan använda MID-funktionen för att extrahera texten i mitten av textsträngen.
1. SÖK ("", A2) + 1: Denna SÖK-funktion kommer att få positionen för det första mellanslaget och lägga till 1 medel för att starta extraktionen från nästa tecken. Detta får siffran 7. Denna del känns igen som argumentet start_num i MID-funktionen.
2. SÖK ("", A2, SÖK ("", A2) +1) - SÖK ("", A2) -1: Denna del känns igen som argumentet num_chars inom MID-funktionen.
- SÖK ("", A2, SÖK ("", A2) +1): Denna del av formeln används för att få positionen för det andra mellanslaget, det kommer att få siffran 9.
- SÖK ("", A2): Denna SÖK-funktion får positionen för det första utrymmet i cell A2. Det kommer att få siffran 6.
- SÖK ("", A2, SÖK ("", A2) +1) - SÖK ("", A2) -1 = 9-6-1: Subtrahera positionen för det första utrymmet från positionen för det andra utrymmet och subtrahera sedan 1 från resultatmedlet för att ta bort ett ledande utrymme. Och resultatet är 2.
3. MID (A2, SÖK ("", A2) + 1, SÖK ("", A2, SÖK ("", A2) +1) - SÖK ("", A2) -1) = MID (A2,7,2 , XNUMX): Äntligen extraherar MID-funktionen två tecken i mitten av cell A2 som börjar vid det sjunde tecknet.
Anmärkningar: Om det bara finns för- och efternamn i de fullständiga namnen får ovanstående formel ett felvärde enligt nedanstående skärmdump:
För att åtgärda detta problem bör du bifoga formeln ovan i IFERROR-funktionen, använd följande formel:
Extrahera eller få för-, mellan- och efternamn från fullständiga namn med en fantastisk funktion
Om du är smärtsam med ovanstående fromuler, här, kommer jag att rekommendera ett enkelt verktyg - Kutools för Excel, med dess Delade namn funktionen kan du extrahera alla för-, mellan- och efternamn i separata celler samtidigt. Klicka för att ladda ner Kutools för Excel för en gratis provperiod!
Relativa funktioner som används:
- LEFT:
- VÄNSTER-funktionen extraherar det angivna antalet tecken från vänster sida av en medföljande sträng.
- RIGHT:
- RIGHT-funktionen används för att extrahera ett visst antal tecken från textsträngens högra sida
- MID:
- MID-funktionen används för att hitta och returnera ett visst antal tecken från mitten av den angivna textsträngen.
- LEN:
- LEN-funktionen returnerar antalet tecken i en textsträng.
- FIND:
- HITTA-funktionen används för att hitta en sträng inom en annan sträng och returnerar startpositionen för strängen inuti en annan sträng.
- SEARCH:
- SEARCH-funktionen kan hjälpa dig att hitta positionen för ett visst tecken eller en delsträng från den givna textsträngen
- SUBSTITUTE:
- Excel SUBSTITUTE-funktionen ersätter text eller tecken i en textsträng med en annan text eller andra tecken.
Fler artiklar:
- Extrahera för- och efternamn från fullständigt namn
- Om du antar att du har en lista med fullständiga namn, nu vill du extrahera första för- och efternamn eller förnamn och förnamn i efternamn från det fullständiga namnet som visas nedan. Hur kan du hantera dessa uppgifter i Excel-arbetsbok?
- Vänd eller vänd för- och efternamn i Excel-listan
- Om du har en lista med fullständiga namn som är formaterade som efternamn och förnamn, nu vill du vända efternamn och förnamn till för- och sista som nedanstående skärmdump. Den här artikeln kommer jag att presentera några formler för att hantera detta jobb i Excel.
- Extrahera flera linjer från en cell
- Om du har en lista med textsträngar som är åtskilda av radbrytningar (som inträffar genom att trycka på Alt + Enter-tangenterna när du skriver in texten), och nu vill du extrahera dessa textrader i flera celler som visas nedan. Hur kunde du lösa det med en formel i Excel?
- Extrahera Nth Word från textsträng i Excel
- Om du har en lista med textsträngar eller meningar vill du nu extrahera det specifika n: a ordet från listan enligt nedanstående skärmdump. Den här artikeln kommer jag att presentera några metoder för att lösa detta jobb i Excel.
De bästa Office-produktivitetsverktygen
Kutools för Excel - Hjälper dig att sticka ut från mängden
Kutools för Excel har över 300 funktioner, Se till att det du behöver bara är ett klick bort...
Fliken Office - Aktivera läsning och redigering av flikar i Microsoft Office (inkluderar Excel)
- En sekund att växla mellan dussintals öppna dokument!
- Minska hundratals musklick åt dig varje dag, säg adjö till mushanden.
- Ökar din produktivitet med 50 % när du visar och redigerar flera dokument.
- Ger effektiva flikar till Office (inklusive Excel), precis som Chrome, Edge och Firefox.
