Potřebovali jste text v jedné buňce rozdělit do několika buněk? Klasickým příkladem může být příjmení a jméno nebo celá adresa a nebo datum '2008/02/13' uvedené jako jeden text v jedné buňce. Může se jednat o seznamy ve stovkách nebo až tisících řádků. Vytvoříme malý parser pomocí textových funkcí NAJÍT, ZLEVA, DÉLKA a ČÁST.Pro názorný příklad použiji příjmení a jméno, které je uvedeno v jedné buňce. Našim společným úkolem bude do jedné buňky oddělit příjmení a do druhé jméno za použíti textových funkcí Excelu. Použiji níže uvedené textové funkce.
| Funkce NAJÍT |
| Syntaxe: NAJÍT(co, kde, start) |
| Funkce NAJÍT vyhledá jeden textový řetězec (co) uvnitř jiného (kde) a vrátí číslo pozice prvního znaku nalezeného podřetězce co vzhledem k prvnímu znaku v řetězci kde. |
| Co je text, který chcete vyhledat. |
| Kde je řetězec, který bude prohledán. |
| Start je pozice znaku v řetězci kde, od kterého se má začít prohledávat. První znak v řetězci kde je na pozici 1. Pokud je argument start vynechán, začne se prohledávat od pozice 1. |
| Funkce ZLEVA |
| Syntaxe: ZLEVA(text, znaky) |
| Funkce ZLEVA vrátí první znak nebo znaky v textovém řetězci na základě zadaného počtu znaků. |
| Text je textový řetězec, ze kterého se budou vybírat znaky. |
| Znaky určuje počet znaků vrácených funkcí ZLEVA. |
| Funkce DÉLKA |
| Syntaxe: DÉLKA(text) |
| Funkce DÉLKA vrátí počet znaků textového řetězce. |
| Text je text, jehož délku chcete zjistit. Mezery jsou považovány za znaky. |
| Funkce ČÁST |
| Syntaxe: ČÁST(text, start, počet_znaků) |
| Funkce ČÁST vrátí zadaný počet znaků z textového řetězce od zadané pozice na základě zadaného počtu znaků. Text je textový řetězec obsahující znaky, které chcete extrahovat. |
| Start je pozice prvního znaku; pro první znak v řetězci se hodnota argumentu start rovná hodnotě 1 atd. |
| Počet_znaků určuje počet znaků vrácených z textu funkcí ČÁST. |
Analýza textu a volba postupu.
Všechna příjmení a jména jsou oddělená mezerou. Proto mezera a její pozice bude v tomto případě klíčová. Nejdříve získám z textu příjmení kombinací funkce NAJÍT a ZLEVA a pak jméno kombinací funkcí DÉLKA, NAJÍT a ČÁST.
1. Získání příjmení
Protože příjmení začíná jako první zleva, použiji funkci ZLEVA(text, znaky). Neznám délku příjmení pro druhý parametr funkce. Proto jako druhý parametr využiji pozici mezery a odečtu 1. Pozici mezery zjistím funkcí NAJÍT. Kombinace funkcí v buňce B1 bude pak vypadat takto: =ZLEVA(A1;(NAJÍT(" ";A1;1))-1)
2. Získání jména
Jméno získám funkcí ČÁST, kde jako parametr Start použiji pozici mezery + 1, takže zde zadám funkci NAJÍT. Pro parametr Počet_znaků využiji funkce DÉLKA(A1). Kombinace funkcí v buňce C1 bude pak vypadat takto: =ČÁST(A2;(NAJÍT(" ";A2;1))+1;DÉLKA(A2))
Teď už jen stačí vzorce zkopírovat pod sebe podle délky vašeho seznamu jmen. Ukázkový příklad ke stažení: Nejste přihlášen(a).
Formát pro tisk
Facebook
Google +
Linkuj
QR kód
E-mailem
PDF
\n
RSS nejnovější články
Komentáře
Rev.045 Lesní společnost Náchod, s.r.o Chatička Zelenka elektro 1.6.2015
tex číslo(celkem 6 znaků)vymazat firma (2 až 4 slova) je možné oddělit třeba(-)popis 2až4 slova a datum.
potřebuji rozdělit do 3 buněk pomocí vzorce.
děkujicitovat
Do A1:
Rev.045 Lesní společnost Náchod, s.r.o-Chatička Zelenka elektro 1.6.2015
Do B1:
=LEFT(A1;FIND(" ";A1)-1)
=ZLEVA(A1;NAJÍT(" ";A1)-1)
Do C1:
=MID(A1;LEN(B1)+2;FIND("-";A1)-LEN(B1)-2)
=ČÁST(A1;DÉLKA(B1)+2;NAJÍT("-";A1)-DÉLKA(B1)-2)
Do D1:
=MID(A1;FIND("-";A1)+1;LEN(A1)-(FIND("-";A1)+1+LEN(E1)))
=ČÁST(A1;NAJÍT("-";A1)+1;DÉLKA(A1)-(NAJÍT("-";A1)+1+DÉLKA(E1)))
Do E1:
=TRIM(RIGHT(SUBSTITUTE(A1;" ";REPT(" ";LEN(A1)));LEN(A1)))
=PROČISTIT(ZPRAVA(DOSADIT(A1;" ";OPAKOVAT(" ";DÉLKA(A1)));DÉLKA(A1)))
PS: Keby ste písal čiarky vo vetách, nemusel som to čítať toľko krát
Patrikcitovat
patrikpatrik napsal/a:
Zapojíme funkci DOSADIT, kterou si budeme před vlasním vykopírováním měnit řetězec pro vyhledávání. Budeme nahrazovat hledaný řetězec znakem “#” pro snadnější vyhledávání. Příklady:
DOSADIT(<buňka>;”:”;”#”;1) nám převede řetězec "Loc: 50°4'48.511"N, 15°8'57.777"E" na tento "Loc# 50°4'48.511"N, 15°8'57.777"E"
1 určuje první výskyt znaku :
První číslo tak dostaneme touto funkcí:
Zadané číslo máme v buňce A3,…
=ČÁST(A3;NAJÍT("#";DOSADIT(A3;" ";"#";1);1)+1;NAJÍT("#";DOSADIT(A3;"°";"#";1);1)-NAJÍT("#";DOSADIT(A3;" ";"#";1);1)-1) - výsledkem by měla být hodnota 50.
Druhé číslo takto
=ČÁST(A3;NAJÍT("#";DOSADIT(A3;"°";"#";1);1)+1;NAJÍT("#";DOSADIT(A3;"'";"#";1);1)-NAJÍT("#";DOSADIT(A3;"°";"#";1);1)-1) - výsledkem by měla být hodnota 4
Takto pokračujeme dál až v případě separace 15°8’57. Nahradíme 1 ve funkci DOSADIT 2.
=ČÁST(A3;NAJÍT("#";DOSADIT(A3;" ";"#";2);1)+1;NAJÍT("#";DOSADIT(A3;"°";"#";2);1)-NAJÍT("#";DOSADIT(A3;" ";"#";2);1)-1) – výsledkem by měla být hodnota 15
=ČÁST(A3;NAJÍT("#";DOSADIT(A3;"°";"#";2);1)+1;NAJÍT("#";DOS
ADIT(A3;"'";"#";2);1)-NAJÍT("#";DOSADIT(A3;"°";"#";2);1)-1) – výsledkem by měla být hodnota 8
Postupně tak měníme pouze hodnoty v dosadit za “ “(mezera), “°”, “’”, “.”, ””” – zde pozor musíme to zadat “”””!citovat