Regulární výrazy v Excelu vypadají děsivě. A navíc – regulární výrazy jsou v Excelu relativní novinka, takže na ně prostě nejsme zvyklí. To by nás ale nemělo zastavit. Protože u regulárních výrazů platí víc než kdekoliv jinde, že i malá znalost vás posune neuvěřitelně daleko. Možná si teď říkáte: Proč se tím vůbec zabývat, když Excel už má spoustu textových funkcí? Jenže regulární výrazy řeší přesně ten moment, kdy klasické funkce přestávají stačit. A právě tady přichází regulární výrazy. Místo toho, abyste Excelu říkali „hledej přesně tenhle text“, mu řeknete: hledej cokoli, co odpovídá tomuto vzoru. A v tom je jejich síla.
Excelový soubor ke stažení:
Libovolný znak (.)
Začneme prvním regulárním výrazem, což je tečka. Tečka v regulárních výrazech znamená jakýkoliv jeden znak. Může se jednat o písmeno, číslici, mezeru, ale i speciální znak. Používá se, když nechceme ověřit konkrétní znak, ale chceme ověřit, že na daném místě je nějaký znak. K ověření regulárních výrazů použijeme novou funkci dostupnou předplatitelům Microsoft 365, a to funkci REGEXEXTRAHOVAT neboli REGEXEXTRACT.
Z následující tabulky chceme extrahovat následující vzor. Napíšeme funkci REGEXEXTRAHOVAT, kde nejprve označíme buňku s textem a chceme ověřit, zda se v buňce vyskytuje vzor, který začíná písmenem A a končí písmenem B. A mezi těmito písmeny má být jeden znak. Jakýkoliv znak. A vzor se vždy píše do uvozovek. Ověříme vzor “A.B”. Funkce vyestrahuje to, co odpovídá zadanému vzoru, tedy, že vzor začíná písmenem A a končí písmenem B a mezi nimi může být jakýkoliv znak.
Vedle v tabulce zkusíme vyselektovat druhý vzor, a to vzor, který začíná písmenem X, následuje pomlčka a za pomlčkou může být jeden znak. Takže vzor bude “X-.”
Číslice (\d)
Regulární výraz obráceného lomítka a písmena d znamená libovolná číslice. Tedy číslice od 0 do 9. Používá se ve chvíli, kdy chceme pracovat s číslicemi, ale nezáleží nám na konkrétní hodnotě čísla, pouze na tom, že se jedná o číslici.
V příkladu chceme vyselektovat z kódu číslici. Tím pádem ověřím „\d“ Funkce REGEXEXTRAHOVAT vyselektuje první číslo, na které narazí. Z čísla 22 se vrátí pouze 2.
Pokud bychom tímto způsobem chtěli vyselektovat dvě číslice, tak použijeme vzor “\d\d”.
Písmeno nebo číslo (\w)
Regulární výraz obráceného lomítka a dvojitého w reprezentuje znak slova, což může odpovídat písmenu, číslici nebo podtržítku. Tento regulární výraz neřeší, jaký znak to je, řeší jen, zda se jedná o písmeno, číslici nebo podtržítko. Ptáte se, proč se do tohoto regulárního znaku zahrnuje i podtržítko? Protože tento znak vznikl pro práci s identifikátory, nikoliv s běžným textem a do identifikátorů patří číslice, písmena a právě podtržítka. Tento znak v sobě neobsahuje ale mezery, pomlčky ani tečky.
Zkusíme vyselektovat vzor “\w” a vrátí se první znak, který odpovídá vzoru.
Jeden nebo více výskytů (+)
Znaménko plus v regulárních výrazech znamená jeden nebo více výskytů. Znak plus se vždy vztahuje k tomu, co mu bezprostředně předchází. Takže pokud budeme z tabulky chtít extrahovat jakékoliv číslice, tak napíšeme „\d+“. V tomto zápisu to znamená, že chceme vyselektovat číslice, které se ve vzoru vyskytují jednou nebo vícekrát. A plus následuje rovnou za písmenem d, které vyjadřuje číslice. Vyselektují se pouze číslice, které jdou po sobě.
A co kdybychom vedle naopak chtěli vyselektovat jakékoliv znaky, použijeme „\w+“.
Znak \d je tedy jedna číslice. Znak \d+ je jedno nebo více číslic za sebou.
Znak \w je jeden znak slova a znak \w+ je jedno nebo více znaků za sebou.
Nula nebo více výskytů (*)
Znak hvězdičky v regulárních výrazech znamená nula nebo vícekrát. Je tedy benevolentnější než znaménko plus, které říká jednou nebo vícekrát. Tento znak tedy říká, že tam něco může být, ale taky nemusí. Stejně jako znaménko plus se vztahuje ke znaku, který hvězdičce bezprostředně předchází.
Zkusíme to na stejné tabulce, jako v předchozím příkladu. Opět chceme nejprve vyselektovat číslice, takže použijeme „\d*“. Proč se na prvních řádcích vrací prázdno? Protože hvězdička znamená nula nebo více výskytů. Když tedy hvězdička hledá číslici, tak hned první znak je písmeno, vzor tedy nenašel číslici, ale jelikož se spokojí i s žádným výskytem, tak se vrátí prázdná buňka.
A vedle chceme ověřit výskyt jakéhokoliv znaku, tedy vzor „\w*“.
Hvězdička tedy * znamená, že předchozí část regulárního výrazu se může opakovat libovolněkrát, ale klidně tam nemusí být vůbec.
Nula nebo jeden výskyt (?)
Další znak, který specifikuje počet výskytů je otazník. Znak otazníku v regulárních výrazech znamená nula nebo jednou. Opět se vztahuje k bezprostředně předchozímu znaku a říká, že předchozí znak nebo skupina znaků je nepovinná, může tam být, ale tak nemusí. Zní vám to podobně jako hvězdička? Rozdíl je v tom, kolikrát se znak může vyskytnout. Znak hvězdička i otazník dovolují, aby se znak nevyskytl vůbec, ale liší se v tom, kolikrát se může objevit, pokud se objeví. U hvězdičky se nemusí objevit nebo se objeví vícekrát a u otazníku se objevit nemusí nebo se objeví jednou.
Zkusíme ověřit vzor “\d?” a tento vzor na řádcích, který neopdoví vzoru vrátí prázdnou buňku. Na ostatních řádcích vrátí první číslici.
Pokud zkusíme vyextrahovat vzor “\w?”, tak se vrátí vždy první znak, který odpovídá vzoru, pokud první znak neodpovídá znaku textu, tak se vrátí prázdná buňka.
Rozdíl mezi + * ?
Znaky plus, krát a otazník se velmi často pletou. Takže si rozdíl ukážeme na následující tabulce.
Ověříme vzor “A-?\w”. Tento vzor vrátí kód na prvních dvou řádcích, jelikož zde je vzor odpovídající. Pomlčka ve vzoru být může nebo nemusí. Nicméně poslední kód neodpovídá, jelikož jsou v něm dvě pomlčky.
Ve druhém sloupci ověříme vzor “A-+\w”. V tomto případě se vrátí chyba na prvním řádku, Znaménko plus znamená, že v kódu pomlčka bude jednou nebo vícekrát. A v prvním kódu pomlčka není.
V posledním sloupci ověříme kód “A-*\w”. Tím pádem se vyselektuje kód na každém řádku, jelikož hvězdička znamená, že se pomlčka může vyskytovat vícekrát a nebo se nemusí vyskytnout vůběc, tím pádem odpovídají všechny kódy.
Mezery (\s)
Obrácené lomítko a písmeno s reprezentuje tzv. bílé znaky, tedy znaky používané pro oddělení textu. Jedná se především o mezery, tabulátory a zalomení řádků. Stejně jako u obráceného lomítka a d a w reprezentuje právě jednu mezeru.
Ideální regulární výraz pro čištění textu. Tento znak totiž můžeme použít ve funkci REGEXNAHRADIT neboli REGEXREPLACE, kde ověříme, že máme v textu více mezer \s+ a můžeme to nahradit jednou mezerou. Mezi jménem a příjmením máme někde více mezer. Tak využijeme tohoto znaku k nahrazení jen jednou mezerou.
Složené závorky {}
V regulárních výrazech slouží složené závorky k určení přesného počtu opakování přechozího znaku nebo skupiny. Jinými slovy stanovují, kolikrát se má přechozí část vzoru opakovat. Stejně jako hvězdička a plus se vztahují vždy jen k tomu, co jim bezprostředně předchází. V další tabulce chceme z kódu vyselektovat šest znaků. Použijeme vzor “\w{6}”, a tím pádem se na řádcích vyselektuje prvních šest znaků. Pokud kód nemá šest znaků, tak se vrátí chyba.
Začátek a konec textu (^ a $)
Znaky stříška a dolaru se v regulárních výrazech nazývají kotvy a neoznačují žádný znak, ale pozici v textu. Stříška znamená začátek textu a dolar značí konec textu. Jejich úkolem je říct regexu, kde musí shoda začínat a kde končit.
Pokud použijeme stříšku, tak shoda musí začínat na začátku buňku. ^
Pokud použijeme dolar, tak shoda musí být na konci buňky.
V tabulce chceme vyselektovat kód ABC, ale musí být na začátku buňky.
Ve druhém sloupci chceme opět vyselektovat kód ABC, ale tentokrát musí být na konci.
Z tabulce chceme vyselektovat vzor, který začíná písmenem A a končí číslicí 5. A mezi nimi může být libovolný počet znaků. E4;”^A\w+5$”)
Tyto znaky tedy určují začátek a konec a používají se zejména, když chceme ověřit celý obsah buňky, nejen jeho část.
Skupiny ()
Kulaté závorky v regulárních výrazech slouží k seskupování. Závorky umožní, aby se více znaků chovalo jako celek. Kulaté závorky reprezentují skupiny, v podstatě tím říkáme, to co je v závorkách, to je ta část, která nás zajímá. Z tabulky chceme vyselektovat písmena ab, která jdou po sobě. K tomu použijeme (ab). Na řádku, kde nemáme tuto kombinaci se vrátí chyba.
Pokud bybchom chtěli vyselektovat všechna ab, která po sobě následují, tak použijeme “(ab)+”.
Množina znaků ([])
Hranaté závorky znamenají množinu znaků. V podstatě tím vyjadřujeme, že se ve vzoru může vyskytovat jeden libovolný znak z tohoto seznamu. Na rozdíl od kulatých závorek vybírají jeden znak, kdežto kulaté závorky seskupují více znaků. Takže zápis [abc] znamená a nebo b nebo c, nikdy to ale není abc jako celek, to by to muselo být zaspané (abc).
Pokud chceme ověřiit, že buňka obsahuje jakoukoliv číslici, tak použijeme zápis [0-9], což se v současné situaaci rovná zápisu \d
Pokud chceme ověřit, že kód obsahuje malé jakékoliv písmeno, tak použijeme zápis [a-z].
Pokud chceme ověřit, že se v buňce vyskytuje velké písmeno, jakékoliv písmeno, tak napíšeme [A-Z].
Nebo (|)
Znak svislé čáry reprezentuje logický výraz NEBO. V prvním sloupci chceme ověřit, na kterých řádcích je Ano nebo Ne. Použijeme funkci REGEXTEST, kde ověříme (Ano | Ne), nicméně tento zápis vrátí pravdu i na řádku, kde je slovo Nevím, jelikož to obsahuje část slova Ne.
Abychom opravdu ověřili, že se na řádcích objevuje pouze Ano nebo Ne, tak musíme právě použít znaky pro začátek a konec textu. Díky tomuto zápisu z ověření vypadnou slova Nevím.
Jak vidíte, regulární výrazy nejsou žádná černá magie. Nejde o to znát všechny vzory nazpaměť, ale pochopit princip — že Excelu neříkáte, co přesně má hledat, ale jaký vzor má hledat. A jakmile se tento způsob zápisů jednou naučíte, otevřou se vám úplně nové možnosti práce s textem. V dnešním videu jsme si prošly základní regulární znaky. Pokud máte zájem o další video, ve kterém navážeme na toto video se složitějšími kombinacemi regulárních znaků, dejte mi vědět v komentáři pod videem.
