Generička formula
=SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2))
Sažetak
Da biste pronašli i zamijenili više vrijednosti formulom, možete ugnijezditi više funkcija ZAMJENA i uvesti parove za pronalaženje / zamjenu iz druge tablice pomoću funkcije INDEX. U prikazanom primjeru izvodimo 4 odvojene operacije pronalaženja i zamjene. Formula u G5 je:
=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))
gdje je "pronađi" imenovani raspon E5: E8, a "nadomjesti" imenovani raspon F5: F8. U nastavku pogledajte informacije o tome kako olakšati čitanje ove formule.
Predgovor
Ne postoji ugrađena formula za pokretanje niza operacija pronalaženja i zamjene u Excelu, pa je ovo "konceptna" formula koja pokazuje jedan pristup. Tekst koji treba potražiti i zamijeniti pohranjuje se izravno na radni list u tablici i preuzima pomoću funkcije INDEX. To rješenje čini "dinamičnim" - bilo koja od ovih vrijednosti se mijenja, a rezultati se odmah ažuriraju. Naravno, ne postoji zahtjev za korištenjem INDEX-a; ako želite, možete tvrdo kodirati vrijednosti u formulu.
Obrazloženje
U osnovi, formula koristi funkciju ZAMJENA za izvođenje svake zamjene, s ovim osnovnim uzorkom:
=SUBSTITUTE(text,find,replace)
"Tekst" je dolazna vrijednost, "pronađi" je tekst koji treba potražiti, a "zamijeni" je tekst kojim se zamjenjuje. Tekst koji treba potražiti i zamijeniti pohranjen je u tablici s desne strane, u rasponu E5: F8, po jedan par u retku. Vrijednosti s lijeve strane nalaze se u imenovanom rasponu "pronađi", a vrijednosti s desne strane nalaze se u imenovanom rasponu "zamijeni". Funkcija INDEX koristi se za dohvaćanje i teksta "pronađi" i "zamijeni" ovako:
INDEX(find,1) // first "find" value INDEX(replace,1) // first "replace" value
Dakle, za pokretanje prve zamjene (potražite "crvenu", zamijenite "ružičastom") koristimo:
=SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1))
Ukupno izvodimo četiri zasebne zamjene i svaka sljedeća ZAMJENA započinje rezultatom iz prethodne ZAMJENE:
=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))
Prekidi redaka radi čitljivosti
Primijetit ćete da je ovakvu ugniježđenu formulu prilično teško pročitati. Dodavanjem preloma retka formulu možemo učiniti puno lakšom za čitanje i održavanje:
= 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))
Traka formule u Excelu ignorira dodatni razmak i prelome redaka, tako da se gornja formula može izravno zalijepiti:
Inače, postoji tipkovni prečac za proširivanje i sažimanje trake s formulama.
Više zamjena
U tablicu se može dodati više redaka za obradu više parova pronalaženja / zamjene. Svaki put kad se doda par, formulu treba ažurirati kako bi uključivala novi par. Također je važno osigurati da se imenovani rasponi (ako ih koristite) ažuriraju tako da uključuju nove vrijednosti po potrebi. Alternativno biste za dinamičke raspone mogli koristiti odgovarajuću Excel tablicu, umjesto imenovanih raspona.
Ostale namjene
Isti se pristup može koristiti za čišćenje teksta "uklanjanjem" interpunkcije i ostalih simbola iz teksta uz niz zamjena. Na primjer, formula na ovoj stranici pokazuje kako očistiti i preoblikovati telefonske brojeve.
