Formula Excel: Normalizați textul

Cuprins

Formula generică

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

rezumat

Pentru a elimina o parte din complexitatea naturală a textului (punctuația benzii, normalizați majusculele, eliminați spațiile suplimentare) puteți utiliza o formulă bazată pe funcția SUBSTITUTE, cu ajutorul funcțiilor TRIM și LOWER.

Context

Pot exista momente în care trebuie să eliminați o parte din variabilitatea textului înainte de altă procesare. Un exemplu este atunci când doriți să numărați cuvinte specifice în șiruri de text mai mari. Deoarece Excel nu oferă suport pentru expresii regulate, nu puteți construi potriviri precise. De exemplu, dacă doriți să numărați de câte ori apare cuvântul „vulpe” într-o celulă, veți ajunge să numărați „vulpi”. Puteți căuta „vulpe” (cu un spațiu), dar aceasta va eșua cu „vulpe” sau „vulpe”. O soluție este simplificarea textului mai întâi cu o formulă într-o coloană de ajutor, apoi rularea contează pe versiunea simplificată. Exemplul de pe această pagină arată o modalitate de a face acest lucru.

Explicaţie

Formula prezentată în acest exemplu folosește o serie de funcții SUBSTITUTE imbricate pentru a elimina parantezele, cratimele, punctele, punctele și virgulele, semnele de exclamare, virgulele și punctele. Procesul se desfășoară din interior spre exterior, fiecare SUBSTITUT înlocuind un personaj cu un singur spațiu, apoi predând următorului SUBSTITUT. Cea mai interioară SUBSTITUTE elimină parantezele din stânga, iar rezultatul este predat următoarei SUBSTITUTE, care elimină parantezele din dreapta, și așa mai departe.

În versiunea de mai jos, au fost adăugate întreruperi de linie pentru lizibilitate și pentru a facilita editarea înlocuirilor. Excel nu-i pasă de întreruperile de linie din formule, deci puteți utiliza formula așa cum este.

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

După ce toate substituțiile sunt complete, rezultatul este rulat prin TRIM pentru a normaliza spațiile, apoi funcția LOWER pentru a forța tot textul să scadă cu minuscule.

Notă: va trebui să ajustați înlocuirile reale pentru a se potrivi cu datele dvs.

Adăugarea unui spațiu principal și final

În unele cazuri, poate doriți să adăugați un caracter spațial la începutul și la sfârșitul textului curățat. De exemplu, dacă doriți să numărați cuvintele cu precizie, vă recomandăm să căutați cuvântul înconjurat de spații (adică să căutați „vulpe”, „hartă”) pentru a evita potrivirile false. Pentru a adăuga un spațiu principal și final, concatenează un spațiu ("") la început și la sfârșit: