Extraia apenas números do texto (string)
No Excel, você pode usar fórmulas para extrair o número de um valor com texto e números combinados. No exemplo a seguir, usamos uma fórmula para obter 1984 do valor “TPS1984”.
Fórmula para extrair números do texto
=TEXTJOIN("",TRUE,IFERROR((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),""))Neste tutorial, entenderemos essa fórmula em detalhes. E para entendê-lo temos que dividi-lo em várias partes:
- Primeiro, temos a função LEN para obter o número de caracteres do valor.
- Depois disso, INDIRETO usa uma referência de célula usando 1 e 7 (retornado pelo LEN).
- Então a função ROW usará INDIRETO e retornará uma matriz de números começando com 1 e terminando com 7.
- Agora o MID usará esse array e retornará um novo com os valores da célula que você se referiu.
- A seguir, temos uma fórmula de multiplicação simples para multiplicar o array por 1. Com esta multiplicação simples, você obtém um novo array onde terá #VALUE! O erro para valores de texto e números permanecerá intacto.
- A partir daqui, a função IFERROR converterá os valores de erro em valores vazios.
- E no final, TEXTJOIN irá combinar os valores, e você terá apenas números no resultado.
A fórmula acima funciona apenas para a versão Excel 2019 e superior. Isso ocorre porque estamos usando TEXTJOIN, indisponível em versões anteriores.
Se desejar um valor para parte da fórmula, selecione essa parte e pressione a tecla de atalho F9.
TEXTJOIN é uma função volátil que altera o valor quando você atualiza uma célula na planilha. Isso pode tornar sua pasta de trabalho um pouco lenta.
Mas e se eu usar uma versão anterior (2007, 2010, 2013, 2016)
Se você estiver usando versões anteriores, deverá usar uma fórmula diferente. Uma fórmula como esta:
=IF(SUM(LEN(A1)-LEN(SUBSTITUTE(A1, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A1, LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("$1:$"&LEN(A1))),1))* ROW(INDIRECT("$1:$"&LEN(A1))),0), ROW(INDIRECT("$1:$"&LEN(A1))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A1)))/10),"") =SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))),0),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A1)))/10)As duas fórmulas acima funcionam bem com diferentes tipos de valores. Eu testei da seguinte maneira.
Na célula A1 acima temos um valor complexo composto por texto, números e símbolos e temos apenas números no resultado.