V Exceli je veľmi jednoduché rozdeliť prvé, stredné a priezvisko, ktoré sa zobrazujú v rovnakom stĺpci, do samostatných stĺpcov rôznymi spôsobmi.
Predpokladajme, že ste dostali zoznam kontaktov so všetkými menami uvedenými celými menami v jednom stĺpci a pravdepodobne budete musieť oddeliť krstné, stredné a priezvisko a rozdeliť ich do samostatných stĺpcov. Na oddelenie názvov môžete použiť niekoľko rôznych spôsobov – pomocou funkcie Text do stĺpcov, výplne Flash a vzorcov.
V Exceli je naozaj jednoduché rozdeliť názvy z jedného stĺpca do dvoch alebo viacerých stĺpcov. V tomto článku vám ukážeme, ako rozdeliť názvy do rôznych stĺpcov v programe Excel rôznymi spôsobmi.
Ako rozdeliť mená v Exceli
Existujú tri jednoduché spôsoby, ako rozdeliť mená v Exceli. V závislosti od štruktúry údajov a od toho, či chcete, aby boli názvy rozdelenia statické alebo dynamické, vyberte jednu z nasledujúcich metód:
- Rozdeľte názvy pomocou funkcie Text to Columns
- Oddeľte názvy pomocou vzorcov
- Názvy oddeľte pomocou Flash Fill
Oddeľte názvy pomocou Sprievodcu textom a stĺpcom
Sprievodca textom do stĺpca je jedným z najbežnejších spôsobov oddelenia mien a priezvisk v Exceli, ako aj stredných mien. Uistite sa, že vedľa mien, ktoré sa chystáte rozdeliť, sú prázdne stĺpce, pretože celé mená budú rozdelené do samostatných stĺpcov.
Napríklad máte nižšie uvedenú množinu údajov s úplnými menami a chcete rozdeliť/oddeliť mená a priezviská a uložiť ich do samostatných buniek.
Najprv zvýraznite stĺpec plných mien, ktoré chcete oddeliť. Potom prejdite na kartu „Údaje“ a kliknite na možnosť „Text do stĺpcov“ v časti „Nástroje údajov“.
Otvorí sa Sprievodca prevodom textu na stĺpce. V prvom kroku sprievodcu vyberte možnosť „Oddelené“ a kliknite na tlačidlo „Ďalej“.
V kroku 2 z 3 Sprievodcu prevodom textu na stĺpce vyberte oddeľovač, ktorý oddeľuje vaše údaje, odstráňte všetky ostatné značky začiarknutia a kliknite na tlačidlo „Ďalej“. V našom prípade „medzera“ oddeľuje meno a priezvisko, preto zvolíme tento oddeľovač.
Časť Ukážka údajov nižšie v okne ukazuje, ako sa analyzujú vaše mená.
V kroku 3 z 3 vyberiete formát údajov a cieľ a kliknete na „Dokončiť“.
Za normálnych okolností bude predvolené „Všeobecné“ fungovať dobre pre väčšinu typov údajov. V poli „Cieľ“ zadajte miesto, kde chcete zobraziť výstup. Musíte zadať adresu prvej bunky v stĺpci, kde chcete výsledky (v našom prípade B2).
Pamätajte, že ak neurčíte cieľovú bunku, sprievodca prepíše pôvodné údaje, takže vyberte prázdny stĺpec.
Po kliknutí na tlačidlo „Dokončiť“ sa celé mená okamžite rozdelia do dvoch samostatných stĺpcov (Krstné meno a Priezvisko).
Ak máte meno, stredné a priezvisko, postupujte podľa rovnakých krokov a vaše mená sa rozdelia do troch stĺpcov namiesto dvoch.
Poznámka: Výsledok tejto metódy je statický. To znamená, že ak zmeníte pôvodný názov, musíte to urobiť znova, aby ste názvy rozdelili.
Delené mená oddelené čiarkami
Ak sú krstné mená a priezviská oddelené čiarkami, odstráňte čiarku a oddeľte mená a priezviská podľa nasledujúcich krokov.
V nasledujúcom príklade sú mená naformátované v opačnom formáte (Priezvisko, Meno), kde je priezvisko na prvom mieste, za ním čiarka a potom krstné meno.
Vyberte mená a prejdite na Údaje –> Text do stĺpca. V kroku 1 vyberte „Oddeľovač“ a kliknite na „Ďalej“. V kroku 2 v časti Oddeľovače začiarknite ako oddeľovač „Čiarka“ (,), pretože vaše mená sú oddelené čiarkou.
V poslednom kroku si vyberiete formát údajov ako „Všeobecné“, špecifikujete cieľ a kliknete na „Dokončiť“.
Teraz budete mať mená v samostatných stĺpcoch.
Oddeľte názvy pomocou vzorcov
Sprievodca Text to Columns umožňuje rýchlo a jednoducho oddeliť názvy. Ak však chcete upraviť pôvodné názvy a chcete dynamickú metódu, ktorá sa bude automaticky aktualizovať pri každej zmene názvov, potom je rozdelenie názvov pomocou vzorcov tou správnou voľbou. Na oddelenie mien môžete použiť funkcie LEFT, RIGHT, MID, LEN a SEARCH alebo FIND.
Oddeľte meno a priezvisko v Exceli pomocou vzorcov
Získajte krstné meno
Povedzme, že máte nižšie uvedený súbor údajov a chcete oddeliť krstné meno do samostatnej bunky. Ak chcete získať krstné meno, musíte skombinovať funkciu FIND a LEFT do jedného vzorca.
Na získanie krstného mena použite nasledujúci vzorec:
=LEFT(A2,FIND(" ",A2)-1)Tento vzorec používa funkciu NÁJSŤ na nájdenie pozície znaku medzery („ ““) medzi menom a priezviskom a odčítaním 1 vylúči samotnú medzeru. Toto číslo sa potom odošle funkcii LEFT, ktorá použije toto číslo pozície na extrahovanie celého textu pred ním. Namiesto funkcie HĽADAŤ môžete použiť aj funkciu HĽADAŤ.
Po zadaní vzorca do prázdnej bunky (B2) potiahnite rukoväť výplne nadol do iných buniek, aby ste použili tento vzorec, a všetky mená budú rozdelené do stĺpca B, ako je znázornené nižšie:
Funkciu HĽADAŤ a NÁJSŤ môžete buď vložiť do funkcie LEFT, aby ste získali krstné meno. Hlavný rozdiel medzi týmito dvoma funkciami je v tom, že HĽADAŤ rozlišuje malé a veľké písmená, zatiaľ čo HĽADAŤ nerozlišuje veľké a malé písmená.
Ak máte namiesto medzery medzi menom a priezviskom čiarku (,), použite čiarku ako prvý argument vo funkcii FIND:
=LEFT(A2,FIND(",",A2)-1)Získajte priezvisko
Ak teraz potrebujete extrahovať priezvisko, použite funkciu RIGHT. Nasledujúci vzorec extrahuje priezvisko z rovnakého súboru údajov:
=RIGHT(A2,DĹŽKA(A2)-NÁJSŤ(" ",A2))Vzorec najprv nájde pozíciu znaku medzery, odpočíta toto číslo od celkovej dĺžky reťazca (ktorú vráti funkcia LEN) a toto číslo sa potom dodá funkcii RIGHT na extrahovanie toľko znakov z konca reťazca. (názov).
Oddeľte meno, stred a priezvisko v Exceli pomocou vzorcov
Rozdelenie mien, ktoré obsahujú stredné meno, vyžaduje rôzne vzorce v závislosti od formátu mena, ktorý máte.
Ak chcete získať krstné meno ak máte stredné meno alebo iniciály v strede, použite rovnaký vzorec NÁJSŤ VĽAVO, ktorý už poznáte.
Získajte priezvisko
Vyššie uvedený vzorec SPRÁVNE NÁJSŤ funguje dobre, keď je k dispozícii iba krstné meno a priezvisko, nebude príliš užitočný v prípade, že vaše pôvodné mená obsahujú stredné meno alebo iniciály stredného mena. Je to preto, že ste v názve nezohľadnili dve medzery.
Ak chcete získať priezvisko, keď máte aj druhé meno, použite tento vzorec:
=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1))Ak chcete extrahovať priezvisko, najprv určte polohu druhého znaku medzery pomocou vnorených funkcií SEARCH, pridajte 1 k SEARCH(” “,A2,1), aby ste spustili extrakciu s ďalším znakom. Potom odpočítajte pozíciu 2. medzery od celkovej dĺžky reťazca a získajte dĺžku priezviska ako výsledné číslo. Potom zadajte toto výsledné číslo funkcii RIGHT, aby ste extrahovali počet znakov z konca reťazca.
Získajte stredné meno
Funkcia MID používa tri argumenty, prvý argument určuje text alebo adresu bunky, druhý určuje počiatočnú pozíciu a posledný argument hovorí o počte znakov na extrahovanie prostredného mena z tejto pozície.
Syntax:
=MID(text, počiatočné_číslo, počet_znakov)Ak chcete získať stredné meno, zadajte tento vzorec do prázdnej bunky:
=MID(A2,HĽADAŤ(" ",A2)+1,HĽADAŤ(" ",A2,HĽADAŤ(" ",A2)+1)-HĽADAŤ(" ",A2)-1)Pozrime sa, ako tento zložitý vzorec funguje:
Ak chcete extrahovať stredné meno alebo iniciály stredného mena, musíte určiť polohu oboch medzier v celom mene. Ak chcete zistiť polohu prvého znaku medzery, zadajte toto HĽADAŤ(" ",A2) funkciu v argumente „start_num“ a pridaním 1 spustíte extrakciu od ďalšieho znaku.
Potom, aby ste zistili dĺžku stredného mena, zadajte toto HĽADAŤ(" ",A2,HĽADAŤ(" ",A2)+1)-HĽADAŤ(" ",A2)-1 vnorená funkcia v argumente ‘num_chars’, ktorá odpočíta pozíciu 1. medzery od pozície 2. medzery a odpočíta 1 od výsledku, aby sa odstránila medzera na konci. Konečný výsledok vám povie, koľko znakov sa má extrahovať.
Teraz funkcia MID so začiatočnou pozíciou stredného mena a číselnými znakmi na extrakciu oddeľuje stredné meno od celého mena (A2).
Oddeľte názvy v Exceli pomocou Flash Fill
Flash výplň sa väčšinou používa na automatické vypĺňanie údajov konkrétneho vzoru. Môže sa použiť aj na rozdelenie mien. Je k dispozícii iba v Exceli 2013, 2016, 2019 a 365.
Povedzme, že máte nižšie uvedený súbor údajov a chcete z celých mien získať iba krstné mená.
Do bunky susediacej s pôvodným názvom zadajte krstné meno. V tomto prípade napíšte „Steve“ do bunky B2.
Potom začnite písať krstné meno do druhej bunky stĺpca. Ak počas písania Excel rozpozná vzor, Flash Fill vám automaticky zobrazí zoznam mien v iných bunkách (sivou farbou).
Keď uvidíte zoznam mien sivou farbou a ak sú tieto mená správne, stačí stlačiť kláves „Enter“ a Flash Fill automaticky vyplní zvyšok stĺpca krstnými menami.
Rovnakým postupom oddeľte priezviská do samostatného stĺpca.
Výsledok:
Flash Fill funguje tak, že deteguje vzor v týchto údajoch a nasleduje tento vzor, pričom vám poskytuje upravené údaje. Najprv, keď zadáte krstné meno do prvej bunky, Flash Fill nerozpozná vzor. Keď však znova začnete písať krstné meno do druhej bunky, Flash Fill rozpozná vzor a zobrazí návrh na rozdelenie krstných mien. Potom stačí stlačiť kláves „Enter“.
Vo všeobecnosti je funkcia Flash Fill v predvolenom nastavení povolená. Ak to vo vašom Exceli nefunguje, po zadaní krstného mena do prvej bunky môžete jednoducho vybrať druhú bunku a kliknúť na tlačidlo „Flash Fill“ v skupine Data Tools na karte „Data“.
Prípadne môžete tiež stlačiť „Ctrl“ + „E“, aby ste dosiahli rovnaké výsledky.
Teraz budú ostatné bunky vyplnené krstnými menami.
Niekedy sa môže stať, že návrh vzoru nevidíte sivou farbou, v takom prípade môžete použiť rukoväť výplne na získanie výsledku Flash Fill.
Najprv zadajte názvy ručne do dvoch buniek a vyberte obe tieto bunky. Potom umiestnite kurzor myši do pravého dolného rohu výberu. Všimnete si, že kurzor sa zmení z malého zeleného štvorca (ikona výplne) na ikonu plus.
Potom dvakrát kliknite na ikonu plus. Tým sa vyplní zvyšok buniek. V tomto bode sú výsledky nesprávne, uvidíte, že obe mená sa znova a znova opakujú. Potom v pravom dolnom rohu výsledných údajov uvidíte malú ikonu automatického dopĺňania, ako je znázornené nižšie. Kliknite na túto ikonu „Automatické dopĺňanie“ a vyberte „Výplň Flash“.
Tým by sa vyplnili krstné mená do všetkých buniek:
Odstráňte stredné meno
Môžete tiež použiť nástroj Flash Fill na odstránenie stredného mena z celého mena.
Povedzme napríklad, že máte nižšie uvedený súbor údajov a chcete získať iba krstné meno a priezvisko bez stredného mena alebo iniciály stredného mena.
Ak chcete získať mená bez stredného mena alebo iniciály v strede, manuálne napíšte „Lord Stark“ do susednej bunky. Potom do druhej susednej bunky napíšte „Daenerys Targaryen“. Počas písania rozpozná Flash Fill vzor a zobrazí vám zoznam mien bez stredných mien (sivou).
Ak je návrh správny, stlačte kláves „Enter“ a Flash Fill automaticky vyplní zvyšok buniek názvami bez stredného mena.
Ak chcete získať iba stredné mená bez mena a priezviska, zadajte stredné meno do prvých dvoch buniek a použite nástroj Flash Fill na získanie stredných mien zo všetkých celých mien v stĺpci.
Tento tutoriál ukazuje, ako oddeliť názvy pri manipulácii s textovými údajmi. Tieto metódy môžu byť užitočné aj pri práci s inými formami údajov, ako sú adresy, názvy produktov, značky atď.