Formula generică
=SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2))
rezumat
Pentru a găsi și a înlocui mai multe valori cu o formulă, puteți cuibra mai multe funcții SUBSTITUTE împreună și puteți alimenta perechile de căutare / înlocuire dintr-un alt tabel utilizând funcția INDEX. În exemplul prezentat, efectuăm 4 operațiuni de căutare și înlocuire separate. Formula din G5 este:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2)),INDEX(find,3),INDEX(replace,3)),INDEX(find,4),INDEX(replace,4))
unde „găsi” este intervalul numit E5: E8, iar „înlocuire” este intervalul numit F5: F8. Vedeți mai jos informații despre cum puteți face această formulă mai ușor de citit.
Prefaţă
Nu există o formulă încorporată pentru executarea unei serii de operații de căutare și înlocuire în Excel, deci aceasta este o formulă de „concept” pentru a arăta o abordare. Textul cu care trebuie căutat și înlocuit este stocat direct pe foaia de lucru dintr-un tabel și recuperat cu funcția INDEX. Acest lucru face ca soluția să fie „dinamică” - oricare dintre aceste valori sunt modificate, rezultatele se actualizează imediat. Desigur, nu este necesară utilizarea INDEX; puteți prefera codurile în formulă dacă preferați.
Explicaţie
La bază, formula folosește funcția SUBSTITUTE pentru a efectua fiecare substituție, cu acest model de bază:
=SUBSTITUTE(text,find,replace)
„Text” este valoarea de intrare, „find” este textul de căutat, iar „replace” este textul cu care se înlocuiește. Textul cu care trebuie căutat și înlocuit este stocat în tabelul din dreapta, în intervalul E5: F8, o pereche pe rând. Valorile din stânga se află în intervalul numit „găsiți”, iar valorile din dreapta se află în intervalul numit „înlocuiți”. Funcția INDEX este utilizată pentru a recupera atât textul „găsiți”, cât și textul „înlocuiți” astfel:
INDEX(find,1) // first "find" value INDEX(replace,1) // first "replace" value
Deci, pentru a rula prima înlocuire (căutați „roșu”, înlocuiți cu „roz”) folosim:
=SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1))
În total, executăm patru înlocuiri separate și fiecare SUBSTITUT următor începe cu rezultatul din SUBSTITUTUL anterior:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2)),INDEX(find,3),INDEX(replace,3)),INDEX(find,4),INDEX(replace,4))
Pauze de linie pentru lizibilitate
Veți observa că acest tip de formulă imbricată este destul de dificil de citit. Adăugând întreruperi de linie, putem face formula mult mai ușor de citit și întreținut:
= SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( B5, INDEX(find,1),INDEX(replace,1)), INDEX(find,2),INDEX(replace,2)), INDEX(find,3),INDEX(replace,3)), INDEX(find,4),INDEX(replace,4))
Bara de formule din Excel ignoră spațiul alb suplimentar și întreruperile de linie, astfel încât formula de mai sus poate fi lipită direct:
Apropo, există o comandă rapidă de la tastatură pentru extinderea și restrângerea barei de formule.
Mai multe înlocuiri
Mai multe rânduri pot fi adăugate la masă pentru a gestiona mai multe perechi de căutare / înlocuire. De fiecare dată când se adaugă o pereche, formula trebuie actualizată pentru a include noua pereche. De asemenea, este important să vă asigurați că intervalele numite (dacă le utilizați) sunt actualizate pentru a include noi valori, după cum este necesar. Alternativ, puteți utiliza un tabel Excel adecvat pentru intervale dinamice, în loc de intervale denumite.
Alte utilizări
Aceeași abordare poate fi utilizată pentru a curăța textul prin „eliminarea” punctuației și a altor simboluri din text cu o serie de substituții. De exemplu, formula de pe această pagină arată cum să curățați și să reformatați numerele de telefon.
