Oddělte jméno a příjmení v Excelu (rozdělte jména pomocí vzorců)

Sledujte video - Rozdělte jména v aplikaci Excel (na jméno, prostřední a příjmení)

Excel je úžasný nástroj, pokud jde o krájení a krájení textových dat.

Existuje tolik užitečných vzorců a funkcí, které můžete použít pro práci s textovými daty v Excelu.

Jednou z velmi častých otázek, které na manipulaci s textovými daty dostávám, je - “Jak oddělit jména a příjmení (nebo jména, prostřední a příjmení) v aplikaci Excel?“.

V aplikaci Excel existuje několik jednoduchých způsobů, jak rozdělit názvy. Metoda, kterou zvolíte, bude záviset na tom, jak jsou vaše data strukturována a zda chcete, aby výsledky byly statické nebo dynamické.

Pojďme tedy začít a podívejme se na různé způsoby rozdělení jmen v Excelu.

Rozdělit jména pomocí textu na sloupce

Funkce text na sloupce v aplikaci Excel vám umožňuje rychle rozdělit textové hodnoty do samostatných buněk v řadě.

Předpokládejme například, že máte datovou sadu, jak je uvedeno níže, a chcete oddělit jméno a příjmení a získat je v samostatných buňkách.

Níže jsou uvedeny kroky k oddělení jména a příjmení pomocí textu na sloupce:

1. Vyberte všechna jména ve sloupci (v tomto příkladu A2: A10)
2. Klikněte na kartu „Data“
3. Ve skupině „Datové nástroje“ klikněte na možnost „Text do sloupců“.
4. V Průvodci převodem textu na sloupec proveďte následující změny:
   1. Krok 1 ze 3: Vyberte Oddělovač (to vám umožní použít jako oddělovač mezeru) a klikněte na Další
   2. Krok 2 ze 3: Vyberte možnost Mezerník a klikněte na Další
   3. Krok 3 ze 3: Nastavte B2 jako cílovou buňku (jinak přepíše stávající data)
5. Klikněte na Dokončit

Výše uvedené kroky by okamžitě rozdělily jména na jméno a příjmení (s křestními jmény ve sloupci B a příjmením ve sloupci C).

Poznámka: V případě, že v buňkách již jsou nějaká data (ta, kde se očekává výstup textu do sloupců), Excel vám zobrazí varování, které vás upozorní, že v buňkách již nějaká data jsou. Můžete se rozhodnout data přepsat nebo zrušit text na sloupce a nejprve je ručně odebrat.

Po dokončení můžete data úplného jména odstranit, pokud chcete.

Několik věcí, které je třeba vědět při použití textu na sloupce k oddělení jmen a příjmení v aplikaci Excel:

1. Výsledek je statický. To znamená, že v případě, že máte nová data nebo původní data mají nějaké změny, musíte to udělat znovu, abyste rozdělili jména.
2. Pokud chcete pouze křestní jméno nebo pouze příjmení, můžete přeskočit sloupce, které nechcete, v kroku 3 dialogového okna „Průvodce textem do sloupce“. Chcete -li to provést, vyberte v náhledu sloupec, který chcete přeskočit, a poté vyberte možnost „Neimportovat sloupec (přeskočit)“.
3. V případě, že nezadáte cílovou buňku, text do sloupce přepíše aktuální sloupec

Možnost Text do sloupců je nejvhodnější, pokud máte konzistentní data (například všechna jména mají pouze jméno a příjmení nebo všechna jména mají jméno, prostřední a příjmení).

V tomto příkladu jsem vám ukázal, jak oddělit názvy, které mají jako oddělovač mezeru. V případě, že oddělovač je čárka nebo kombinace čárky a mezery, můžete stále použít stejné kroky. V takovém případě můžete oddělovač zadat v kroku 2 ze 3 průvodce. Již existuje možnost použít jako oddělovač čárku, nebo můžete vybrat možnost „Jiné“ a určit také vlastní oddělovač.

Přestože je text do sloupců rychlý a efektivní způsob rozdělení názvů, je vhodný pouze v případě, že chcete, aby byl výstup statickým výsledkem. V případě, že máte datovou sadu, která se může rozšířit nebo změnit, je lepší použít k oddělení názvů vzorce.

Oddělte křestní jména, prostřední jména a příjmení pomocí vzorců

Vzorce vám umožňují krájet a krájet textová data a extrahovat, co chcete.

V této části budu sdílet různé vzorce, které můžete použít k oddělení údajů o jménech (podle toho, jak jsou vaše data strukturována).

Níže jsou uvedeny tři vzorce, které můžete použít k oddělení jména, prostředního jména a příjmení (podrobně vysvětleno dále v následujících částech).

Vzorec pro získání křestního jména:

= VLEVO (A2, HLEDAT ("", A2) -1)

Vzorec pro získání prostředního jména:

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

Vzorec pro získání příjmení:

= VPRAVO (A15, LEN (A15)-VYHLEDÁVÁNÍ ("@", SUBSTITUTE (A15, "", "@", LEN (A15) -LEN (SUBSTITUTE (A15, "", "" ""))))))

Získejte křestní jméno

Předpokládejme, že máte datovou sadu, jak je uvedeno níže, a chcete rychle oddělit křestní jméno v jedné buňce a příjmení v jedné buňce.

Následující vzorec vám poskytne křestní jméno:

= VLEVO (A2, HLEDAT ("", A2) -1)

Výše uvedený vzorec používá funkci VYHLEDÁVÁNÍ k získání pozice znaku mezery mezi jménem a příjmením. Funkce DOLEVA potom použije toto číslo pozice mezery k extrahování veškerého textu před ním.

Toto je poměrně jednoduché použití extrahování části textové hodnoty. Protože vše, co musíme udělat, je identifikovat první pozici mezerníku, nezáleží na tom, zda má jméno nějaké prostřední jméno nebo ne. Výše uvedený vzorec bude fungovat dobře.

Pojďme nyní s každým příkladem trochu pokročileji.

Získejte příjmení

Řekněme, že máte stejnou datovou sadu a tentokrát potřebujete získat příjmení.

Níže uvedený vzorec extrahuje příjmení z výše uvedené datové sady:

= RIGHT (A2, LEN (A2)-VYHLEDÁVÁNÍ ("", A2))

Opět celkem přímočaře.

Tentokrát nejprve zjistíme pozici znaku mezery, která se poté použije ke zjištění počtu znaků, které zbývají za mezerou (což by bylo příjmení).

Toho je dosaženo odečtením hodnoty pozice znaku mezery od celkového počtu znaků v názvu.

Toto číslo je poté použito ve funkci DOPRAVA k načtení všech těchto znaků napravo od jména.

Přestože tento vzorec funguje skvěle, když je uvedeno pouze jméno a příjmení, nefungovalo by to v případě, že máte také prostřední jméno. Důvodem je, že jsme účtovali pouze jeden znak mezery (mezi jménem a příjmením). Mít prostřední jméno přidá do názvu více mezer.

Chcete -li získat příjmení, když máte také prostřední jméno, použijte následující vzorec:

= VPRAVO (A15, LEN (A15)-VYHLEDÁVÁNÍ ("@", SUBSTITUTE (A15, "", "@", LEN (A15) -LEN (SUBSTITUTE (A15, "", "" ""))))))

Nyní to začalo být trochu složité … že?

Dovolte mi vysvětlit, jak to funguje.

Výše uvedený vzorec nejprve najde celkový počet mezer v názvu. To se provádí tak, že se získá délka jména s mezerou a bez ní a pak se odečte ta bez mezery od mezery. To dává celkový počet mezer.

Funkce SUBSTITUTE se pak používá k nahrazení posledního znaku mezery symbolem „@“ (můžete použít jakýkoli symbol - něco, co se pravděpodobně nevyskytuje jako součást názvu).

Jakmile nahradíte symbol @ místo posledního znaku mezery, můžete snadno najít polohu tohoto symbolu @. To se provádí pomocí funkce HLEDAT.

Nyní stačí extrahovat všechny znaky napravo od tohoto symbolu @. To se provádí pomocí funkce DOPRAVA.

Získejte prostřední jméno

Předpokládejme, že máte datovou sadu, jak je uvedeno níže, a chcete extrahovat prostřední jméno.

Následující vzorec to udělá:

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

Výše uvedený vzorec používá funkci MID, která vám umožňuje určit počáteční pozici a počet znaků, které chcete z této pozice extrahovat.

Počáteční pozici lze snadno najít pomocí funkce HLEDAT.

Nejtěžší je zjistit, kolik znaků extrahovat po této počáteční pozici. Abyste toho dosáhli, musíte zjistit, kolik znaků je od počáteční pozice do posledního mezerníku.

To lze provést pomocí funkce SUBSTITUTE a nahradit poslední znak mezery symbolem „@“. Jakmile to bude hotové, můžete snadno použít funkci HLEDAT a najít polohu tohoto posledního mezerníku.

Nyní, když máte počáteční pozici a pozici posledního místa, můžete prostřední jméno snadno načíst pomocí funkce MID.

Jednou z výhod použití vzorce k oddělení názvů je, že výsledek je dynamický. V případě, že se vaše datová sada rozšíří a budou do ní přidány další názvy, nebo pokud se některá jména v původní datové sadě změní, nemusíte si s výslednými daty dělat starosti.

Oddělte jména pomocí funkce Najít a nahradit

Miluji flexibilitu, kterou přináší „Najít a nahradit“ - protože v ní můžete použít znaky zástupných znaků.

Nejprve vysvětlím, co je to divoká karta.

Zástupný znak je něco, co můžete použít místo jakéhokoli textu. Můžete například použít symbol hvězdičky (*) a bude v aplikaci Excel představovat libovolný počet znaků. Abych vám dal příklad, pokud chci najít všechna jména začínající na abecedu A, mohu použít A* při hledání a nahrazování. Tím se vyhledají a vyberou všechny buňky, kde název začíná na A.

Pokud vám to stále není jasné, nezoufejte. Pokračujte ve čtení a několik dalších příkladů vám objasní, co jsou zástupné znaky a jak je použít k rychlému oddělení názvů (nebo jakýchkoli textových hodnot v aplikaci Excel).

Ve všech níže uvedených příkladech vytvořte záložní kopii datové sady. Funkce Najít a nahradit změní data, na kterých je použita. Nejlepší je nejprve zkopírovat a vložit data a poté použít na zkopírované datové sadě funkci Najít a nahradit.

Získejte křestní jméno

Předpokládejme, že máte datovou sadu, jak je uvedeno níže, a chcete získat pouze křestní jméno.

Níže jsou uvedeny kroky, jak to provést:

1. Zkopírujte data názvu do sloupce A a vložte je do sloupce B.
2. S vybranými daty ve sloupci B klikněte na kartu Domů
3. Ve skupině Úpravy klikněte na Najít a vybrat.
4. Klikněte na Nahradit. Otevře se dialogové okno „Najít a nahradit“.
5. V dialogovém okně „Najít a nahradit“ zadejte následující
   1. Najděte co: * (mezerník následovaný symbolem hvězdičky)
   2. Nahradit: ponechte toto prázdné
6. Klikněte na Nahradit vše.

Výše uvedené kroky vám poskytnou křestní jméno a odstraní vše za křestním jménem.

To funguje, i když máte jména se středním jménem.

Profesionální tip: Klávesová zkratka pro otevření dialogového okna Najít a nahradit je Ovládání + H (podržte ovládací klávesu a poté stiskněte klávesu H).

Získejte příjmení

Předpokládejme, že máte datovou sadu, jak je uvedeno níže, a chcete získat pouze příjmení.

Níže jsou uvedeny kroky, jak to provést:

1. Zkopírujte data názvu do sloupce A a vložte je do sloupce B.
2. S vybranými daty ve sloupci B klikněte na kartu Domů
3. Ve skupině Úpravy klikněte na Najít a vybrat.
4. Klikněte na Nahradit. Otevře se dialogové okno „Najít a nahradit“.
5. V dialogovém okně „Najít a nahradit“ zadejte následující
   1. Najděte co: * (symbol hvězdičky následovaný mezerou)
   2. Nahradit: ponechte toto prázdné
6. Klikněte na Nahradit vše.

Výše uvedené kroky vám poskytnou příjmení a odstraní vše před křestním jménem.

To funguje, i když máte jména se středním jménem.

Odeberte prostřední jméno

V případě, že se chcete zbavit pouze prostředního jména a mít pouze křestní jméno a příjmení, můžete to provést pomocí funkce Najít a nahradit.

Předpokládejme, že máte datovou sadu, jak je uvedeno níže, a chcete z nich odebrat prostřední jméno.

Níže jsou uvedeny kroky, jak to provést:

1. Zkopírujte data názvu do sloupce A a vložte je do sloupce B.
2. S vybranými daty ve sloupci B klikněte na kartu Domů
3. Ve skupině Úpravy klikněte na Najít a vybrat.
4. Klikněte na Nahradit. Otevře se dialogové okno „Najít a nahradit“.
5. V dialogovém okně „Najít a nahradit“ zadejte následující
   1. Najděte co: * (mezerník následovaný symbolem hvězdičky následovaný mezerou)
   2. Nahradit: (stačí sem zadat mezerník)
6. Klikněte na Nahradit vše.

Výše uvedené kroky by odstranily prostřední jméno z celého jména. V případě, že některá jména nemají žádné prostřední jméno, nebudou změněna.

Oddělte jména pomocí Flash Fill

Flash fill byl představen v aplikaci Excel 2013 a umožňuje opravdu snadno upravit nebo vyčistit sadu textových dat.

A pokud jde o oddělení údajů o jménech, je to přímo v uličce Flash Fill.

Nejdůležitější věcí, kterou je třeba při používání Flash Fill vědět, je, že je potřeba vzor, ​​který flash fill dokáže identifikovat. Jakmile identifikuje vzorec, snadno vám pomůže rozdělit jména v aplikaci Excel (v tomto získáte jasnější informace, když projdete několika příklady níže).

Získejte křestní jméno nebo příjmení od celého jména

Předpokládejme, že máte datovou sadu, jak je uvedeno níže, a chcete získat pouze křestní jméno.

1. Do sousední buňky ručně zadejte křestní jméno z celého jména. V tomto případě bych napsal Ricka.
2. Do druhé buňky ručně zadejte křestní jméno z názvu sousední buňky. Během psaní uvidíte Flash Fill, který vám automaticky zobrazí seznam křestního jména (šedě).
3. Když uvidíte jména šedě, rychle se na ně podívejte, abyste se ujistili, že zobrazují správná jména. Pokud jsou správná, stiskněte klávesu Enter a Flash Fill automaticky vyplní zbytek buněk křestním jménem.

Flash Fill potřebuje, abyste mu dali vzor, ​​který může dodržovat při poskytování upravených dat. V našem případě, když do první buňky zadáte křestní jméno, Flash Fill nemůže zjistit vzor.

Ale jakmile začnete zadávat křestní jméno do druhé buňky, Flash Fill porozumí vzoru a ukáže vám nějaký návrh. Pokud je návrh správný, stačí stisknout klávesu Enter.

A pokud to není správné, můžete zkusit zadat ručně několik dalších buněk a zkontrolovat, zda Flash Fill dokáže rozeznat vzor nebo ne.

Někdy se může stát, že vzor neuvidíte šedě (jak je uvedeno v kroku 2 výše). Pokud tomu tak je, k získání výsledku Flash Fill postupujte podle následujících pokynů:

1. Zadejte text ručně do dvou buněk.
2. Vyberte obě tyto buňky
3. Umístěte kurzor na pravou dolní část výběru. Všimnete si, že se kurzor změní na ikonu plus
4. Poklepejte na něj (levé tlačítko myši). Tím se vyplní všechny buňky. V tomto okamžiku jsou výsledky pravděpodobně nesprávné a ne to, co jste očekávali.
5. V pravé dolní části výsledných dat uvidíte malou ikonu automatického vyplňování. Klikněte na tuto ikonu automatického vyplňování
6. Klikněte na Flash Fill

Výše uvedené kroky vám poskytnou výsledek z Flash Fill (na základě vzoru, který odvodil).

K vyplnění příjmení nebo prostředního jména můžete také použít Flash Fill. Do prvních dvou buněk zadejte příjmení (nebo prostřední jméno) a flash fill bude schopen porozumět vzoru

Změňte uspořádání názvu pomocí Flash Fill

Flash Fill je chytrý nástroj a dokáže rozluštit i mírně složitý vzor

Předpokládejme například, že máte datovou sadu, jak je uvedeno níže, a chcete změnit uspořádání jména od Rick Novak na Novak, Rick (kde na prvním místě je příjmení následované čárkou a poté křestním jménem).

Níže jsou uvedeny kroky, jak to provést:

1. Do sousední buňky zadejte ručně Novak, Ricku
2. Do druhé buňky zadejte ručně Connore, Susan. Během psaní se vám ve Flash Fill zobrazí seznam jmen ve stejném formátu (šedě).
3. Když uvidíte jména šedě, rychle se na ně podívejte, abyste se ujistili, že zobrazují správná jména. Pokud jsou správná, stiskněte klávesu Enter a Flash Fill automaticky vyplní zbytek buněk jmény ve stejném formátu.

Odebrat prostřední jméno (nebo jen získat prostřední jméno)

Můžete také použít Flash Fill, abyste se zbavili prostředního jména nebo získali pouze prostřední jméno.

Předpokládejme například, že máte datovou sadu, jak je uvedeno níže, a chcete získat pouze křestní jméno a příjmení, nikoli prostřední jméno.

Níže jsou uvedeny kroky, jak to provést:

1. Do sousední buňky zadejte ručně Rick Novak
2. Do druhé buňky zadejte ručně Susan Connorová. Během psaní se vám ve Flash Fill zobrazí seznam jmen ve stejném formátu (šedě).
3. Když uvidíte jména šedě, rychle se na ně podívejte, abyste se ujistili, že zobrazují správná jména. Pokud jsou správná, stiskněte klávesu Enter a Flash Fill automaticky vyplní zbytek buněk jmény bez prostředního jména.

Podobně, pokud chcete získat pouze prostřední jména, zadejte prostřední jméno do prvních dvou buněk a pomocí Flash Fill získáte prostřední jméno ze všech zbývajících jmen.

Příklady uvedené v tomto kurzu používají názvy při manipulaci s textovými daty. Stejné koncepty můžete použít také pro práci s jinými formáty dat (jako jsou adresy, názvy produktů atd.)