Un gadget dans Joindre.Texte()

L’article précédent nous a montré comment, en utilisant la fonction Joindre.Texte(), nous pouvions demander à une simple formule – assistée il est vrai d’une validation matricielle ! – de faire, en créant une liste, tout un travail correspondant normalement à une boucle de calcul.

On peut seulement reprocher à cette liste le fait d’utiliser « M » et « F » au lieu de « Mr. » et « Mme ». Essayez d’imaginer comment vous pourriez arriver à ce résultat, tel qu’on peut le voir dans la seconde image ci-dessous :

J’y suis parvenu à l’aide d’une fonction très peu utilisée par la grande majorité des utilisateurs d’Excel, et même complètement ignorée de la plupart d’entre eux, la fonction Substitue(). L’astuce consiste à insérer la formule présentée dans l’article précédent à l’intérieur de deux appels à Substitue(), l’un pour remplacer les « M » par « Mr. » et l’autre les « F » par « Mme ».

Voici donc la formule de la cellule I6, reproduite ensuite vers le bas, dans laquelle nous avons mis en rouge ce qui est nouveau :

=substitue(substitue(joindre.texte(" - ";;SI($D$6:$D$30<>H6;"";$A$6:$A$30&" "&$B$6:$B$30&" "&$C$6:$C$30));"M ";"Mr. ");"F ";"Mme. ")

Remarque 1 – N’oubliez pas de valider cette formule comme formula matricielle, validée avec [Ctrl]-[Maj]-[Entrée].

La fonction substitue() sert à remplacer, y compris plusieurs fois dans la même cellule, une chaîne de caractères par une autre chaîne de caractères.

Remarque 2 – Faites attention aux jokers « ? » et « * » qui ne se comportent pas comme l’on pourrait s’y attendre avec cette fonction. Supposons qu’une cellule contienne Alfred et que l’on écrive dans une autre cellule =substitue(cellule;"f?";"g?") ou =substitue(cellule;"f*";"g*") : le résultat restera « Alfred » –  et non « Algred » – dans les deux cas !

Lors de mes interventions sur les modèles de mes clients, j’ai constaté plus d’une fois que des solutions compliquées avaient été mises en œuvre là où de simples substitue() auraient résolu le problème de façon bien plus rapide et élégante.