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

Poslední aktualizace:

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 často jiná.

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 => když text má stejný tvar

Ř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 poté 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í (nebo nahrazení) 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í:

  • 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

Excel | Najít a nahradit (odstranit) obsah buněk s určitým textem + příklad
5 (100%) 1 vote[s]

Jmenuji se Honza Zedníček a nejčastěji se se mnou v civilu můžete potkat v kancelářích Kentico Software v Brně, kde působím jako BI Developer. Částečně pracuji také jako freelancer. Tuto práci dělám pro různé firmy již přes 5 let, ale Kentico je moje srdcovka ♡. Před tím jsem pracoval jako finanční controller třeba v Aero Vodochody a Sberbank nebo jako manažer dluhopisového programu v investiční skupině Unicapital. 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 (působením rumu ^^) a sloužily i někomu dalšímu. Nebojte se položit dotaz nebo reagovat do komentáře. Přihlašte se do naší nové Excel facebook skupiny Excel CZ/SK diskuse »

Category: Excel funkce Excel návody

About Ing. Jan Zedníček - BI Developer, Finance controller

Jmenuji se Honza Zedníček a nejčastěji se se mnou v civilu můžete potkat v kancelářích Kentico Software v Brně, kde působím jako BI Developer. Částečně pracuji také jako freelancer. Tuto práci dělám pro různé firmy již přes 5 let, ale Kentico je moje srdcovka ♡. Před tím jsem pracoval jako finanční controller třeba v Aero Vodochody a Sberbank nebo jako manažer dluhopisového programu v investiční skupině Unicapital. 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 (působením rumu ^^) a sloužily i někomu dalšímu. Nebojte se položit dotaz nebo reagovat do komentáře. Přihlašte se do naší nové Excel facebook skupiny Excel CZ/SK diskuse »

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.