Fórmula genérica
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),xtable,2,0)))
Resumo
Para traduzir letras em uma string em números, você pode usar uma fórmula de matriz baseada nas funções TEXTJOIN e VLOOKUP, com uma tabela de tradução definida para fornecer as pesquisas necessárias. No exemplo mostrado, a fórmula em C5 é:
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0)))
onde "xtable" é o intervalo nomeado E5: F10.
Observação: esta é uma fórmula de matriz e deve ser inserida com control + shift + enter.
Explicação
Basicamente, essa fórmula usa uma operação de matriz para gerar uma matriz de letras do texto de entrada, traduz cada letra individualmente em um número, depois reúne todos os números novamente e retorna a saída como uma string.
Para analisar a string de entrada em uma matriz ou letras, usamos as funções MID, ROW, LEN e INDIRECT como esta:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
LEN retorna o comprimento do texto de entrada, que é concatenado em "1:" e entregue a INDIRETO como texto. INDIRETO avalia o texto como uma referência de linha, e a função ROW retorna uma matriz de números para MID:
MID(B5,(1;2;3),1)
O MID então extrai um caractere para cada posição inicial e temos:
=TEXTJOIN("",1,VLOOKUP(T(IF(1,("a";"b";"c"))),xtable,2,0))
Basicamente, estamos pedindo PROCV para encontrar uma correspondência para "a", "b" e "c" ao mesmo tempo. Por razões obscuras, precisamos "desreferenciar" este array de uma maneira particular usando as funções T e IF. Após a execução de VLOOKUP, temos:
=TEXTJOIN("",1,(9;4;6))
e TEXTJOIN retorna a string "946".
Produza um número
Para produzir um número como resultado final (em vez de uma string), adicione zero. A operação matemática forçará a string a um número.
Soma dos números
Para somar os números em vez de listá-los, você pode substituir TEXTJOIN por SUM desta forma:
=SUM(VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0))
Observação: a função TEXTJOIN foi introduzida por meio do programa de assinatura do Office 365 em 2018.
