Formule générique
=SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2))
Sommaire
Pour rechercher et remplacer plusieurs valeurs par une formule, vous pouvez imbriquer plusieurs fonctions SUBSTITUTE ensemble et alimenter en paires rechercher / remplacer à partir d'une autre table à l'aide de la fonction INDEX. Dans l'exemple illustré, nous effectuons 4 opérations de recherche et de remplacement distinctes. La formule dans G5 est:
=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))
où "find" est la plage nommée E5: E8 et "replace" est la plage nommée F5: F8. Voir ci-dessous pour savoir comment rendre cette formule plus facile à lire.
Préface
Il n'y a pas de formule intégrée pour exécuter une série d'opérations de recherche et de remplacement dans Excel, c'est donc une formule «concept» pour montrer une approche. Le texte à rechercher et à remplacer est stocké directement dans la feuille de calcul dans une table et récupéré avec la fonction INDEX. Cela rend la solution «dynamique» - n'importe laquelle de ces valeurs est modifiée, les résultats sont mis à jour immédiatement. Bien entendu, il n'est pas nécessaire d'utiliser INDEX; vous pouvez coder en dur les valeurs dans la formule si vous préférez.
Explication
À la base, la formule utilise la fonction SUBSTITUTE pour effectuer chaque substitution, avec ce modèle de base:
=SUBSTITUTE(text,find,replace)
«Texte» est la valeur entrante, «trouver» est le texte à rechercher et «remplacer» est le texte par lequel remplacer. Le texte à rechercher et à remplacer est stocké dans le tableau de droite, dans la plage E5: F8, une paire par ligne. Les valeurs de gauche se trouvent dans la plage nommée «find» et les valeurs de droite sont dans la plage nommée «replace». La fonction INDEX est utilisée pour récupérer à la fois le texte "rechercher" et le texte "remplacer" comme ceci:
INDEX(find,1) // first "find" value INDEX(replace,1) // first "replace" value
Donc, pour exécuter la première substitution (recherchez "rouge", remplacez par "rose") nous utilisons:
=SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1))
Au total, nous exécutons quatre substitutions distinctes, et chaque SUBSTITUT suivant commence par le résultat du SUBSTITUT précédent:
=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))
Sauts de ligne pour plus de lisibilité
Vous remarquerez que ce type de formule imbriquée est assez difficile à lire. En ajoutant des sauts de ligne, nous pouvons rendre la formule beaucoup plus facile à lire et à maintenir:
= 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))
La barre de formule dans Excel ignore les espaces blancs supplémentaires et les sauts de ligne, de sorte que la formule ci-dessus peut être collée directement:
À propos, il existe un raccourci clavier pour développer et réduire la barre de formule.
Plus de substitutions
Plus de lignes peuvent être ajoutées à la table pour gérer plus de paires de recherche / remplacement. Chaque fois qu'une paire est ajoutée, la formule doit être mise à jour pour inclure la nouvelle paire. Il est également important de s'assurer que les plages nommées (si vous les utilisez) sont mises à jour pour inclure de nouvelles valeurs si nécessaire. Vous pouvez également utiliser un tableau Excel approprié pour les plages dynamiques, au lieu de plages nommées.
Autres utilisations
La même approche peut être utilisée pour nettoyer le texte en "enlevant" la ponctuation et d'autres symboles du texte avec une série de substitutions. Par exemple, la formule de cette page montre comment nettoyer et reformater les numéros de téléphone.
