Exceller

Rechercher et remplacer plusieurs valeurs

Find Replace Multiple Values

Formule Excel: rechercher et remplacer plusieurs valeursFormule générique
= SUBSTITUTE ( SUBSTITUTE (B5, INDEX (find,1), INDEX (replace,1)), INDEX (find,2), INDEX (replace,2))
Résumé

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ù «trouver» est le 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, il s'agit donc d'une formule «conceptuelle» 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 sûr, 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 sur la gauche sont dans 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 'find' et le texte 'replace' comme ceci:



une valeur de texte est également appelée:
 
 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:

comment calculer votre paiement hypothécaire dans excel
 
= 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:

Sauts de ligne ajoutés dans la barre de formule pour plus de lisibilité et de maintenance

Au fait, il y a 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 des plages nommées.

Autres utilisations

La même approche peut être utilisée pour nettoyer le texte en «supprimant» 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 .

Auteur Dave Bruns


^