Formule générique
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),xtable,2,0)))
Sommaire
Pour traduire les lettres d'une chaîne en nombres, vous pouvez utiliser une formule matricielle basée sur les fonctions TEXTJOIN et VLOOKUP, avec une table de traduction définie pour fournir les recherches nécessaires. Dans l'exemple illustré, la formule en C5 est:
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0)))
où "xtable" est la plage nommée E5: F10.
Remarque: il s'agit d'une formule matricielle et doit être saisie avec Ctrl + Maj + Entrée.
Explication
Au fond, cette formule utilise une opération de tableau pour générer un tableau de lettres à partir du texte d'entrée, traduit chaque lettre individuellement en un nombre, puis joint à nouveau tous les nombres et renvoie la sortie sous forme de chaîne.
Pour analyser la chaîne d'entrée dans un tableau ou des lettres, nous utilisons les fonctions MID, ROW, LEN et INDIRECT comme ceci:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
LEN renvoie la longueur du texte d'entrée, qui est concaténé à "1:" et transmis à INDIRECT sous forme de texte. INDIRECT évalue le texte en tant que référence de ligne et la fonction ROW renvoie un tableau de nombres à MID:
MID(B5,(1;2;3),1)
MID extrait ensuite un caractère pour à chaque position de départ et nous avons:
=TEXTJOIN("",1,VLOOKUP(T(IF(1,("a";"b";"c"))),xtable,2,0))
Essentiellement, nous demandons à RECHERCHEV de trouver une correspondance pour "a", "b" et "c" en même temps. Pour des raisons obscures, nous devons "déréférencer" ce tableau d'une manière particulière en utilisant à la fois les fonctions T et IF. Après l'exécution de VLOOKUP, nous avons:
=TEXTJOIN("",1,(9;4;6))
et TEXTJOIN renvoie la chaîne "946".
Sortir un nombre
Pour afficher un nombre comme résultat final (au lieu d'une chaîne), ajoutez zéro. L'opération mathématique contraindra la chaîne en un nombre.
Numéros de somme
Pour additionner les nombres au lieu de les lister, vous pouvez remplacer TEXTJOIN par SUM comme ceci:
=SUM(VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0))
Remarque: la fonction TEXTJOIN a été introduite via le programme d'abonnement Office 365 en 2018.
