Všeobecný vzorec
=LOWER(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"("," "),")"," "),"-"," "),":"," "),";"," "),"!"," "),","," "),"."," ")))
Zhrnutie
Na odstránenie prirodzenej zložitosti textu (interpunkcia prúžku, normalizácia malých a veľkých písmen, odstránenie medzery navyše) môžete použiť vzorec založený na funkcii SUBSTITUTE s pomocou funkcií TRIM a LOWER.
Kontext
Môže sa stať, že budete musieť pred ďalším spracovaním odstrániť časť variability textu. Jedným z príkladov je, keď chcete počítať konkrétne slová vo väčších textových reťazcoch. Pretože Excel neposkytuje podporu pre regulárne výrazy, nemôžete zostaviť presné zhody. Napríklad, ak chcete spočítať, koľkokrát sa v bunke objaví slovo „líška“, nakoniec spočítate „líšky“. Môžete hľadať „líšku“ (s medzerou), ale to zlyhá pri „líške“ alebo „líške“. Jedným z riešení je najskôr zjednodušiť text pomocou vzorca v pomocnom stĺpci, potom sa pri zjednodušenej verzii počíta počet. Príklad na tejto stránke ukazuje jeden zo spôsobov, ako to dosiahnuť.
Vysvetlenie
Vzorec zobrazený v tomto príklade používa sériu vnorených funkcií SUBSTITUTE na odstránenie zátvoriek, pomlčiek, dvojbodiek, bodkočiarok, výkričníkov, čiarok a bodiek. Proces beží zvnútra von, pričom každý SUBSTITUTE nahradzuje jeden znak jednou medzerou a potom sa odovzdáva ďalšiemu SUBSTITUTE. Vnútorná väčšina SUBSTITUTE odstráni ľavú zátvorku a výsledok sa odovzdá ďalšej SUBSTITUTE, ktorá odstráni pravú zátvorku atď.
Vo verzii nižšie boli pridané zalomenia riadkov kvôli čitateľnosti a uľahčeniu úprav náhrad. Excel sa nestará o zalomenie riadkov vo vzorcoch, takže môžete použiť vzorec tak, ako je.
= LOWER( TRIM( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( A1, "("," "), ")"," "), "-"," "), ":"," "), ";"," "), "!"," "), ","," "), "."," ")))
Po dokončení všetkých substitúcií sa výsledok spustí cez TRIM, aby sa normalizovali medzery, potom funkcia LOWER vynúti, aby sa všetok text písal malými písmenami.
Poznámka: Budete musieť upraviť skutočné náhrady tak, aby vyhovovali vašim údajom.
Pridanie popredného a posledného priestoru
V niektorých prípadoch možno budete chcieť na začiatok a koniec vyčisteného textu pridať znak medzery. Ak chcete napríklad presne spočítať slová, môžete vyhľadať slovo obklopené medzerami (tj. Vyhľadať výraz „fox“, „map“), aby ste sa vyhli nesprávnym zhodám. Ak chcete pridať úvodnú a koncovú medzeru, stačí spojiť medzeru ("") na jej začiatku a na konci:
=" "&formula&" "
Kde „vzorec“ je dlhší vzorec uvedený vyššie.
