Funkce textové - praktické příklady - Excel

Co se v článku dozvíte

Seznam řešených příkladů:

• PRÁCE SE ZNAKY
  • Spočítat výskyt zadaného znaku -buňka - kolik krát se zadaný znak objevuje v buňce
  • Počet znaku v zadané oblasti - vychází z předchozího příkladu
  • Odstranit dvě a více mezer - zůstane pouze jedná mezera mezi slovy
  • Vložit znak na určené místo
  • Doplnit text nulami nebo jiným znakem - napříkad z důvodu souměrnosti
• PRÁCE SE TEXTY / SLOVY
  • Spočítat výskyt výrazu v oblasti - jak zjistit kolikrát se zadané slovo vyskytuje v požadované oblasti
  • Vložit +420 do telefoního čísla
  • Rozdělení jména - na jméno a příjmení, které je v jedné buňce
  • Obsahuje buňka slovo / text - využití funkce KDYŽ a HLEDAT
• SPECIÁLNÍ ZNAKY
  • Uvozovky ve funkci CONCATENATE - využití funkce ZNAK
  • Jak sloučit text do jedné buňky a rozdělit do více řádku
• SPOJENÍ FUNKCÍ
  • Uvozovky ve funkci CONCATENATE - využití funkce ZNAK
  • Obsahuje buňka slovo / text - využití funkce KDYŽ a HLEDAT
  • Odřádkování místo čárky - využití DOSADIT a ZNAK
• OSTATNÍ
  • Z textu vytvořit hodnotu - pro další výpočty
  • Další využití textových funkcí
• KE STAŽENÍ
  • Ke stažení ukázkové příklady

Úvodem do textových funkcí

Teoretický popis funkcí, jejich syntaxe a praktický příklad je k dispozici v článku: Textové funkce - abecedně řazený popis se syntaxi a příklady. V totmo článku již tyto vědomosti aplikujeme do praktických příkladů, které jsou v praxi potřeba.

Na řešení většinou nejde použít jen jedna funkce, ale musí se jich zkombinovat několik. čímž se teprve dostanete k požadovanému výsledku.

Spočítat výskyt zadaného znaku

Například v buňce je zadáno slovo: "nejnekulaťoulinkatější" a potřebujete zjistit kolikrát se v tomto slově objevuje písmeno "e"!

Řešení je jednoduché.

• Zjistíte délku řetězce - DÉLKA
• Ze zkoumaného řetězce odstraníte hledané znaky - tj. nahradíte je prázdným znakem funkce DOSADIT
• Zjistíte délku zkráceného řetězce opět funkce DÉLKA
• Tyto dvě délky odečtete

Ukázkové řešení za předpokladu, že zkoumaný text je v buňce A1 a funkce (vzorec) bude v buňce B1

=DÉLKA(A1)-DÉLKA(DOSADIT(A1;"e";""))

Počet znaku v oblasti

Zjištění počtu výskytu daného znaků (například e) v zadané oblasti (např: A1:A5.). V přechdozího příkladů je popsán teoretický postup řešení (takže se nebudu opakovat). Jen sledovanou oblast místo buňky rozšiříme na porhledávanou oblast.

=SUMA(DÉLKA(A1:A5)-DÉLKA(DOSADIT(A1:A5;"e";"")))

Poznámka: Nutno zadávat jako maticový vzorec Shift + Ctrl + Enter, ale to jen na doplnění.

Spočítat výskyt výrazu v oblasti

Jak spočítat kolikrát se výraz vyskytuje v dané oblasti. V buňkách A1 až A5 máte zadána různá slova (například klíčová slova z vyhledávače) a potřebujete zjistit, kolikrát se dané slovo v této oblastí nalézá. Podobně jako v předchozím případě, jen budete počítat s celou oblastí.

• Zjistíte délku řetězce v dané oblastí
• Ze zkoumané oblasti odstraníte hledané slovo - nahradíte je prázdným znakem
• Zjistíte délku bez hledaného slova
• Tyto dvě délky odečtete - jako v předchozím příkladu
• Vydělíte délkou daného slova

Tabulka do Vašeho příkladu:

• A1: letenky
• A2: levné letenky
• A3: letenky zdarma
• A4: letenky chorvatsko
• A5: letenky balonem

To buňky zadáte vzorec, která spočte počet výskytu slova letenky v oblasti A1:A5

=SUMA(DÉLKA(A1:A5)-DÉLKA(DOSADIT(A1:A5;"letenky";"")))/DÉLKA("letenky")

Poznámka: Samozdřejmě jde vzorec upravit tak aby se daná slova volily dynamicky, včetně dynamické volby oblastí, atd.

Poznámka: Nutno zadávat jako maticový vzorec Shift + Ctrl + Enter, ale to jen na doplnění.

Odstranit dvě a více mezer

Mám text, kde někdo mezi slovy nechal dvě a více mezer, jak mohu tyto mezery odstranit? Doporučuji použít funkci PROČISTIT. Třeba je dotyčný placen za úhozy (a mezerník je také úhoz).

PROČISTIT(" Někdo kdo má velice rád mezerník a fláka mezery všude. ")

Výsledkem bude normální čitelný text: Někdo kdo má velice rád mezerník a fláka mezery všude.

Vložit znak na určené místo

Máme rodné číslo např 7711225544 a potřebujeme na 7 místo vložit lomítko. Nejideálnější se jeví použít funkci NAHRADIT. Kdy počtu nahrazovaných znaků přiřadíte nulu. Předpoklad v buňce A1 je rodné číslo.

Rodné číslo bude vypadat 771122/5544.

=NAHRADIT(A1;7;0;"/")

Finta pokud budete chtít dále doplnit mezery za rok a měsíc.

=NAHRADIT(NAHRADIT(NAHRADIT(C11;5;0;"-");3;0;"-");9;0;"/")

Rodné číslo bude vypadat 77-11-22/5544.

Vložit +420 do telefoního čísla

potřebujete vložit +420 do telefoního čísla

=CONCATENATE("+420";A36)

ošetříme pokud by buňka pro telefoní číslo byla prázdná tak nebude vypsáno +420 ale buňka bude prázdná.

=KDYŽ(A36="";"";CONCATENATE("+420";A36))

A doplníme o kontrolu pokud buňka už obsahuje číslo s předvolbou ať není výsledek+420+420777888999 ...

=KDYŽ(A44="";"";KDYŽ(DÉLKA(A44)=9;CONCATENATE("+420";A44);A44))

Dále jde kontrolovat zda není zadaváno 777 222 333 atd...

Jak sloučit text do jedné buňky a rozdělit do více řádku

Potřebujete sloučit text v buňkách a přidat do buňky "Alt+Enter". Tak aby byl text v jedné buňce ve více řádcích

aaa

bbb

ccc

aaa bbb ccc

Alt+Enter provede funkce ZNAK(10) v anglickém excelu CHAR(10). Sloučení provedete do buňky A4, v buňkách A1, A2, A3 budete mít slučovaný text.

=A1 & ZNAK(10) & A2 & ZNAK(10) & A3

Nebo

=CONCATENATE(A1;ZNAK(10);A2;ZNAK(10);A3)

Poznámka: Ve formátu buňky musí být povoleno zalomit text.

Rozdělení jména

Potřebujeme rozdělit jméno, které se nachází v jedné buňce. Jeméno rozdělíme na Jméno a příjmení.

pro jméno

=ZLEVA(A48;(NAJÍT(" ";A48;1))-1)

pro příjmení

=ČÁST(A48;(NAJÍT(" ";A48;1))+1;DÉLKA(A48))

nebo:

=ZPRAVA(A52;(DÉLKA(A52)-NAJÍT(" ";A52;1)))

Poznámka: Předpokládám, že v buňce je jen jméno a přijmení. Neobsahuje tituly, další jména atd.

Z textu vytvořit hodnotu

Potřebujete-li po úpravách pomocí textových funkcí dále s tímto výsledkem počítat můžete využít funkce HODNOTA převést na hodnotu s kterou lze dále počítat:

=HODNOTA(ZLEVA(A60;3))

Nebo fintu a pouze násobit jedničkou :)

=ZLEVA(A58;3)*1

Doplnit text nulami

V buňce mám údaj a potřebuji jej doplnit nulami, tak aby počet číslic byl 13.

Pro 1 to bude 1000000000000

Pro 12345 to bude 1234500000000

Možné řešení je:

=CONCATENATE(A1;OPAKOVAT("0"; 13-DÉLKA(A1)))

Odřádkování místo čárky

Z databáze je v jedné buňce adresa, která jsou položky odděleny čárkami, pro tisk štítku je potřeba text odřádkovat. Místo čárky vložit Enter. Jak to provést? Využitím funkce DOSADIT.

=DOSADIT(B6;", ";ZNAK(10))

Uvozovky ve funkci CONCATENATE

Je potřeba aby funkce CONTACENATE zobrazila uvozovky.

Doporučuji využít funkce ZNAK(34). Tato funkce zobrazí uvozovky a funkce CONCATENATE si s těmato uvozovkama bez problému poradí.

" =ZNAK(34) "test" =CONCATENATE(ZNAK(34);"test";ZNAK(34)) =CONCATENATE("test") =CONCATENATE("=CONCATENATE(";ZNAK(34);"test";ZNAK(34);")")

Obsahuje buňka slovo

Obsahuje buňka hledané slovo? Využití HLEDAT a KDYŽ.

=KDYŽ(HLEDAT("koruna";B5)>0;"obsahuje";"neobsahuje")

Další využití textových funkcí

Textové funkce lze využít v mnoha praktických aplikacích. Obrovské využití mají při statistických analýzách textů. Při analýzách pro SEO reporty. Chceteli se dozvědět více sepsal jsem tyto články, které využívají textové funkce:

• Excel pro SEO (1)
• Excel pro SEO (2)

Ke stažení ukázkové příklady

Soubor Ukázky praktických příkladů na textové funkce - Microsoft Excel 2010, ke stažení zdarma.

Závěrem

Pro případné dotazy a odpovědi jsou k dispozici komentáře. Zajímavý dotaz dolním do článku.

Článek byl aktualizován: 19.09.2020 10:57

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

           

Komentáře