Excel formula: Normalizirajte tekst -

Sadržaj

Generička formula

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

Sažetak

Da biste uklonili dio prirodne složenosti teksta (interpunkcijske trake, normalizirajte mala i velika slova, uklonite suvišne razmake), možete koristiti formulu koja se temelji na funkciji ZAMJENA, uz pomoć funkcija TRIM i LOWER.

Kontekst

Ponekad trebate ukloniti neke varijable teksta prije druge obrade. Jedan je primjer kada želite brojati određene riječi unutar većih tekstualnih nizova. Budući da Excel ne pruža podršku za regularne izraze, ne možete konstruirati precizna podudaranja. Na primjer, ako želite izbrojati koliko se puta riječ "lisica" pojavi u ćeliji, na kraju ćete izbrojiti "lisice". Možete tražiti "lisica" (s razmakom), ali to neće uspjeti s "lisica" ili "lisica". Jedno zaobilazno rješenje je prvo pojednostaviti tekst pomoću formule u pomoćnom stupcu, a zatim izvoditi računa na pojednostavljenoj verziji. Primjer na ovoj stranici pokazuje jedan od načina za to.

Obrazloženje

Formula prikazana u ovom primjeru koristi niz ugniježđenih SUBSTITUTE funkcija za uklanjanje zagrada, crtica, dvotočaka, polutačaka, uskličnika, zareza i točaka. Proces se izvodi iznutra prema van, pri čemu svaki ZAMJENA zamjenjuje jedan znak jednim razmakom, a zatim se predaje sljedećem ZAMJENI. Unutarnji ZAMJENA uklanja lijeve zagrade, a rezultat se predaje sljedećem ZAMJENI, koji uklanja desne zagrade itd.

U donjoj verziji dodani su prijelomi redaka radi čitljivosti i radi lakšeg uređivanja zamjena. Excel ne mari za prijelome redaka u formulama, tako da formulu možete koristiti takvu kakva jest.

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

Nakon završetka svih zamjena, rezultat se vodi kroz TRIM da bi se normalizirali razmaci, a zatim funkcija LOWER za prisiljavanje cijelog teksta na mala slova.

Napomena: Morat ćete prilagoditi stvarne zamjene tako da odgovaraju vašim podacima.

Dodavanje vodećeg i pratećeg prostora

U nekim ćete slučajevima možda htjeti dodati razmak na početak i kraj očišćenog teksta. Na primjer, ako želite precizno brojati riječi, možda ćete htjeti potražiti riječ okruženu razmacima (tj. Potražiti "lisica", "karta") kako biste izbjegli lažna podudaranja. Da biste dodali vodeći i prateći razmak, samo spojite razmak ("") na početak i kraj: