Ako rozdeliť meno a priezvisko alebo ako spojiť názov ulice a jej číslo, ak máte tieto údaje v rôznych bunkách v Exeli. Pozrite si návod, ako si v Exceli s týmito, ale aj ďalšími, problémami ľahko poradiť.
Stiahnete si z internetu tabuľky, kolega vám pošle podklady alebo exportujete zostavu zo softvéru. Niekde je medzera navyše, čísla uložené ako text, čísla či desatinné miesta oddelené chybnými znakmi, nelogicky pospájané textové stĺpce atď. Často sa stane, že sa k vám dostanú dáta v inej podobe ako ich k práci potrebujete.
Aké funkcie v Exceli sa používajú na spájanie a rozdeľovanie dát (textu) v Exceli
Na spájanie textu je v Exceli k dispozícii funkcia CONCATENATE, avšak rovnaký výsledok dosiahneme aj použitím znaku &. Ďalšie užitočné funkcie sú LEFT, RIGHT, MID, FIND, SEARCH, LEN alebo SUBSTITUTE.
Funkcia LEFT vytiahne zadaný počet znakov zľava. Obdobne funkcia RIGHT vytiahne zadaný počet znakov sprava. Funkcia MID je zaujímavá v tom, že tiež vytiahne zo zadaného textu (prípadne aj čísla) zadaný počet znakov (zľava doprava), avšak musíte zadať začiatočnú pozíciu.
Funkcie FIND a SEARCH sú si podobné. Nájdu v texte alebo v čísle pozíciu (poradové číslo), kde sa zadaný text alebo číslo prvýkrát vyskytuje. Funkcia FIND naviac rozlišuje veľké a malé písmená.
Príklad: Rodné číslo býva často v tvare RRMMDD/CCCC. Ak by sme funkciou FIND alebo SEARCH hľadali v rodnom čísle znak „/“, dostali by sme hodnotu 7.
Funkcia LEN spočíta, aký dlhý je text alebo číslo, resp. koľko má znakov. Funkcia SUBSTITUTE, ako už z jej názvu je zrejmé, nahradí zadaný text alebo číslo iným textom alebo číslom.
Niektoré funkcie použité samostatne nemajú veľké využitie. Sú však šikovným pomocníkom, ak ich správne skombinujeme. Pozrite si na príklade, ako možno využiť funkcie na rozdeľovanie a spájanie textu (dát).
Príklad: Rozdeľovanie textu v Exceli
V nasledujúcej tabuľke máme v prvom stĺpci meno a priezvisko. V novej tabuľke chceme mať v samostatnom stĺpci meno a v samostatnom stĺpci priezvisko. Použijeme funkcie LEFT, LEN, MID a FIND.
Oddelenie mena a priezviska je založené na rovnakom princípe, aký by sme uplatnili v prípade, keby sme meno a priezvisko prepisovali ručne.
Pokiaľ ide o meno, pri jeho ručnom prepísaní sa pozrieme, kde končí. Keďže meno končí pred medzerou, prepísali by sme všetky znaky zľava až po medzeru (bez tejto medzery). Na rovnakom princípe je založená aj funkcia =LEFT(A2;FIND(“ “;A2)-1).
Funkcia LEFT vytiahne z daného textu zadaný počet znakov zľava. Keďže meno môže mať rôzny počet znakov, nemôžeme zadať fixný počet znakov. Meno Richard má 7 znakov, Maria 5 znakov. Musíme použiť inú funkciu, ktorá potrebný počet znakov pre každý prípad vypočíta. Funkcia FIND nájde v bunke A2 medzeru, ktorá je ôsmym znakom zľava. Medzera však nie je súčasťou mena, preto ju do počtu znakov zahrnúť nechceme a tak vždy odpočítame 1 (tento údaj môžeme zadať fixne).
Priezvisko v našom príklade začína za medzerou. Pri ručnom prepísaní priezviska by sme našli v texte medzeru a prepísali všetky znaky nasledujúce za ňou. Nakoľko aj priezviská môžu byť rôzne dlhé, je potrebné aj pre tento prípad naformulovať univerzálnu funkciu. S rovnakou logikou postupuje aj funkcia =MID(A2;FIND(“ “;A2)+1;LEN(A2)-FIND(“ “;A2)). Na prvý pohľad ide o komplikovaný zápis, tak si ho postupne rozoberme.
Funkcia MID vytiahne z daného textu, napr. Richard Diaz, zadaný počet znakov zľava, pričom začína na určenej pozícii. Má tri argumenty:
- Text – text, z ktorého chceme vytiahnuť určitý počet znakov. V našom prípade ide o text v bunke A2 „Richard Diaz“;
- Počiatočná pozícia – počiatočná pozícia prvého znaku textu, ktorý chceme vytiahnuť. V prípade mena a priezviska „Richard Diaz“ priezvisko začína písmenom D. Chceme zistiť poradové číslo písmena D v texte „Richard Diaz“. Na tento účel bude slúžiť funkcia FIND(“ “;A2)+1;
- Počet znakov – počet znakov, ktoré chceme vytiahnuť. V našom prípade ide o priezvisko „Diaz“, čiže chceme vytiahnuť 4 znaky. Na tento účel bude slúžiť kombinácia funkcií LEN(A2)-FIND(“ “;A2).
Vieme, že v našom prípade priezvisko začína prvým znakom za medzerou. Potrebujeme zistiť poradové číslo medzery, od ktorého sa odpichneme. Počiatočnú pozíciu (druhý argument funkcie MID) priezviska nám udáva funkcia FIND(“ “;A2)+1, ktorá zistí poradové číslo medzery a pripočíta k nemu 1. V prípade osoby „Richard Diaz“ priezvisko začína na deviatom znaku.
Nasledujúcim krokom je zistenie počtu znakov priezviska. Funkcia LEN(A2) zistí celkový počet znakov mena a priezviska vrátane medzery (12 znakov). Od celkového počtu je potrebné odpočítať počet znakov od začiatku až po medzeru (vrátane nej), t. j. odpočítame počet znakov mena a medzeru. Použijeme opäť funkciu FIND(“ “;A2), ktorej výsledkom je číslo 8. Priezvisko „Diaz“ má teda 4 znaky (12 – 8).
Poznámka: Priezvisko by sme mohli získať aj funkciou =RIGHT(A2;LEN(A2)-SEARCH(" ";A2)). V Exceli existuje v mnohých prípadoch niekoľko riešení, prostredníctvom ktorých sa vieme dopracovať k rovnakému výsledku.
Prepísanie mena a priezviska môže byť rýchlejšie v porovnaní s vytvorením vzorca v prípade, ak ide o niekoľko jednotiek položiek. Ak máme položiek niekoľko desiatok, stoviek alebo tisícok, je rozumnejšie venovať čas vytvoreniu funkčného vzorca, čím môžeme ušetriť čas v násobkoch. Pol dňa prepisovania tabuliek môžete zredukovať aj na pol hodinu. Naviac eliminujete riziko preklepov.
Príklad: Spájanie textu z rôznych buniek v Exceli
Ďalej sa budeme zaoberať spájaním textu pomocou funkcie CONCATENATE a pomocou znaku &. Ako príklad použijeme vytvorenie súvislej adresy z dvoch stĺpcov, v ktorých máme názov ulice a príslušné číslo.
Poznámka: Číslo je uložené v textovom formáte. Keď bude číslo začínať nulou, Excel ju v tomto prípade automaticky neodstráni.
Funkciou =CONNCATENATE(A2;“ “;B2) sme spojili 3 textové reťazce:
- názov ulice, ktorý sa nachádza v bunke A2,
- medzeru, ktorá je fixne zadaná medzi úvodzovkami,
- číslo ulice, ktoré sa nachádza v bunke B2.
Rovnaký postup sme použili aj v druhom prípade, pričom na spojenie troch textových reťazcov sme použili operátor na reťazenie textu &.
Zdroj databáz: Vygenerované prostredníctvom https://www.mockaroo.com.
