Generieke formule
=SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2))
Samenvatting
Om meerdere waarden te zoeken en te vervangen door een formule, kunt u meerdere SUBSTITUTE-functies samen nesten, en zoek / vervang-paren uit een andere tabel invoeren met de functie INDEX. In het getoonde voorbeeld voeren we 4 afzonderlijke zoek- en vervangbewerkingen uit. De formule in G5 is:
=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))
waarbij "vind" het benoemde bereik E5: E8 is en "vervang" het benoemde bereik F5: F8. Zie hieronder voor informatie over hoe u deze formule beter leesbaar kunt maken.
Voorwoord
Er is geen ingebouwde formule voor het uitvoeren van een reeks zoek- en vervangbewerkingen in Excel, dus dit is een "concept" -formule om één benadering te laten zien. De tekst waarnaar moet worden gezocht en waarmee moet worden vervangen, wordt direct op het werkblad in een tabel opgeslagen en opgehaald met de functie INDEX. Dit maakt de oplossing "dynamisch" - elk van deze waarden wordt gewijzigd, de resultaten worden onmiddellijk bijgewerkt. Het is natuurlijk niet vereist om INDEX te gebruiken; u kunt waarden in de formule hard coderen als u dat wilt.
Uitleg
In de kern gebruikt de formule de SUBSTITUTE-functie om elke substitutie uit te voeren, met dit basispatroon:
=SUBSTITUTE(text,find,replace)
"Tekst" is de binnenkomende waarde, "vind" is de tekst waarnaar moet worden gezocht en "vervangen" is de tekst die moet worden vervangen. De tekst waarnaar moet worden gezocht en waarmee moet worden vervangen, wordt opgeslagen in de tabel rechts, in het bereik E5: F8, één paar per rij. De waarden aan de linkerkant bevinden zich in het benoemde bereik "vind" en de waarden aan de rechterkant bevinden zich in het benoemde bereik "vervangen". De INDEX-functie wordt gebruikt om zowel de "zoek" tekst en de "vervang" tekst als volgt op te halen:
INDEX(find,1) // first "find" value INDEX(replace,1) // first "replace" value
Dus om de eerste vervanging uit te voeren (zoek naar "rood", vervang door "roze") gebruiken we:
=SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1))
In totaal voeren we vier afzonderlijke vervangingen uit, en elke volgende VERVANGING begint met het resultaat van de vorige VERVANGING:
=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))
Regelafbrekingen voor leesbaarheid
U zult merken dat dit soort geneste formule vrij moeilijk te lezen is. Door regeleinden toe te voegen, kunnen we de formule veel gemakkelijker leesbaar en onderhouden maken:
= 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))
De formulebalk in Excel negeert extra witruimte en regeleinden, dus de bovenstaande formule kan direct worden geplakt:
Overigens is er een sneltoets om de formulebalk uit en samen te vouwen.
Meer vervangingen
Er kunnen meer rijen aan de tabel worden toegevoegd om meer zoek / vervang-paren te verwerken. Elke keer dat een paar wordt toegevoegd, moet de formule worden bijgewerkt om het nieuwe paar op te nemen. Het is ook belangrijk om ervoor te zorgen dat de benoemde bereiken (als u ze gebruikt) worden bijgewerkt om indien nodig nieuwe waarden op te nemen. Als alternatief kunt u een goede Excel-tabel gebruiken voor dynamische bereiken, in plaats van benoemde bereiken.
Andere gebruiken
Dezelfde benadering kan worden gebruikt om tekst op te schonen door interpunctie en andere symbolen uit tekst te "strippen" met een reeks vervangingen. De formule op deze pagina laat bijvoorbeeld zien hoe u telefoonnummers opschoont en opnieuw formatteert.
