Přejít k hlavnímu obsahu

Extrahujte křestní jméno a příjmení z celého jména v aplikaci Excel

Předpokládejme, že máte seznam uživatelských jmen, nyní byste chtěli rozdělit celá jména na první, prostřední a příjmení v oddělených sloupcích, jak ukazuje následující snímek obrazovky. V tomto článku uvedu několik vzorců pro řešení této úlohy v aplikaci Excel.


Extrahujte nebo získejte křestní jména z celých jmen v aplikaci Excel

Chcete-li extrahovat nebo získat křestní jména z celých jmen, funkce LEFT and SEARCH vám může pomoci, obecná syntaxe je:

=LEFT(name,FIND(" ",name)-1)
  • name: Celé jméno nebo odkaz na buňku, ze kterého chcete nejprve extrahovat.

Zkopírujte nebo zadejte následující vzorec do prázdné buňky, kde chcete získat výsledek:

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

Poté přetáhněte úchyt výplně dolů do buněk, na které chcete použít tento vzorec, a všechna křestní jména byla extrahována, jak je uvedeno níže:


Vysvětlení vzorce:

SEARCH ("", A2) -1: Tato funkce SEARCH se používá k získání pozice prvního znaku mezery, odečtení 1 znamená, že počet požadovaných znaků je o jeden menší než pozice mezery. Získá se výsledek: 5.

VLEVO (A2, SEARCH ("", A2) -1) = VLEVO ("Jerry T. Stone", 5): Tato LEVÁ funkce extrahuje text celým jménem „Jerry T. Stone“ z levé strany s 5 znaky, které jsou vráceny funkcí SEARCH.


Extrahujte nebo získejte příjmení z celých jmen v aplikaci Excel

Chcete-li extrahovat příjmení z celých jmen, zvýhodněním vám může být kombinace funkcí VPRAVO, DÉLKA, HLEDÁNÍ, DÉLKA a NÁHRADA. Obecná syntaxe je:

=RIGHT(name,LEN(name)-FIND("*",SUBSTITUTE(name," ","*",LEN(name)-LEN(SUBSTITUTE(name," ","")))))
  • name: Celé jméno nebo odkaz na buňku, ze kterého chcete extrahovat příjmení.

Zkopírujte nebo zadejte níže uvedený vzorec do prázdné buňky, kde chcete získat příjmení:

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

A potom přetáhněte popisovač výplně dolů do buněk, abyste vyplnili tento vzorec, a všechna příjmení byla extrahována z celých jmen, viz screenshot:


Vysvětlení vzorce:

1. LEN (A2) -LEN (NÁHRADA (A2, "", "")): Tato část slouží k získání počtu znaků mezery v buňce A2.

  • DÉLKA (A2): Tato funkce LEN vrací celkový počet znaků v buňce A2. Vrátí se: 14.
  • NÁHRADA (A2, "", ""): Tato funkce SUBSTITUTE slouží k nahrazení všech znaků mezer ničím. Výsledek získáte takto: „JerryT. Stone".
  • LEN (SUBSTITUTE (A2, "", ""): Získejte celkovou délku celého jména v buňce A2 bez mezer.
  • LEN (A2) -LEN (SUBSTITUTE (A2, "-", "")): Odečtěte délku celého jména bez mezery od celkové délky celého jména, abyste získali číslo mezery, a tím získáte 2.

2. SUBSTITUTE (A2, "", "*", LEN (A2) -LEN (SUBSTITUTE (A2, "", ""))) = SUBSTITUTE (A2, "", "*", 2): Tato funkce SUBSTITUTE slouží k nahrazení posledního výskytu mezery vrácené vzorcem první části znakem *. A získáte tento výsledek: „Jerry T. * Kámen".

3. FIND ("*", SUBSTITUTE (A2, "", "*", LEN (A2) -LEN (SUBSTITUTE (A2, "", "")))) = FIND ("*", "Jerry T. * Kámen "): Funkce FIND vrátí pozici znaku * v textovém řetězci, který vrátí funkce SUBSTITUTE, a dostanete číslo 9.

4. LEN (A2) -FIND ("*", SUBSTITUTE (A2, "", "*", LEN (A2) -LEN (SUBSTITUTE (A2, "", "")))) = LEN (A2) - 9: Tato část vzorce získá, kolik znaků je za poslední mezerou. Získá se číslo 5. Toto bude rozpoznáno jako argument num_chars ve funkci RIGHT.

5. VPRAVO (A2, LEN (A2) -FIND ("*", SUBSTITUTE (A2, "", "*", LEN (A2) -LEN (SUBSTITUTE (A2, "", "")))) = VPRAVO (A2, 5): Nakonec se funkce RIGHT používá k extrakci 5 znaků, které jsou vráceny vzorcem v kroku 4, z pravé strany textového řetězce v buňce A2.


Poznámka: Pokud váš seznam celých jmen obsahuje pouze jméno a příjmení, můžete použít následující jednoduchý vzorec a získat příjmení podle potřeby:

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


Extrahujte nebo získejte prostřední jména z celých jmen v aplikaci Excel

Pokud potřebujete extrahovat prostřední jména z celých jmen, tento vzorec, který je vytvořen funkcemi MID a SEARCH. Obecná syntaxe je:

=MID(name, SEARCH(" ", name) + 1, SEARCH(" ", name, SEARCH(" ", name)+1) - SEARCH(" ", name)-1)
  • name: Celé jméno nebo odkaz na buňku, ze kterého chcete extrahovat prostřední jméno.

Výsledek zadejte nebo zkopírujte do prázdné buňky do následujícího vzorce:

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

Poté přetáhněte popisovač výplně dolů do buněk a použijte tento vzorec a extrahují se také všechna prostřední jména, viz screenshot:


Vysvětlení vzorce:

Chcete-li extrahovat prostřední jméno, měli byste nejprve najít pozici obou mezer mezi celým jménem a poté použít funkci MID k extrakci textu uprostřed textového řetězce.

1. VYHLEDÁVÁNÍ ("", A2) + 1: Tato funkce VYHLEDÁVÁNÍ získá pozici první mezery, přidání 1 znamená spuštění extrakce z dalšího znaku. Získá se číslo 7. Tato část je v rámci funkce MID rozpoznána jako argument start_num.

2. SEARCH ("", A2, SEARCH ("", A2) +1) - SEARCH ("", A2) -1: Tato část je rozpoznána jako argument num_chars ve funkci MID.

  • SEARCH ("", A2, SEARCH ("", A2) +1): Tato část vzorce se používá k získání pozice druhého prostoru, získá číslo 9.
  • SEARCH ("", A2): Tato funkce SEARCH získá pozici první mezery v buňce A2. Získá číslo 6.
  • SEARCH ("", A2, SEARCH ("", A2) +1) - SEARCH ("", A2) -1 = 9-6-1: Odečtěte pozici prvního prostoru od polohy druhého prostoru a poté odečtěte 1 od výsledku znamená odebrání úvodního prostoru. A výsledek je 2.

3. MID (A2, SEARCH ("", A2) + 1, SEARCH ("", A2, SEARCH ("", A2) +1) - SEARCH ("", A2) -1) = MID (A2,7,2) , XNUMX): Nakonec funkce MID extrahuje 2 znaky uprostřed buňky A2, která začíná sedmým znakem.


Poznámka: Pokud jsou v celých jménech pouze křestní jména a příjmení, výše uvedený vzorec získá chybovou hodnotu, jak je uvedeno níže:

Chcete-li tento problém vyřešit, měli byste přiložit výše uvedený vzorec do funkce IFERROR, použijte následující vzorec:

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


Extrahujte nebo získejte křestní, prostřední a příjmení z celých jmen s úžasnou funkcí

Pokud vás výše uvedená doporučení trápí, doporučím zde snadný nástroj - Kutools pro Excel, S jeho Rozdělit jména Funkce, můžete extrahovat všechna křestní jména, prostřední jména a příjmení do samostatných buněk najednou.  Klikněte a stáhněte si zdarma Kutools pro Excel!


Použité relativní funkce:

  • LEFT:
  • Funkce LEFT extrahuje daný počet znaků z levé strany zadaného řetězce.
  • RIGHT:
  • Funkce RIGHT se používá k extrakci konkrétního počtu znaků z pravé strany textového řetězce
  • MID:
  • Funkce MID se používá k vyhledání a vrácení konkrétního počtu znaků ze středu zadaného textového řetězce.
  • LEN:
  • Funkce LEN vrací počet znaků v textovém řetězci.
  • FIND:
  • Funkce FIND se používá k vyhledání řetězce v jiném řetězci a vrací počáteční pozici řetězce uvnitř jiného.
  • SEARCH:
  • Funkce SEARCH vám pomůže najít pozici konkrétního znaku nebo podřetězce z daného textového řetězce
  • SUBSTITUTE:
  • Funkce Excel SUBSTITUTE nahradí text nebo znaky v textovém řetězci jiným textem nebo znaky.

Další články:

  • Extrahujte křestní jméno a příjmení z celého jména
  • Předpokládejme, že máte seznam celých jmen, nyní byste chtěli extrahovat první počáteční a příjmení nebo křestní jméno a první znak příjmení z celého jména, jak je uvedeno níže. Jak byste se mohli s těmito úkoly vypořádat v sešitu aplikace Excel?
  • Extrahujte více řádků z buňky
  • Pokud máte seznam textových řetězců, které jsou odděleny zalomením řádků (k tomu dochází při stisknutí kláves Alt + Enter při zadávání textu), a nyní chcete tyto řádky textu extrahovat do více buněk, jak je ukázáno níže. Jak byste to mohli vyřešit pomocí vzorce v aplikaci Excel?

Nejlepší kancelářské nástroje produktivity

Kutools pro Excel - pomůže vám vyniknout před davem

Populární funkce: Najít, zvýraznit nebo identifikovat duplikáty  |  Odstranit prázdné řádky  |  Kombinujte sloupce nebo buňky bez ztráty dat  |  Kolo bez vzorce ...
Super VLookup: Více kritérií  |  Vícenásobná hodnota  |  Přes Multi-Sheets  |  Fuzzy vyhledávání...
Adv. Rozbalovací seznam: Snadno rozevírací seznam  |  Závislý rozbalovací seznam  |  Vícenásobný výběr rozevíracího seznamu...
Správce sloupců: Přidejte konkrétní počet sloupců  |  Přesunout sloupce  |  Přepnout stav viditelnosti skrytých sloupců  Porovnejte sloupce s Vyberte stejné a různé buňky ...
Doporučené funkce: Zaměření mřížky  |  Návrhové zobrazení  |  Velký Formula Bar  |  Správce sešitů a listů | Knihovna zdrojů (Automatický text)  |  Výběr data  |  Zkombinujte pracovní listy  |  Šifrovat/dešifrovat buňky  |  Odesílat e-maily podle seznamu  |  Super filtr  |  Speciální filtr (filtr tučné/kurzíva/přeškrtnuté...) ...
Top 15 sad nástrojů12 Text Tools (doplnit text, Odebrat znaky ...)  |  50+ Graf Typ nemovitosti (Ganttův diagram ...)  |  40+ Praktické Vzorce (Vypočítejte věk na základě narozenin ...)  |  19 Vložení Tools (Vložte QR kód, Vložit obrázek z cesty ...)  |  12 Konverze Tools (Čísla na slova, Přepočet měny ...)  |  7 Sloučit a rozdělit Tools (Pokročilé kombinování řádků, Rozdělit buňky Excelu ...)  |  ... a více

Kutools pro Excel se může pochlubit více než 300 funkcemi, Zajištění toho, že to, co potřebujete, je jen jedno kliknutí...


Záložka Office - Povolte čtení a úpravy na záložkách v Microsoft Office (včetně Excelu)

  • Jednu sekundu přepnete mezi desítkami otevřených dokumentů!
  • Snižte stovky kliknutí myší každý den, sbohem s myší rukou.
  • Zvyšuje vaši produktivitu o 50% při prohlížení a úpravách více dokumentů.
  • Přináší efektivní karty do Office (včetně Excelu), stejně jako Chrome, Edge a Firefox.
Comments (5)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
عندى اسم خماسي وعاوز لغايه الاسم الرباعى فقط
This comment was minimized by the moderator on the site
Hi,
Please try the below formula:
=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))), (4-1)*LEN(A2)+1, LEN(A2)))
Hope it can help you!
This comment was minimized by the moderator on the site
Eu gostaria de extrair o primeiro e do meio nomes juntos.
This comment was minimized by the moderator on the site
Eu gostaria de ter uma lista de todos nomes excepto o ultimo. Portanto, uma lista de nome completo sem o sobrenome.
This comment was minimized by the moderator on the site
you are a champ! very helpful!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations