Generisk formel
=SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2))
Sammendrag
For å finne og erstatte flere verdier med en formel, kan du hekke flere SUBSTITUTE-funksjoner sammen, og mate inn finn / erstatt par fra en annen tabell ved hjelp av INDEX-funksjonen. I eksemplet som er vist, utfører vi fire separate søke- og erstatningsoperasjoner. Formelen i G5 er:
=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))
hvor "finn" er det navngitte området E5: E8, og "erstatt" er det navngitte området F5: F8. Se nedenfor for informasjon om hvordan du gjør denne formelen lettere å lese.
Forord
Det er ingen innebygd formel for å kjøre en serie finne og erstatte operasjoner i Excel, så dette er en "konsept" formel for å vise en tilnærming. Teksten du skal se etter og erstatte med, lagres direkte på regnearket i en tabell og hentes med INDEX-funksjonen. Dette gjør løsningen "dynamisk" - noen av disse verdiene endres, resultatene oppdateres umiddelbart. Det er selvfølgelig ikke noe krav om å bruke INDEX; du kan kode verdiene i formelen hvis du foretrekker det.
Forklaring
I kjernen bruker formelen SUBSTITUTE-funksjonen til å utføre hver erstatning, med dette grunnleggende mønsteret:
=SUBSTITUTE(text,find,replace)
"Tekst" er den innkommende verdien, "finn" er teksten du skal se etter, og "erstatt" er teksten du skal erstatte med. Teksten du skal se etter og erstatte med, er lagret i tabellen til høyre, i området E5: F8, ett par per rad. Verdiene til venstre er i det navngitte området "finn" og verdiene til høyre er i det navngitte området "erstatt". INDEX-funksjonen brukes til å hente både "finn" -teksten og "erstatt" -teksten slik:
INDEX(find,1) // first "find" value INDEX(replace,1) // first "replace" value
Så for å kjøre den første erstatningen (se etter "rød", erstatt med "rosa") bruker vi:
=SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1))
Totalt kjører vi fire separate erstatninger, og hver påfølgende SUBSTITUT begynner med resultatet fra forrige SUBSTITUT:
=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))
Linjeskift for lesbarhet
Du vil merke at denne typen nestede formel er ganske vanskelig å lese. Ved å legge til linjeskift kan vi gjøre formelen mye lettere å lese og vedlikeholde:
= 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))
Formellinjen i Excel ignorerer ekstra hvite mellomrom og linjeskift, slik at formelen ovenfor kan limes inn direkte:
Forresten er det en hurtigtast for å utvide og skjule formellinjen.
Flere bytter
Flere rader kan legges til tabellen for å håndtere flere finne / erstatte par. Hver gang et par legges til, må formelen oppdateres for å inkludere det nye paret. Det er også viktig å sørge for at de navngitte områdene (hvis du bruker dem) er oppdatert slik at de inkluderer nye verdier etter behov. Alternativt kan du bruke en skikkelig Excel-tabell for dynamiske områder, i stedet for navngitte områder.
Andre bruksområder
Den samme tilnærmingen kan brukes til å rydde opp tekst ved å "strippe" tegnsetting og andre symboler fra tekst med en rekke erstatninger. For eksempel viser formelen på denne siden hvordan du rengjør og omformaterer telefonnumre.
