Generisk formel
=SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2))
Resumé
For at finde og erstatte flere værdier med en formel kan du rede flere SUBSTITUTE-funktioner sammen og føde i find / udskift par fra en anden tabel ved hjælp af INDEX-funktionen. I det viste eksempel udfører vi 4 separate find- og erstatningsoperationer. Formlen 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 "find" er det navngivne område E5: E8, og "erstat" er det navngivne område F5: F8. Se nedenfor for information om, hvordan du gør denne formel lettere at læse.
Forord
Der er ingen indbygget formel til at køre en række find- og erstatningsoperationer i Excel, så dette er en "koncept" -formel, der viser en tilgang. Teksten, du skal kigge efter og erstatte med, gemmes direkte på regnearket i en tabel og hentes med INDEX-funktionen. Dette gør løsningen "dynamisk" - nogen af disse værdier ændres, resultater opdateres med det samme. Der er naturligvis ikke noget krav om at bruge INDEX; du kan hårdkode værdier i formlen, hvis du foretrækker det.
Forklaring
I kernen bruger formlen SUBSTITUTE-funktionen til at udføre hver substitution med dette grundlæggende mønster:
=SUBSTITUTE(text,find,replace)
"Tekst" er den indkommende værdi, "find" er den tekst, der skal søges efter, og "erstat" er den tekst, der skal erstattes med. Teksten, du skal kigge efter og erstatte med, er gemt i tabellen til højre i området E5: F8, et par pr. Række. Værdierne til venstre er i det navngivne område "find", og værdierne til højre er i det navngivne område "erstatt". INDEX-funktionen bruges til at hente både "find" -teksten og "erstat" -teksten som denne:
INDEX(find,1) // first "find" value INDEX(replace,1) // first "replace" value
Så for at køre den første udskiftning (se efter "rød", erstat med "lyserød") bruger vi:
=SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1))
I alt kører vi fire separate erstatninger, og hver efterfølgende UDSKIFTER begynder med resultatet fra den forrige UDSKIFTER:
=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 læsbarhed
Du vil bemærke, at denne form for indlejret formel er ret vanskelig at læse. Ved at tilføje linjeskift kan vi gøre formlen meget lettere at læse og vedligeholde:
= 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))
Formelbjælken i Excel ignorerer ekstra hvidt mellemrum og linjeskift, så ovenstående formel kan indsættes direkte:
Forresten er der en tastaturgenvej til at udvide og skjule formellinjen.
Flere udskiftninger
Flere rækker kan tilføjes til tabellen for at håndtere flere find / udskift par. Hver gang et par tilføjes, skal formlen opdateres for at inkludere det nye par. Det er også vigtigt at sikre, at de navngivne områder (hvis du bruger dem) opdateres, så de inkluderer nye værdier efter behov. Alternativt kan du bruge en ordentlig Excel-tabel til dynamiske områder i stedet for navngivne områder.
Andre anvendelser
Den samme fremgangsmåde kan bruges til at rydde op ved at "strippe" tegnsætning og andre symboler fra tekst med en række udskiftninger. For eksempel viser formlen på denne side, hvordan man renser og omformaterer telefonnumre.
