Fórmula genérica
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),xtable,2,0)))
Resumen
Para traducir letras en una cadena a números, puede usar una fórmula de matriz basada en las funciones TEXTJOIN y VLOOKUP, con una tabla de traducción definida para proporcionar las búsquedas necesarias. En el ejemplo que se muestra, la fórmula en C5 es:
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0)))
donde "xtable" es el rango con nombre E5: F10.
Nota: esta es una fórmula de matriz y debe ingresarse con control + shift + enter.
Explicación
En esencia, esta fórmula utiliza una operación de matriz para generar una matriz de letras a partir del texto de entrada, traduce cada letra individualmente a un número, luego une todos los números nuevamente y devuelve la salida como una cadena.
Para analizar la cadena de entrada en una matriz o letras, usamos funciones MID, ROW, LEN e INDIRECT como esta:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
LEN devuelve la longitud del texto de entrada, que se concatena a "1:" y se pasa a INDIRECTO como texto. INDIRECTO evalúa el texto como una referencia de fila, y la función FILA devuelve una matriz de números a MID:
MID(B5,(1;2;3),1)
MID luego extrae un carácter para cada posición inicial y tenemos:
=TEXTJOIN("",1,VLOOKUP(T(IF(1,("a";"b";"c"))),xtable,2,0))
Básicamente, le pedimos a BUSCARV que busque una coincidencia para "a", "b" y "c" al mismo tiempo. Por razones oscuras, necesitamos "desreferenciar" esta matriz de una manera particular usando las funciones T e IF. Después de que se ejecuta VLOOKUP, tenemos:
=TEXTJOIN("",1,(9;4;6))
y TEXTJOIN devuelve la cadena "946".
Salida de un número
Para generar un número como resultado final (en lugar de una cadena), agregue cero. La operación matemática convertirá la cadena en un número.
Suma de números
Para sumar los números en lugar de enumerarlos, puede reemplazar TEXTJOIN con SUM de esta manera:
=SUM(VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0))
Nota: la función TEXTJOIN se introdujo a través del programa de suscripción de Office 365 en 2018.
