Generisk formel
=SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2))
Sammanfattning
För att hitta och ersätta flera värden med en formel kan du bygga flera SUBSTITUTE-funktioner tillsammans och mata in sök / ersätt par från en annan tabell med INDEX-funktionen. I exemplet som visas utför vi fyra separata sök- och ersättningsoperationer. Formeln i G5 är:
=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))
där "hitta" är det namngivna området E5: E8, och "ersätt" är det namngivna området F5: F8. Se nedan för information om hur du gör den här formeln lättare att läsa.
Förord
Det finns ingen inbyggd formel för att köra en serie sök- och ersättningsoperationer i Excel, så detta är en "koncept" -formel för att visa ett tillvägagångssätt. Texten att leta efter och ersätta med lagras direkt på kalkylbladet i en tabell och hämtas med INDEX-funktionen. Detta gör lösningen "dynamisk" - något av dessa värden ändras, resultaten uppdateras omedelbart. Naturligtvis finns det inget krav att använda INDEX; du kan hårda in värden i formeln om du föredrar det.
Förklaring
Kärnan använder formeln SUBSTITUTE-funktionen för att utföra varje substitution, med detta grundläggande mönster:
=SUBSTITUTE(text,find,replace)
"Text" är det inkommande värdet, "hitta" är texten du ska leta efter och "ersätt" är texten du ska ersätta med. Texten att leta efter och ersätta med lagras i tabellen till höger, i intervallet E5: F8, ett par per rad. Värdena till vänster ligger i det namngivna området "hitta" och värdena till höger ligger i det namngivna området "ersätt". INDEX-funktionen används för att hämta både "hitta" -texten och "ersätt" -texten så här:
INDEX(find,1) // first "find" value INDEX(replace,1) // first "replace" value
Så för att köra den första ersättningen (leta efter "röd", ersätt med "rosa") använder vi:
=SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1))
Totalt kör vi fyra separata utbyten, och varje efterföljande SUBSTITUT börjar med resultatet från föregående 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))
Linjeavbrott för läsbarhet
Du kommer att märka att den här typen av kapslad formel är ganska svår att läsa. Genom att lägga till radbrytningar kan vi göra formeln mycket lättare att läsa och underhålla:
= 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))
Formelfältet i Excel ignorerar extra vitt utrymme och radbrytningar, så formeln ovan kan klistras in direkt:
Förresten finns det en tangentbordsgenväg för att expandera och kollapsa formelfältet.
Fler utbyten
Fler rader kan läggas till i tabellen för att hantera fler sök / ersätt par. Varje gång ett par läggs till måste formeln uppdateras för att inkludera det nya paret. Det är också viktigt att se till att de angivna intervallen (om du använder dem) uppdateras för att inkludera nya värden efter behov. Alternativt kan du använda en ordentlig Excel-tabell för dynamiska intervall istället för namngivna intervall.
Andra användningsområden
Samma tillvägagångssätt kan användas för att rensa upp text genom att "strippa" skiljetecken och andra symboler från text med en serie substitutioner. Till exempel visar formeln på denna sida hur man rengör och omformaterar telefonnummer.
