Fórmula genérica
(=TEXTJOIN("",TRUE,IFERROR(MID(A1,ROW(INDIRECT("1:100")),1)+0,"")))
Resumo
Para remover caracteres não numéricos de uma string de texto, você pode tentar esta fórmula experimental com base na função TEXTJOIN, uma nova função no Excel 2019. No exemplo mostrado, a fórmula em C5 é:
(=TEXTJOIN("",TRUE,IFERROR(MID(B5,ROW(INDIRECT("1:100")),1)+0,"")))
Observação: esta é uma fórmula de matriz e deve ser inserida com control + shift + enter.
Explicação
Trabalhando de dentro para fora, a fórmula MID é usada para extrair o texto em B5, um caractere por vez. A chave é a peça LINHA / INDIRETA:
ROW(INDIRECT("1:100"))
que gira uma matriz contendo 100 números como este:
(1,2,3,4,5,6,7,8… .99.100)
Nota: 100 representa o máximo de caracteres a serem processados. Mude para se adequar aos seus dados.
Este array vai para a função MID como o argumento start_num . Para num_chars , usamos 1.
A função MID retorna uma matriz como esta:
("1"; "0"; "0"; ""; "a"; "p"; "p"; "l"; "e"; "s"; ""; ""; ""; " "…)
(itens extras na matriz removidos para facilitar a leitura)
A este array, adicionamos zero. Este é um truque simples que força o Excel a tentar forçar o texto a um número. Valores numéricos de texto como "1", "2", "3", "4" etc. são convertidos, enquanto os valores não numéricos falham e geram um erro #VALUE. Usamos a função IFERROR para capturar esses erros e retornar uma string vazia (""), enquanto os valores numéricos passam pelo array. O resultado é uma matriz que contém apenas números e strings vazias:
(1; 0; 0; ""; ""; ""; ""; "";….)
Finalmente, este resultado de matriz vai para a função TEXTJOIN como o argumento text1 . Para delimitador , usamos uma string vazia ("") e para ignore_empty fornecemos TRUE. TEXTJOIN então concatena todos os valores não vazios na matriz e retorna o resultado.
Nota: TEXTJOIN retornará os números como texto, por exemplo "100," 500 ", etc. Se quiser um resultado numérico verdadeiro, adicione zero ou envolva toda a fórmula na função VALUE.
