Excel vzorec: Normalizovat text -

Obsah

Obecný vzorec

=LOWER(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"("," "),")"," "),"-"," "),":"," "),";"," "),"!"," "),","," "),"."," ")))

souhrn

Chcete-li odstranit část přirozené složitosti textu (interpunkční proužky, normalizovat velká a malá písmena, odstranit mezery), můžete použít vzorec založený na funkci SUBSTITUTE s pomocí funkcí TRIM a LOWER.

Kontext

Mohou nastat situace, kdy budete muset před dalším zpracováním odstranit část variability textu. Jedním z příkladů je, když chcete počítat konkrétní slova do větších textových řetězců. Protože Excel neposkytuje podporu pro regulární výrazy, nemůžete vytvářet přesné shody. Například pokud chcete spočítat, kolikrát se slovo „liška“ objeví v buňce, nakonec spočítáte „lišky“. Můžete hledat „lišku“ (s mezerou), ale to selže u „lišky“ nebo „lišky“. Jedním řešením je nejprve zjednodušit text pomocí vzorce ve sloupci pomocníka, poté se počítá spuštění ve zjednodušené verzi. Příklad na této stránce ukazuje jeden způsob, jak toho dosáhnout.

Vysvětlení

Vzorec zobrazený v tomto příkladu používá řadu vnořených funkcí SUBSTITUTE k odstranění závorek, pomlček, dvojteček, středníků, vykřičníků, čárek a teček. Proces běží zevnitř ven, přičemž každý SUBSTITUTE nahradí jeden znak jednou mezerou a poté se předá další SUBSTITUTE. Vnitřní nejvíce SUBSTITUTE odstraní levé závorky a výsledek se předá další SUBSTITUTE, která odebere pravé závorky atd.

Ve verzi níže byly přidány konce řádků kvůli čitelnosti a pro snazší úpravy náhrad. Excel se nestará o zalomení řádků ve vzorcích, takže můžete použít vzorec tak, jak je.

= LOWER( TRIM( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( A1, "("," "), ")"," "), "-"," "), ":"," "), ";"," "), "!"," "), ","," "), "."," ")))

Po dokončení všech substitucí je výsledek spuštěn pomocí funkce TRIM k normalizaci mezer, poté funkce LOWER vynutí veškerý text na malá písmena.

Poznámka: Budete muset upravit skutečné náhrady, aby vyhovovaly vašim datům.

Přidání úvodního a koncového prostoru

V některých případech můžete přidat mezeru na začátek a konec vyčištěného textu. Chcete-li například přesně spočítat slova, můžete hledat slovo obklopené mezerami (tj. Vyhledat „liška“, „mapa“), abyste se vyhnuli falešným shodám. Chcete-li přidat úvodní a koncový prostor, stačí zřetězit mezeru ("") na začátek a konec: