Jak rozdělit buňky v aplikaci Excel (rozdělit do více sloupců)

Mohou nastat situace, kdy musíte buňky v Excelu rozdělit. Může to být, když data získáte z databáze nebo je zkopírujete z internetu nebo je získáte od kolegy.

Jednoduchý příklad, kdy je třeba rozdělit buňky v Excelu, je, když máte celá jména a chcete je rozdělit na křestní jméno a příjmení.

Nebo dostanete adresu ‘a chcete adresu rozdělit, abyste mohli města nebo kód PIN analyzovat samostatně.

Jak rozdělit buňky v aplikaci Excel

V tomto kurzu se dozvíte, jak rozdělit buňky v aplikaci Excel pomocí následujících technik:

• Použití funkce převodu textu na sloupec.
• Pomocí textových funkcí aplikace Excel.
• Použití Flash Fill (k dispozici v letech 2013 a 2016).

Pojďme začít!

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

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

Zde jsou kroky k rozdělení těchto jmen 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ě „Datové nástroje“ klikněte na „Text do sloupců“.
• V průvodci Převod textu na sloupce:
  • Krok 1 ze 3 Průvodce převáděním textu na sloupce: Ujistěte se, že je vybrána možnost Oddělovač (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ě mezerník).
  • Klikněte na Další.
  • Krok 2 ze 3 Průvodce převáděním textu na sloupce: Jako oddělovač vyberte mezeru a zrušte výběr všech ostatních položek. Jak bude váš výsledek vypadat, můžete vidět v části Náhled dat v dialogovém okně.
  • Klikněte na Další.
  • Krok 3 ze 3 Průvodce převáděním textu na sloupce: V tomto kroku můžete určit formát dat a kde chcete výsledek. Formát dat ponechám jako obecný, protože mám textová data k rozdělení. Výchozí cíl je A2 a pokud v tom budete pokračovat, nahradí původní sadu dat. Pokud chcete zachovat původní data nedotčená, vyberte jako cíl jinou buňku. V tomto případě je vybrána B2.
  • Klikněte na Dokončit.

To okamžitě rozdělí text buňky do dvou různých sloupců.

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 jména, prostředního jména a příjmení jej rozdělí na tři části.
• Výsledek, který získáte z použití funkce Text na sloupec, je statický. To znamená, že pokud dojde k nějakým změnám v původních datech, budete muset proces zopakovat, abyste získali aktualizované výsledky.

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

Funkce textu v Excelu jsou skvělé, když chcete krájet a krájet textové řetězce.

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

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

Předpokládejme, že máte stejná data jako 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:

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

Tento vzorec by našel první znak mezery a poté 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 Wayna je vesmírná postava na 6. pozici. Poté extrahuje všechny znaky nalevo od něj pomocí funkce VLEVO.

Extrahování příjmení

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

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

Tento vzorec používá funkci vyhledávání k nalezení polohy mezerníku pomocí funkce HLEDAT. Poté čí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í je poté extrahováno pomocí funkce DOPRAVA.

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

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

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

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

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

Získání křestního jména:

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

Je to stejný vzorec, jaký jsme použili, když neexistovalo žádné prostřední jméno. Jednoduše vyhledá první znak mezery a vrátí všechny znaky před mezeru.

Extrahování prostředního jména

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

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

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

V případě, že neexistuje žádné prostřední jméno, funkce MID vrátí chybu. Aby se předešlo chybě, je zabalena do funkce IFERROR.

Extrahování 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 ne (počítáním počtu mezer). Pokud existuje pouze 1 znak mezery, jednoduše vrátí veškerý text napravo od znaku mezery.

Ale pokud jsou 2, pak si všimne druhého znaku mezery a vrátí počet znaků za druhým mezerou.

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

Rozdělte buňky v aplikaci Excel pomocí 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 data křestního jména a příjmení:

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

Zde je návod, jak můžete ze jména pomocí Flash Fill extrahovat křestní jméno:

• Do buňky B2 zadejte křestní jméno Bruce Wayne (tj. Bruce).
• Když je buňka vybrána, všimnete si malého čtverečku 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 uvidíte ikonu Možnosti automatického vyplňování. Klikněte na to.
• Ze seznamu vyberte Flash Fill.
• Jakmile vyberete Flash Fill, všimnete si, že se všechny buňky samy aktualizují a nyní u každého jména zobrazí křestní jméno.

Jak Flash Fill funguje?

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

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é selhává.

Mám -li například seznam jmen, který obsahuje kombinaci jmen, přičemž některé mají prostřední jméno a některé nikoli.

Pokud v takovém případě rozbalím prostřední jméno, Flash Fill chybně vrátí příjmení v případě, že neexistuje křestní jméno.

Abych byl upřímný, je to stále dobré přiblížení trendu. Nicméně to není to, co jsem chtěl.

Ale stále je to dost dobrý nástroj, který můžete udržet ve svém arzenálu a použít jej, kdykoli to bude potřeba.

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

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

Chcete -li město rychle získat, 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ě poskytne název města.

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

Všimněte si toho, že by adresa potřebovala být homogenní datovou sadou se stejným oddělovačem (v tomto případě čárkou).

V případě, že se pokusíte použít Flash Fill, když neexistuje žádný vzor, ​​zobrazí se vám chyba, jak je uvedeno níže:

V tomto kurzu jsem pokryl tři různé způsoby rozdělení buněk v aplikaci Excel do více sloupců (pomocí textu na sloupce, vzorců a Flash Fill)

Doufám, že jste tento návod k Excelu považovali za užitečný.