Při práci s daty často vzniká potřeba data před jejich použitím určitým způsobem upravit, protože nám jejich struktura nevyhovuje. Pokud nám někdo pošle nějaký excel nebo si data exportujeme z podnikového systému, tak doufáme, že je nebudeme muset upravovat. Realita je jiná a často musíme použít celou řadu velkého seznamu excel funkcí – například pro extrakci nějakého řetězce typu ID nebo prostě najít a nahradit nějaký znak.

Typy situací kdy potřebujeme najít a nahradit obsah buněk v Excelu

V tomto článku budeme pracovat s příkladem když jsme si vyexportovali tržby za produkty (jablka, hrušky a maliny) viz níže na obrázku. Problém je, že náš podnikový systém nám data vyexportoval s nějakými prefixy.

Na obrázku uvidíte 3 situace se kterými se můžete potkat a každá má trošku jiné řešení:

  • Příklad 1 (Sloupec B) – Prefix je pro všechny řádky stejný => nejjednodušší situace na vyřešení
  • Příklad 2 (Sloupec C) – Prefix je pro každý řádek jiný (má jiné číslo a poslední znak), ale má stejnou délku (budeme chtít odstranit 6 znaků)
  • Příklad 3 (Sloupec F) – Bystré oko si všimne, že prefixy jsou pro každý řádek jiné a mají jinou délku. Nicméně slovo, které hledáme se vždy nachází za znakem “_” a toho využijeme

Cílem příkladu je vymyslet způsob, jak u všech sloupců odstranit nepotřebné znaky před jablky, hruškami a malinami.

zadani prikladu najit a nahradit v excelu_3

Příklad 1 – Odstranění textu z buněk pomocí najít a nahradit

Řešení příkladu 1 – pokud mají znaky, které chceme z buněk odstranit stejný tvar, tak nejjednodušší je stisknutím CTRL+H vyvolat okno pomocí kterého můžeme požadovaný text najít a nahradit.

  • Nejprve musíme označit oblast, ve které chceme akci provést (Sloupec B)
  • Poté stiskneme CTRL+H
  • Hledáme text 0001_ a chceme ho nahradit prázdnou hodnotou

Po potvrzení prefix zmizí.

Excel najit a nahradit

Příklad 2 – Odstranění textu, který je různý, ale má stejnou délku

Řešení příkladu 2 – Pokud víme, že náš text má stále stejnou délku (6 znaků), ta můžeme použít funkci =ČÁST (anglicky =MID). Pomocí této funkce umíme exceu říct, že chceme z textu vzít pouze 7. znak a všechny následující znaky a prvních 6 znaků tak vynecháme.

Vzorec pro první řádek tedy bude vypadat =MID(C4;6;1000) a výsledkem je hodnota Jablka. Potažením za ouško pořešíme všechny řádky.

Excel najit a nahradit text se stejnou délkou

Příklad 3 – Odstranění textu do určitého znaku (v našem případě “_”)

Řešení příkladu 3 – Toto je relativně nejsložitější situace, ale samozřejmě má poměrně jednoduché řešení pomocí najít a nahradit:

  • Nejprve je potřeba vyhledat znak “_” pomocí funkce =NAJÍT (v angličtině =FIND)
  • a pokud známe pozici znaku “_”, tak pomočí funkce =ČÁST (v angličtině =MID) vezmeme pouzy ty znaky, které následují po zjištěném počtu znaků (podobně jako u příkladu 2)

Vzorec pro první řádek vypadá takto =MID(F4;FIND(“_”;F4)+1;1000) a výsledkem je hodnota Jablka, POužili jsme opět funkci =ČÁST  (MID) jako v předchozím případě, ale jako 2 argument jsme zadali funkci =NAJÍT (FIND), která vyhledala pozici znaku “_”.

Takto jsme to museli udělat, protože pozice znaku “_” byla pro každou hodnotu odlišná a nemohli jsme ji tedy zadat natvrdo jako v příkladu 2 kdy jsme zadávali pro všechny řádky hodnotu 6.

Najít a nahradit text od určitého znaku

V praxi se můžete dostat samozřejmě i do jiných situací (někdy i neřešitelných nebo řešitelných obtížně). Obecně platí, že je vždycky důležité se na data podívat a zjistit v nich nějaký patern podobně jako jsme to udělali v příkladech viz výše.

Excel sešit stahujte -> najit a nahradit v excelu.xlsx

4.5/5 - (4 votes)

Jan Zedníček - Data & Finance

Jmenuji se Honza Zedníček a působím jako freelancer. Tuto práci dělám pro různé firmy již přes 10 let. Před tím jsem dlouhou dobu pracoval také jako BI developer, finanční controller, analytik a krátce jako finanční manažer. Vše pro společnosti z oblasti IT, bankovnictví a výroby. Po práci si rád zahraju tenis, volejbal, šachy, zajdu do posilovny a rád ochutnávám dobré rumy. Svoje znalosti se snažím zapisovat na tento web - abych je nezapomněl a sloužily i někomu dalšímu.

=> Přihlašte se do naší Excel facebook skupiny (1.4k+ členů), kde si pomáháme Excel CZ/SK diskuse »
=> Pokud vám článek pomohl, sdílejte ho nebo mě uveďte na vašich webových stránkách jako užitečný zdroj informací

3 comments on “Excel | Najít a nahradit (odstranit) obsah buněk s určitým textem + příklad

  1. Dobrý den, dá se funkce “Najdi a nahraď” použít v komentářích v Excelu? Funguje mi v komentářích pouze funkce Najdi.

  2. Pěkný článek, možná by čtenáře zajímala varianta, kdy v jedné tabulce mám nějaký text s nějakou formou odkazů/dat a potřebuji je změnit za jiná a v druhé tabulce mám např. “překladovou tabulku”
    staré URL; nové URL
    staré URL2; nové URL2
    staré URL3; nové URL3
    Jak jednuduše v textu vyhledat a zaměnit data dle tohoto klíče?

Leave a Reply

Your email address will not be published. Required fields are marked *