Mohou nastat situace, kdy budete muset rozdělit buňky v aplikaci Excel. Může se jednat o to, když získáte data z databáze nebo je zkopírujete z internetu nebo od kolegů.

Jednoduchým příkladem, kdy je třeba rozdělit buňky v aplikaci Excel, je situace, kdy máte celá jména a chcete je rozdělit na jméno a příjmení.

Nebo získáte adresu ‚a chcete adresu rozdělit, abyste mohli samostatně analyzovat města nebo kód PIN.

Jak rozdělit buňky v aplikaci Excel

V tomto výukovém programu se naučíte, jak rozdělit buňky v aplikaci Excel pomocí následujících technik:

• Použití funkce Text na sloupec.
• Použití textových funkcí aplikace Excel.
• Použití funkce Flash Fill (k dispozici v 2013 a 2016).

Začněme!

Rozdělte buňky v aplikaci Excel pomocí textu na sloupec

Níže mám seznam názvů některých mých oblíbených fiktivních postav a chci rozdělit tato jména do samostatných buněk .:

Zde jsou kroky ke spli t tato jména na křestní jméno a příjmení:

• Vyberte buňky, ve kterých máte text, který chcete rozdělit (v tomto případě A2: A7).
• Klikněte na kartu Data
• Ve skupině „Data Tools“ klikněte na „Text to Columns“.
• v Průvodce převodem textu na sloupce:
  • Krok 1 ze 3 Průvodce textem na sloupce: Ujistěte se, že je vybrána možnost Oddělený (je to výchozí výběr). To vám umožní oddělit křestní jméno a příjmení na základě zadaného oddělovače (v tomto případě mezery).
  • Klikněte na Další.
  • Krok 2 ze 3 Průvodce převodem textu na sloupce: Jako oddělovač vyberte mezeru a zrušte výběr všech ostatních. Jak by váš výsledek vypadal, můžete vidět v dialogovém okně v části Náhled dat.
  • Klikněte na Další.
  • Krok Průvodce ze 3 na 3 Text na sloupce: V tomto kroku můžete určit formát dat a místo, kde chcete výsledek. Datový formát ponechám jako obecný, protože mám textová data rozdělená. Výchozí cíl je A2 a pokud v tom budete pokračovat, nahradí původní soubor dat. Pokud chcete zachovat původní data beze změny, vyberte jako cíl jinou buňku. V tomto případě je vybrána B2.
  • Klikněte na Dokončit.

Tím se okamžitě rozdělí text buňky na dva různé sloupce.

Poznámka:

• Funkce Text na sloupec rozděluje obsah buněk na základě oddělovače. I když to funguje dobře, pokud chcete oddělit křestní jméno a příjmení, v případě křestního, středního a příjmení se rozdělí na tři části.
• Výsledek, který získáte při používání Funkce převodu textu na sloupec je statická. To znamená, že pokud dojde k jakýmkoli změnám v původních datech, budete muset postup opakovat, abyste získali aktualizované výsledky.

Rozdělte buňky v aplikaci Excel pomocí textových funkcí

Textové funkce aplikace Excel jsou skvělé, když chcete rozdělit textové řetězce na kostky.

Zatímco funkce Text na sloupec poskytuje statický výsledek, výsledek, který získáte při používání funkcí, je dynamický a automaticky se aktualizuje, když změňte původní data.

Rozdělení jmen, která mají jméno a příjmení

Předpokládejme, že máte stejná data, která jsou uvedena níže:

Extrahování křestního jména

Chcete-li získat křestní jméno z tohoto seznamu, použijte následující vzorec:

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

Tento vzorec by zaznamenal první znak mezery a potom by vrátil veškerý text před tento znak mezery:

Tento vzorec používá funkci SEARCH k získání pozice znaku mezery. V případě Bruce Waynea je vesmírná postava na 6. pozici. Potom extrahuje všechny znaky nalevo od něj pomocí funkce LEFT.

Extrakce příjmení

Podobně pro získání příjmení použijte následující vzorec:

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

Tento vzorec používá funkci hledání k vyhledání pozice mezerníku pomocí funkce SEARCH. Potom toto číslo odečte od celkové délky jména (která je dána funkcí LEN). To udává počet znaků v příjmení.

Toto příjmení se poté extrahuje pomocí PRAVÉ funkce.

Poznámka: Tyto funkce nemusí fungovat dobře, pokud máte úvodní, koncové nebo dvojité mezery v názvech. Kliknutím sem se dozvíte, jak odstranit úvodní / koncové / dvojité mezery v aplikaci Excel.

Rozdělení jmen, která mají křestní jméno, druhé jméno a příjmení

Mohou nastat případy, kdy získejte kombinaci jmen, kde některá jména mají také prostřední jméno.

Vzorec je v takových případech trochu složitý.

Extrakce křestního jména

Jak získat křestní jméno:

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

Toto je stejný vzorec, který jsme použili, když nebylo žádné prostřední jméno. Jednoduše hledá první znak mezery a vrátí všechny znaky před mezeru.

Extrakce prostředního jména

Získání prostředního jména:

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

Funkce MID začíná od prvního znaku mezery a extrahuje prostřední jméno pomocí rozdílu mezi pozicí prvního a druhého znaku mezery.

V případech žádné prostřední jméno, funkce MID vrátí chybu. Chcete-li se vyhnout chybě, je zabalena do funkce IFERROR.

Extrakce příjmení

Chcete-li získat příjmení, použijte následující vzorec:

=IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=1,RIGHT(A2,LEN(A2)-SEARCH(" ",A2)),RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2)+1)))

Tento vzorec kontroluje, zda existuje prostřední jméno, nebo nikoli (počítáním počtu znaků mezery). Pokud existuje pouze 1 mezerový znak, jednoduše vrátí veškerý text napravo od mezerového znaku.

Pokud však existují 2, všimne si druhého mezerového znaku a vrátí počet znaků za druhá mezera.

Poznámka: Tento vzorec funguje dobře, pokud máte jména, která mají buď první jméno a příjmení, nebo křestní, prostřední a příjmení. Pokud však máte mix, ve kterém máte přípony nebo pozdravy, budete muset vzorce dále upravit.

Rozdělte buňky v aplikaci Excel pomocí funkce Flash Fill

Flash Fill je nová funkce zavedená v aplikaci Excel 2013.

Mohlo by to být opravdu užitečné, když máte vzor a chcete rychle extrahovat jeho část.

Vezměme si například křestní jméno a příjmení:

Flash fill funguje tak, že identifikuje vzory a replikuje je pro všechny ostatní buňky.

Takto můžete extrahovat křestní jméno ze seznamu pomocí funkce Flash Fill:

• V buňce B2 zadejte křestní jméno pro Bruce Wayna (tj. Bruce).
• Když je vybraná buňka, všimnete si malého čtverce na pravém konci výběru buňky. Poklepejte na něj. Tím se ve všech buňkách vyplní stejný název.
• Když jsou buňky vyplněny, vpravo dole se zobrazí ikona Možnosti automatického vyplňování. Klikněte na něj.
• Vyberte Flash Fill ze seznamu.
• Jak jakmile vyberete Flash Fill, všimnete si, že se všechny buňky aktualizují samy a nyní zobrazují křestní jméno pro každé jméno.

Jak funguje Flash Fill?

Flash Fill hledá vzory v datové sadě a replikuje je.

Flash Fill je překvapivě chytrá funkce a ve většině případů funguje podle očekávání. Ale v některých případech také selže.

Například pokud mám seznam jmen, který má kombinaci jmen, přičemž některé mají prostřední jméno a některé ne.

Pokud v takovém případě extrahuji prostřední jméno, Flash Fill chybně vrátí příjmení, pokud není k dispozici křestní jméno.

Abych byl upřímný, stále je to dobrá aproximace trendu. To však není to, co jsem chtěl.

Ale stále je to dostatečně dobrý nástroj, který vám pomůže udržet si arzenál a použít jej, kdykoli to bude potřeba.

Zde je další příklad, kde Flash Fill funguje skvěle.

Mám sadu adres, ze kterých chci rychle extrahovat město.

Chcete-li rychle získat město, zadejte název města pro první adresu (v tomto příkladu zadejte Londýn do buňky B2) a pomocí automatického vyplňování vyplňte všechny buňky. Nyní použijte Flash Fill a z každé adresy vám okamžitě dá název města.

Podobně můžete adresu rozdělit a extrahovat libovolnou část adresy.

Pamatujte, že toto bude potřebovat, aby adresa byla homogenní datová sada se stejným oddělovačem (v tomto případě čárkou).

V případě, že zkusíte použít Flash Fill, když není žádný vzor, zobrazí se vám chyba, jak je znázorněno níže:

V tomto kurzu jsem popsal tři různé způsoby rozdělení buněk v aplikaci Excel do více sloupců (pomocí Text to Sloupce, vzorce a Flash Fill)

Doufám, že vám tento návod pro Excel byl užitečný.

Také by se vám mohly líbit následující výukové programy pro Excel:

• Jak rychle kombinovat buňky v aplikaci Excel.
• Jak extrahovat podřetězec v aplikaci Excel pomocí vzorců.
• Jak počítat buňky obsahující textové řetězce.
• Extrahovat uživatelská jména z E-mail s ID v aplikaci Excel.
• Jak rozdělit více řádků v buňce na samostatné buňky / sloupce.