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
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
=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
EDIT: No neberie mi to prílohu, tak to dávam na GoogleDrivecitovat
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
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
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
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
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
http://uloz.to/xJRu4SoZ/slova-xlsxcitovat
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
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
PS: Musím začať fičať na tom prekladači od eLCHa...citovat