Nacházíte se: WALL.czExcel návod › Parser textu - funkce NAJÍT, ZLEVA, DÉLKA a ČÁST
Kategorie: Excel návod

Parser textu - funkce NAJÍT, ZLEVA, DÉLKA a ČÁST

ExcelPotř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)

Parser textu

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))

Parser textu

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).

print Formát pro tisk

Sdílet článek:

Komentáře rss

Přidat komentář >

Strana:  1 2   další »
, Funkce right odpovědět
avatar
Dobrý den,

mohu se zeptat, proč pro Váš příklad - získání jména - nelze využít funkce "right"? Myslela bych, že když pro vyhledání příjmení mohu využít funkci "left", protože příjmení začíná první zleva, pro vyhledání příjmení by mohla platit funkce "right", když ho najdu zleva. Děkuji za odpověďcitovat
odpověděl(a)
elninoslov
Áno, dá sa to urobiť aj inak, napr. takto:
=RIGHT(A4;LEN(A4)-FIND(" ";A4))
=ZPRAVA(A4;DÉLKA(A4)-NAJÍT(" ";A4))

alebo
=REPLACE(A4;1;FIND(" ";A4);"")
=NAHRADIT(A4;1;NAJÍT(" ";A4);"")
citovat
, vrácení řetězce odpovědět
avatar
Nedaří se mi najít řešení pro vrácení řetězce ohraničeného znaky. mám buňky a různě dlouhém textu ve kterém se nachází hmotnost ve formátu xxkg nebo xxxg případně s desetinou čárkou xx,xkg. potřeboval bych vrátit tuto hodnotu. Řešil jsem to kombinací funkcí ale není to spolehlivé. nevíte jak na to prosím?citovat
odpověděl(a)
elninoslov
Nerozumiem Vašemu zadaniu. Uveďte prílohu s príkladom, čo máte, a aj toho čo chcete z toho mať. Tu prikladám príklad získania čísla z textu reprezentujúceho hmotnosť v gramoch bez ohľadu či sa napíše xxkg, xxxg, xx,xkg, xxx.

EDIT: No neberie mi to prílohu, tak to dávam na GoogleDrivecitovat
odpověděl(a)
avatar
no myslím třeba takovíto text "Brit Care DogJunior Large Breed Lamb & Rice 1kg" a ne vždy musí být váha na konci textu třeba "Canidae dog Foundations Puppy - Chicken - 10,8kg + dárek"citovat
odpověděl(a)
avatar
jo a text který chci získat je 1kg, 10,8kg atd.citovat
odpověděl(a)
elninoslov
Tak skúste toto maticové riešenie GoogleDrivecitovat
odpověděl(a)
avatar
Tak to mi bohužel nějak nefunguje.citovat
, Spojení odpovědět
avatar
Pro mně je to zbytečně složité mě by stačilo pouze pro třeba pro "\Rev.". A jen vzorce. Chci to kopírovat mezi listy s "Nazev" by to bylo zbytečně složité. Děkuji. PS kde se ty vzorce dají naučit nějaké jsem si vytvořil ,ale tohle mi nějak nejde.citovat
odpověděl(a)
elninoslov
Urobil som Vám teda ešte iné, ale to, že je to podľa Vás zložité, s tým nič neurobíte, jednoduché riešenie nieje.

Do B1:
=MID(A1;FIND("\Rev.";A1)+5;FIND(" ";A1;FIND("\Rev.";A1)+5)-(FIND("\Rev.";A1)+5))
=ČÁST(A1;NAJÍT("\Rev.";A1)+5;NAJÍT(" ";A1;NAJÍT("\Rev.";A1)+5)-(NAJÍT("\Rev.";A1)+5))
alebo
=LEFT(REPLACE(A1;1;FIND("\Rev.";A1)+4;"");FIND(" ";REPLACE(A1;1;FIND("\Rev.";A1)+4;""))-1)
=ZLEVA(NAHRADIT(A1;1;NAJÍT("\Rev.";A1)+4;"");NAJÍT(" ";NAHRADIT(A1;1;NAJÍT("\Rev.";A1)+4;""))-1)


Do C1:
=TRIM(MID(A1;FIND(" ";A1;FIND("\Rev.";A1)+5)+1;FIND("-";A1;FIND(" ";A1;FIND("\Rev.";A1)+5)+1)-(FIND(" ";A1;FIND("\Rev.";A1)+5)+1)))
=PROČISTIT(ČÁST(A1;NAJÍT(" ";A1;NAJÍT("\Rev.";A1)+5)+1;NAJÍT("-";A1;NAJÍT(" ";A1;NAJÍT("\Rev.";A1)+5)+1)-(NAJÍT(" ";A1;NAJÍT("\Rev.";A1)+5)+1)))
alebo
=TRIM(LEFT(REPLACE(REPLACE(A1;1;FIND("\Rev.";A1)+4;"");1;FIND(" ";REPLACE(A1;1;FIND("\Rev.";A1)+4;""));"");FIND("-";REPLACE(REPLACE(A1;1;FIND("\Rev.";A1)+4;"");1;FIND(" ";REPLACE(A1;1;FIND("\Rev.";A1)+4;""));""))-1))
=PROČISTIT(ZLEVA(NAHRADIT(NAHRADIT(A1;1;NAJÍT("\Rev.";A1)+4;"");1;NAJÍT(" ";NAHRADIT(A1;1;NAJÍT("\Rev.";A1)+4;""));"");NAJÍT("-";NAHRADIT(NAHRADIT(A1;1;NAJÍT("\Rev.";A1)+4;"");1;NAJÍT(" ";NAHRADIT(A1;1;NAJÍT("\Rev.";A1)+4;""));""))-1))


Do D1:
=TRIM(MID(A1;FIND("-";A1;FIND("\Rev.";A1)+4)+1;LEN(A1)-FIND("-";A1;FIND("\Rev.";A1)+4)-1-LEN(TRIM(RIGHT(SUBSTITUTE(A1;" ";REPT(" ";LEN(A1)));LEN(A1))))))
=PROČISTIT(ČÁST(A1;NAJÍT("-";A1;NAJÍT("\Rev.";A1)+4)+1;DÉLKA(A1)-NAJÍT("-";A1;NAJÍT("\Rev.";A1)+4)-1-DÉLKA(PROČISTIT(ZPRAVA(DOSADIT(A1;" ";OPAKOVAT(" ";DÉLKA(A1)));DÉLKA(A1))))))


Do E1:
=TRIM(SUBSTITUTE(RIGHT(SUBSTITUTE(A1;" ";REPT(" ";LEN(A1)));LEN(A1));".docx";""))
=PROČISTIT(DOSADIT(ZPRAVA(DOSADIT(A1;" ";OPAKOVAT(" ";DÉLKA(A1)));DÉLKA(A1));".docx";""))


Kombinácií je veľké množstvo. A kde sa to naučiť ? Na to asi neexistuje odpoveď.
-Musíte s tým prichádzať do styku, nestačí iba občas - čiže prax
-Takéto fórum ako toto, je vynikajúci zdroj, sekcia Návody, ale hlavne sekcia Fórum
-Musíte mať snahu a veľa času
-Na základné nalepenie vedomosti je vhodné si prečítať nejakú odbornú literatúru, nemusí to byť hneď kniha "Mistrovství v Microsoft Excel".
-No a samozrejme Google a nejaká tá angličtina
...

A ešte k tomu kopírovaniu. Predtým ako to budete bunku s vzorcom kopírovať, si nastavte všetky adresy zdrojovej bunky, podľa toho kde bude, či treba absolútnu adresu (nebude sa pri kopírovaní meniť $A$1, alebo sa bude meniť iba časť $A1, ako potrebujete), alebo relatívnu (ako teraz A1). Pozor na to. Toto sa musíte naučiť používať.citovat
, Spojení odpovědět
avatar
Chci se zeptat jak by šlo původní 4 vzorce z 31.10.2015 2:15 spojit s =DOSADIT(NAHRADIT(B2;1;NAJÍT("\Nav.";B2)+4;"");".docx";"") tak aby to šlo přímo bez tohoto řádku.citovat
odpověděl(a)
elninoslov
Dôrazne doporučujem použiť na zjednodušenie vzorcov Definovaný názov.

Definovaný názov : "Nazev"
=SUBSTITUTE(REPLACE(Hárok1!$A$1;1;IFERROR(FIND("\Nav.";Hárok1!$A$1);FIND("\Rev.";Hárok1!$A$1))+4;"");".docx";"")
=DOSADIT(NAHRADIT(Hárok1!$A$1;1;CHYBHODN(NAJÍT("\Nav.";Hárok1!$A$1);NAJÍT("\Rev.";Hárok1!$A$1))+4;"");".docx";"")

je nastavený aj na hľadanie "\Nav." aj na "\Rev.". Pozor na meno listu.

Do A1:
G:\OneDrive\Dokumenty\Navrhu\Navrhu 2015\Nav.055 Nějaký text různé délky - nějaký text různé délky a datum 8.11.2015.docx

Do B1:
=LEFT(Nazev;FIND(" ";Nazev)-1)
=ZLEVA(Nazev;NAJÍT(" ";Nazev)-1)


Do C1:
=TRIM(MID(Nazev;LEN(B1)+2;FIND("-";Nazev)-LEN(B1)-2))
=PROČISTIT(ČÁST(Nazev;DÉLKA(B1)+2;NAJÍT("-";Nazev)-DÉLKA(B1)-2))


Do D1:
=TRIM(MID(Nazev;FIND("-";Nazev)+1;LEN(Nazev)-(FIND("-";Nazev)+1+LEN(E1))))
=PROČISTIT(ČÁST(Nazev;NAJÍT("-";Nazev)+1;DÉLKA(Nazev)-(NAJÍT("-";Nazev)+1+DÉLKA(E1))))


Do E1:
=TRIM(RIGHT(SUBSTITUTE(Nazev;" ";REPT(" ";LEN(Nazev)));LEN(Nazev)))
=PROČISTIT(ZPRAVA(DOSADIT(Nazev;" ";OPAKOVAT(" ";DÉLKA(Nazev)));DÉLKA(Nazev)))


Ak nechcete použiť Definovaný názov, tak všetky výskyty slova "Nazev" zamente za to, čo ste mali dať do Definovaného názvu (tentoraz nemusíte názov listu dávať, alebo podľa potreby), teda
SUBSTITUTE(REPLACE($A$1;1;IFERROR(FIND("\Nav.";$A$1);FIND("\Rev.";$A$1))+4;"");".docx";"")
DOSADIT(NAHRADIT($A$1;1;CHYBHODN(NAJÍT("\Nav.";$A$1);NAJÍT("\Rev.";$A$1))+4;"");".docx";"")


podľa potreby zmente absolútne adresovanie $A$1 na relatívne A1.

A vzorce tak budú síce bez Definovaného názvu, ale vážne neprehľadné a ťažko upraviteľné:

Do B1:
=LEFT(SUBSTITUTE(REPLACE($A$1;1;IFERROR(FIND("\Nav.";$A$1);FIND("\Rev.";$A$1))+4;"");".docx";"");FIND(" ";SUBSTITUTE(REPLACE($A$1;1;IFERROR(FIND("\Nav.";$A$1);FIND("\Rev.";$A$1))+4;"");".docx";""))-1)
=ZLEVA(DOSADIT(NAHRADIT($A$1;1;CHYBHODN(NAJÍT("\Nav.";$A$1);NAJÍT("\Rev.";$A$1))+4;"");".docx";"");NAJÍT(" ";DOSADIT(NAHRADIT($A$1;1;CHYBHODN(NAJÍT("\Nav.";$A$1);NAJÍT("\Rev.";$A$1))+4;"");".docx";""))-1)


Do C1:
=TRIM(MID(SUBSTITUTE(REPLACE($A$1;1;IFERROR(FIND("\Nav.";$A$1);FIND("\Rev.";$A$1))+4;"");".docx";"");LEN(B1)+2;FIND("-";SUBSTITUTE(REPLACE($A$1;1;IFERROR(FIND("\Nav.";$A$1);FIND("\Rev.";$A$1))+4;"");".docx";""))-LEN(B1)-2))
=PROČISTIT(ČÁST(DOSADIT(NAHRADIT($A$1;1;CHYBHODN(NAJÍT("\Nav.";$A$1);NAJÍT("\Rev.";$A$1))+4;"");".docx";"");DÉLKA(B1)+2;NAJÍT("-";DOSADIT(NAHRADIT($A$1;1;CHYBHODN(NAJÍT("\Nav.";$A$1);NAJÍT("\Rev.";$A$1))+4;"");".docx";""))-DÉLKA(B1)-2))


Do D1:
=TRIM(MID(SUBSTITUTE(REPLACE($A$1;1;IFERROR(FIND("\Nav.";$A$1);FIND("\Rev.";$A$1))+4;"");".docx";"");FIND("-";SUBSTITUTE(REPLACE($A$1;1;IFERROR(FIND("\Nav.";$A$1);FIND("\Rev.";$A$1))+4;"");".docx";""))+1;LEN(SUBSTITUTE(REPLACE($A$1;1;IFERROR(FIND("\Nav.";$A$1);FIND("\Rev.";$A$1))+4;"");".docx";""))-(FIND("-";SUBSTITUTE(REPLACE($A$1;1;IFERROR(FIND("\Nav.";$A$1);FIND("\Rev.";$A$1))+4;"");".docx";""))+1+LEN(E1))))
=PROČISTIT(ČÁST(DOSADIT(NAHRADIT($A$1;1;CHYBHODN(NAJÍT("\Nav.";$A$1);NAJÍT("\Rev.";$A$1))+4;"");".docx";"");NAJÍT("-";DOSADIT(NAHRADIT($A$1;1;CHYBHODN(NAJÍT("\Nav.";$A$1);NAJÍT("\Rev.";$A$1))+4;"");".docx";""))+1;DÉLKA(DOSADIT(NAHRADIT($A$1;1;CHYBHODN(NAJÍT("\Nav.";$A$1);NAJÍT("\Rev.";$A$1))+4;"");".docx";""))-(NAJÍT("-";DOSADIT(NAHRADIT($A$1;1;CHYBHODN(NAJÍT("\Nav.";$A$1);NAJÍT("\Rev.";$A$1))+4;"");".docx";""))+1+DÉLKA(E1))))


Do E1:
=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(REPLACE($A$1;1;IFERROR(FIND("\Nav.";$A$1);FIND("\Rev.";$A$1))+4;"");".docx";"");" ";REPT(" ";LEN(SUBSTITUTE(REPLACE($A$1;1;IFERROR(FIND("\Nav.";$A$1);FIND("\Rev.";$A$1))+4;"");".docx";""))));LEN(SUBSTITUTE(REPLACE($A$1;1;IFERROR(FIND("\Nav.";$A$1);FIND("\Rev.";$A$1))+4;"");".docx";""))))
=PROČISTIT(ZPRAVA(DOSADIT(DOSADIT(NAHRADIT($A$1;1;CHYBHODN(NAJÍT("\Nav.";$A$1);NAJÍT("\Rev.";$A$1))+4;"");".docx";"");" ";OPAKOVAT(" ";DÉLKA(DOSADIT(NAHRADIT($A$1;1;CHYBHODN(NAJÍT("\Nav.";$A$1);NAJÍT("\Rev.";$A$1))+4;"");".docx";""))));DÉLKA(DOSADIT(NAHRADIT($A$1;1;CHYBHODN(NAJÍT("\Nav.";$A$1);NAJÍT("\Rev.";$A$1))+4;"");".docx";""))))
citovat
, Úprava vzorce odpovědět
avatar
Dobrý den,
tak jsem hledal a našel vaše stránky. Využil jsem níže uvedené vzorce, ale nedaří se mi upravit ten první a dále pak odmazání koncovky .docx . Potřeboval bych, aby se mazala i cesta a to číslo bylo fungovalo jako odkaz na daný dokument. Zatím jsem to řešil pomocnými sloupci v jednom je =KDYŽ(A2;ZPRAVA(B2;DÉLKA(B2)-45);" ") druhem je =KDYŽ(A2;ZLEVA(C2;DÉLKA(C2)-5);" ") a třetím je =KDYŽ(A2;HYPERTEXTOVÝ.ODKAZ(B2;E2);""). V buňce A 2 je pouze podmínka, aby se nevypisovali nesmysli pokud tam nic není. Ten text buňce B2 je G:\OneDrive\Dokumenty\Navrhu\Navrhu 2015\Nav.055 Nějaký text různé délky + nějaký text různé délky a datum 8.11.2015.docx Poradíte mi prosím jak to upravit.citovat
odpověděl(a)
elninoslov
Myslíte niečo takéto ?
SK
=SUBSTITUTE(REPLACE(B2;1;FIND("\Nav.";B2)+4;"");".docx";"")
CZ
=DOSADIT(NAHRADIT(B2;1;NAJÍT("\Nav.";B2)+4;"");".docx";"")
Ten kód slúži ako oddeľovač a musí začínať na "\Nav.", ďalej musí to byť dokument ".docx". Ak kód bude začínať aj inak (napr. "\Doc.") alebo prípona môže byť napr aj ".doc", treba vzorec skomplikovať.citovat
, Výpis slova odpovědět
avatar
Děkuji,
ten vzorec chodí, ale není to přesně co jsem potřeboval. Mně jde o to, že potřebuji tento vzorec kopírovat postupně po řádcích a hledat ty výrazy také po jednotlivých řádcích kam ho nakopíruji. Do řádku jsem to sice převedl vzorec chodil buňky A1.Když jsem to nakopíroval do druhé řádku vždy to chodilo pouze z první buňky A1.citovat
odpověděl(a)
elninoslov
Stačí v Definovanom názve "Vyskyt" zmeniť $A$1 na $A1citovat
, Výpis slova odpovědět
avatar
Asi jsem se špatně vyjádřil potřeboval bych ta slova vypsat do samostatné buňky.citovat
odpověděl(a)
elninoslov
No to ste sa teda naozaj zle vyjadril, aj predtým, aj teraz. Vy chcete vypísať vlastne zo známych slov, tie, ktoré sa v danom texte vyskytujú ? Ak ich máte iba 6, tak nepotrebujete makro.
http://uloz.to/xJRu4SoZ/slova-xlsxcitovat
, Slovo odpovědět
avatar
Dobrý den,
Jak by šlo z jedné buňky kde je text vyndat nějaké slovo. Text je pokaždé jinak dlouhý a jiný obsah. Ty slova se vyndávali, pokud by je text obsahoval. Jednalo by se asi o 6 vybraných slov. Jaký vzorec by šel použít? Děkuji.citovat
odpověděl(a)
elninoslov
Najjednoduchšie mi to príde makro-funkciou:
Function VymazSlova(Kde As String, Coo As String) As String
Dim Co, i As Integer
Co = Split(Coo, ",")
For i = LBound(Co) To UBound(Co)
Kde = Replace(Kde, Co(i), "", , , vbTextCompare)
Next i
VymazSlova = Trim(Replace(Replace(Replace(Kde, " ", "•°"), "°•", ""), "•°", " "))
End Function

Použitie v liste potom bude:
=VymazSlova(A1;"ja,ty,my,vy,oni,tie")citovat
, jak postupovat odpovědět
avatar
Děkuji už jsem si to opravil sám. Chyba byla v překladu slova délka.citovat
odpověděl(a)
elninoslov
Presne tak, a keď sa pozriete na čas písania príspevku, tak je zabudnutie jedného LEN celkom v norme :). Ale super, že ste na to prišiel sám.
PS: Musím začať fičať na tom prekladači od eLCHa...citovat
, jak postupovat odpovědět
avatar
Děkuji jsou to docela dobré vzorce. Ale poslední vzorec E1 ten nejde na víc děla chybu i do vzorce na D1.Ve vzorci D1 je asi na víc (délka E1).Mohl by jste prosím opravit ten poslední, aby v poslením sloupci E1 byl pouze datum.citovat

Strana:  1 2   další »

Uživatelské menu

Nejste přihlášen(a)
avatar\n

Menu

On-line nástroje

Formulář Faktura

Formulář Faktura III

Oblíbený formulář Faktura byl vylepšen a rozšířen. Formulář faktura III
Více se dočtete zde.

Aktivní diskuse

zavření souboru z VBA makra bez uložení

Palooo • 17.7. 9:02

Parametricky dotaz v power query

AL • 13.7. 15:23

Parametricky dotaz v power query

lubo • 13.7. 15:20

Parametricky dotaz v power query

AL • 13.7. 13:23

Parametricky dotaz v power query

mepexg • 13.7. 13:10

Parametricky dotaz v power query

AL • 13.7. 9:43

Parametricky dotaz v power query

mepexg • 13.7. 5:42