Formula generică
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"-","")," ",""),".","")+0
rezumat
O modalitate de curățare și reformatare a numerelor de telefon este eliminarea tuturor caracterelor străine, apoi aplicarea formatului de număr de telefon încorporat Excel.
Această formulă de mai sus folosește o serie de funcții SUBSTITUTE imbricate pentru a elimina spațiile, cratimele, punctele, parantezele și virgulele.
Va trebui să ajustați înlocuirile reale pentru a se potrivi cu datele dvs.
Explicaţie
Formula rulează din interior spre exterior, fiecare SUBSTITUT eliminând un caracter.
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.
Ori de câte ori utilizați funcția SUBSTITUTE, rezultatul va fi text. Deoarece nu puteți aplica un format de număr textului, trebuie să convertim textul într-un număr. O modalitate de a face acest lucru este să adăugați zero (+0), care convertește automat numerele în format text în numere în format numeric.
În cele din urmă, se aplică formatul numărului de telefon „Special” (coloana D).
Această pagină explică formatele de numere personalizate și cu multe exemple.
Truc pentru spațiul alb pentru o mai bună lizibilitate
Când cuibăriți mai multe funcții, poate fi dificil să citiți formula și să păstrați toate parantezele echilibrate. Excel nu-i pasă de spațiul alb suplimentar dintr-o formulă, deci puteți adăuga întreruperi de linie în formulă pentru a face formula mai lizibilă. De exemplu, formula de mai sus poate fi scrisă după cum urmează:
= SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( A1, "(",""), ")",""), "-",""), " ",""), ".","")
Rețineți că celula apare în mijloc, cu nume de funcții deasupra și înlocuiri mai jos. Acest lucru nu numai că facilitează citirea formulei, ci și facilitează adăugarea și eliminarea substituțiilor.
Puteți utiliza același truc pentru a face mai ușor de citit instrucțiunile IF imbricate.
