Avec la fonction SUBSTITUE, vous ne pouvez remplacer qu'une seule valeur par une autre valeur ...

Voici comment effectuer de multiples remplacements avec une seule formule et (un peu plus bas) comment créer une fonction nommée pour pouvoir ensuite le faire encore plus facilement :

=SUBSTITUE_TABLEAU(texte;tableau_remplacements)

Exemple d'utilisation

L'objectif ici est de retourner le texte de A2 après avoir effectué tous les remplacements définis dans le tableau D2:E8 :

Ce résultat peut être obtenu avec la formule suivante :

=REDUCE(A2;SEQUENCE(NBVAL(D2:D8));LAMBDA(t;n;SUBSTITUE(t;INDEX(D2:D8;n);INDEX(E2:E8;n))))

Cette formule a bien effectué tous les remplacements demandés.

Pour mieux comprendre, voici tout d'abord une version plus lisible de cette même formule :

=REDUCE(A2;SEQUENCE(NBVAL(D2:D8));LAMBDA(texte;numero;SUBSTITUE(texte;INDEX(D2:D8;numero);INDEX(E2:E8;numero))))

Décomposons maintenant cette formule.

La formule commence par la fonction REDUCE avec comme valeur initiale le texte en A2 :

=REDUCE(A2;

La fonction REDUCE va ensuite parcourir le tableau généré par la fonction SEQUENCE (qui est un simple tableau numéroté de 1 à 7, car le tableau D2:D8 contient 7 valeurs) et effectuer une action sur le texte en fonction de celui-ci :

SEQUENCE(NBVAL(D2:D8));

La fonction REDUCE va donc exécuter ici 7x la fonction LAMBDA dont les arguments sont : le texte à modifier et le numéro de remplacement (de 1 à 7) :

LAMBDA(texte;numero;

Et pour terminer la fonction SUBSTITUE va effectuer le remplacement de la ligne en cours :

SUBSTITUE(texte;INDEX(D2:D8;numero);INDEX(E2:E8;numero))))

Les valeurs utiles aux remplacements sont récupérées par la fonction INDEX dans le tableau en fonction du numéro en cours.

Fonction nommée

Pour simplifier son utilisation, vous pouvez créer une fonction nommée pour pouvoir ensuite l'utiliser très facilement :

=SUBSTITUE_TABLEAU(A2;D2:E8)

Pour le faire, ajoutez simplement une nouvelle fonction nommée, entrez les 2 arguments texte et tableau_remplacements puis la formule suivante :

=REDUCE(texte;SEQUENCE(LIGNES(tableau_remplacements));LAMBDA(t;n;SUBSTITUE(t;INDEX(tableau_remplacements;n;1);INDEX(tableau_remplacements;n;2))))

Cette formule est légèrement différente de la précédente (dans le but d'obtenir une fonction simple à 2 arguments).