Funkce pro práci s textem – spojování, pročištění, vyhledávání

calc.png Text v buňkách můžete pomocí funkcí vyhledávat, upravovat, nahrazovat, transformovat a provádět s ním mnohá další kouzla. V minulých dílech jste se již naučili vzorce vkládat, kopírovat, upravovat, takže nadále se již budeme zabývat samotnými funkcemi a jejich využitím.  

Přdchozí díly volného seriálu:

Velmi často je potřeba spojit obsah různých buněk do jedné výsledné. V případě sčítání čísel použijeme funkci součet (+), v případě textu je to operátor &, tedy např. =A1&B1. Zde nezapomínejte na to, že mezi slova a věty patří mezery, takže je potřeba vzorec patřičným způsobem upravit, tj. např. =A1&" "&B1. Spojovat takto můžete text i čísla, např.

="výška "&A2&" cm x šířka "&B2&" cm = "&C2&" cm2"

kdy v buňkách A2, B2 a C2 jsou příslušné číselné údaje. Následující obrázek obsahuje praktickou ukázku.

Spojování obsahu buněkSpojování obsahu buněk

Pokud vás dlouhá změť znaků & mate, použijte namísto toho funkci CONCATENATE, jejímž výsledkem je spojení zadaných parametrů. Výše uvedený příklad tedy zapíšete jako

=CONCATENATE("výška ";A2;" cm x šířka ";B2;" cm = ";C2;" cm2")

Pokud chcete zachovat bezproblémovou kompatibilitu s formátem XLS, nepoužívejte v této funkci více než 30 parametrů.

Další užitečnou funkcí je TRIM, která odstraní ze zadaného textu nadbytečné mezery na začátku a na konci. Uprostřed textu pak převede vícenásobné mezery na jednoduché. Jak jsme si řekli již dříve, funkce je možné na sebe navazovat - zde se pak nabízí spojení

=TRIM(CONCATENATE(text1;text2;text3)) 

Vyčištění nadbytečných mezer po spojení textůVyčištění nadbytečných mezer po spojení textů

TRIM se vám bude hodit také v případě porovnávání a vyhledávání řetězců, kdy jedna zapomenutá mezera na konci navíc může znamenat neshodu. Čisticí úlohu má také funkce CLEAN, která ze zadaného textu odstraňuje netisknutelné znaky. K převodu daného textu na malá písmena použijte funkci LOWER, na velká pak funkci UPPER. Použijete-li funkci PROPER, budou začátky všech slov velkými písmeny, jak je vidět na následujícím příkladu.

Změna velikosti písmenZměna velikosti písmen

Často je potřeba ze zadaného textu získat jeho část. K tomu vám poslouží několik funkcí. LEFT vrací z textu zadaný počet znaků zleva, RIGHT zprava a MID pak od zadané pozice v řetězci, např. výsledkem MID("Ahoj";2;2) bude "ho". Další příklady vidíte na obrázku.

Získání zvolené části textuZískání zvolené části textu

Málokdy je však předem známo, kolik znaků nebo z které pozice je potřeba text získat, resp. se tyto údaje liší v závislosti na skutečném obsahu buněk - např. chcete pouze první slovo z druhé věty. Tento již složitější úkol pomohou vyřešit výše uvedené funkce v kombinaci s funkcemi pro vyhledávání FIND a SEARCH.

Funkce FIND hledá zadaný text ve zvoleném řetězci od stanovené pozice. Pokud ho nalezne, vrátí pozici začátku jeho prvního výskytu, jinak chybovou hodnotu #VALUE!. Vše nejlépe vysvětlí krátký příklad: FIND("text";"Prohledávaný text"). Výsledkem bude číslo 14, protože „text" začíná v „Prohledávaný text" na 14. znakové pozici. Funkce FIND rozlišuje velikost znaků („case-sensitive"), takže FIND("Text";"Prohledávaný text") skončí chybovým hlášením #VALUE!.

„Case-sensitive" znamená, že jsou rozlišována malá a velká písmena; např. „Ahoj" pak není to samé jako „ahoj" či „AHOJ" apod.

FIND může mít ještě třetí parametr, kterým je pozice, od které se má začít hledat: FIND("tam";"tam a tam";2). Výsledkem bude hodnota 7.

Funkce SEARCH je velmi podobná - liší se v tom, že u ní nezáleží na velikosti písmen a lze v ní použít zástupné znaky - regulární výrazy, které výrazným způsobem rozšiřují možnosti hledání. Jejich použití však vyžaduje určité znalosti a zkušenosti, které přesahují rámec tohoto článku. Pro použití regulárních výrazů v některých funkcích musí být aktivováno jejich vyhodnocování přes menu Nástroje | Volby... | OpenOffice.org Calc | Spočítat zaškrtnutím Povolit regulární výrazy ve vzorcích.

Nechť je v buňce A2 text „První věta. Druhá věta." Jak získat první slovo z druhé věty? Nejprve z textu vypreparujte druhou větu - předpokladem je, že druhá věta začíná po první tečce v textu, takže vzorec =MID(A2;FIND(".";A2)+1;LEN(A2)) vrátí řetězec „ Druhá věta." K výsledku funkce FIND(".";A2) je zde nutné přičíst jedničku, protože výsledkem je pozice první tečky a vy chcete vrátit znaky až za první tečkou. První slovo pak dostanete tak, že si necháte vypsat všechny znaky až po první mezeru (neuvažuje se zde, že by za prvním slovem mohla být např. čárka). Protože po tečce za větou bývá obvykle mezera (nemusí to ale tak být), odstraňte dále ze získané druhé věty nadbytečné mezery pomocí funkce TRIM:

=TRIM(MID(A2;FIND(".";A2)+1;LEN(A2)))

Nyní získáte první slovo tak, že v druhé větě najdete pozici první mezery a necháte si vrátit všechny znaky do této pozice:

=LEFT(TRIM(MID(A2;FIND(".";A2)+1;LEN(A2)));FIND(" ";TRIM(MID(A2;FIND(".";A2)+1;LEN(A2))))-1)

Vzorec vypadá složitě, protože obsahuje všechny kroky najednou. Pokud si ale do buňkyA3 vložíte předchozí vzorec TRIM(MID(A2;FIND(".";A2)+1;LEN(A2))), tedy získání druhé věty, zjednoduší se zápis na =LEFT(A3;FIND(" ";A3)-1), tedy že chcete z druhé věty (je nyní v buňce A3) získat zleva o jeden znak méně, než je pozice první mezery ve druhé větě. Výsledkem bude řetězec „Druhá". Použitá funkce LEN vrací délku zadaného textu včetně všech mezer na začátku a na konci.

Získání prvního slova z druhé větyZískání prvního slova z druhé věty

Uvedené příklady si můžete stáhnout: Textové funkce 1 - příklady, taktéž si můžete stáhnout přehledovou tabulku v PDF formátu: Textové funkce - přehledová tabulka.

(Jako ve škole) Průměr: 1,00 | Hodnotilo: 11
 

Komentáře

user avatar Gudrun Zanícený
Odpovědět
Funkce pro práci s textem – spojování, pročištění, vyhledávání
20. 08. 2010, 20:18:56
Prosím o radu s formátováním části řetězce. Rád bych ve vzorci, který mi vrací dlouhý text složený z různých buněk, měl některé znaky tučně. Konkrétně ="1. třída: "&A1&A2&A3&"2. třída: "&B1&B2&B3 . Tak aby "1. třída:" a "2. třída:" byly tučně. Díky za pomoc.
user avatar hanus
Odpovědět
Re:Funkce pro práci s textem – spojování, pročištění, vyhledávání
15. 01. 2011, 00:13:22
Myslím si, že toho nelze pomocí vzorců dosáhnout. Vzorcem lze aplikovat styl, ale na celou buňku. Rozlišné formátování v obsahu buňky lze jen u vloženého textu pomocí formátování znaků.
user avatar Eva Krejčová
Odpovědět
Funkce pro práci s textem – spojování, pročištění, vyhledávání
30. 11. 2011, 11:15:28
Prosím o vysvětlení záhady. Když v textovém řetězci hledám tečku pomocí funkce FIND, ukáže funkce její pozici bez problémů, když chci najít tečku pomocí funkce SEARCH, vrátí funkce číslo jedna, ať je tečka v řetězci kdekoli.
Díky za pomoc i za články, E.K.
user avatar Franta
Odpovědět
Funkce pro práci s textem – spojování, pročištění, vyhledávání
2. 03. 2016, 08:39:00
Dobrý den
potřebuji poradit jak odstranit mezeru na konci textu přímo v buňce kde se nachází.
Když použiji TRIM tak odstraní také komentář v buňce.
Nejlépe by bylo makro moc děkuji. Franta
user avatar lp.
Odpovědět
Re:Funkce pro práci s textem – spojování, pročištění, vyhledávání
3. 03. 2016, 09:06:25
Můžete sdělit jakou co a v jaké verzi používáte?

To, že by funkce listu odstranila z buňky komentář jsem dosud neslyšel.
user avatar neutr
Odpovědět
Funkce pro práci s textem – spojování, pročištění, vyhledávání
2. 03. 2016, 09:37:17
Takovéhle dotazy patří do fóra. Původní článek je z roku 2009 a čte to jen minimum lidí.
Měl byste uvést v jaké verzi AOO (LO) to maže komentář - to je jistě chyba kterou by bylo záhodno ověřit a nahlásit pokud je v aktuální verzi. Tedy například v LibreOffice Fresh, nebo Still, a nebo v poslední verzi AOO.

Věcně jde spíš o to, zda se nejdná například o zalomení textu. Pokud je to skutečně mezera a Trim nefunguje správně, můžete použít například vzorec = LEFT(A1;LEN(A1)-1) v pomocném sloupci.

Další cestou by bylo nahrazení textu pomocí dialogu "Najdi a nahraď". K tomu byste musel ještě prostudovat REGULÁRNÍ VÝRAZY - ale zase to lze aplikovat na mnoho buněk současně - tedy na celý sešit, nebo jen na výběr. To byste musel pro makro udělat taktéž. při tom žádný pomocný sloupec nepotřebujete - také výhoda.

Makro také není problém, ale vychází to nastejno se systémovým nahrazováním pomocí dialogu. Výhodu by to mělo, pokud byste potřeboval zahrnout nějaké podmínky, které dialog neumí. Také se dá spouštět pomocí klávesové zkratky, ikony, rozšířené hlavní nabídky, nebo tlačítka.

Přidat názor

 

Nejsou podporovány žádné značky, komentáře jsou jen čistě textové. Více o diskuzích najdete v nápovědě. Diskuzi můžete sledovat pomocí RSS kanálu.

 
Tomáš Hanusek

Tomáš Hanusek

 
 
 
woo jaw demo hz