Baixe a apostila de exemplo
Este tutorial irá demonstrar como encontrar e extrair um número de dentro de uma string de texto no Excel e nas planilhas do Google.
Encontrar e extrair o número da string
Às vezes, seus dados podem conter números e texto e você deseja extrair os dados numéricos.
Se a parte do número estiver no lado direito ou esquerdo da string, é relativamente fácil dividir o número e o texto. No entanto, se os números estiverem dentro da string, ou seja, entre duas strings de texto, você precisará usar uma fórmula muito mais complicada (mostrada abaixo).
TEXTJOIN - Extrair Números no Excel 2016+
No Excel 2016 foi introduzida a função TEXTJOIN, que pode extrair os números de qualquer lugar da string de texto. Aqui está a fórmula:
| 1 | = TEXTJOIN ("", TRUE, IFERROR ((MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1) * 1), "")) |
Vamos ver como essa fórmula funciona.
As funções ROW, INDIRECT e LEN retornam uma matriz de números correspondente a cada caractere em sua string alfanumérica. Em nosso caso, “Segunda01Dia” tem 11 caracteres, então a função ROW retornará a matriz {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11}.
| 1 | = TEXTJOIN ("", VERDADEIRO, IFERROR ((MID (B3, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11}, 1) * 1), "")) |
Em seguida, a função MID extrai cada caractere da string de texto, criando uma matriz contendo sua string original:
| 1 | = TEXTJOIN ("", TRUE, IFERROR (({"M"; "o"; "n"; "d"; "a"; "y"; "0"; "1"; "D"; "a ";" y "} * 1)," ")) |
Multiplicar cada valor na matriz por 1 criará uma matriz contendo erros Se o caractere da matriz for texto:
| 1 | = TEXTJOIN ("", TRUE, IFERROR (({# VALUE!; # VALUE!; # VALUE!; # VALUE!; # VALUE!; # VALUE!; 0; 1; # VALUE!; # VALUE!; # VALUE !}), "")) |
Em seguida, a função IFERROR remove os valores de erro:
| 1 | = TEXTJOIN ("", TRUE, {""; ""; ""; ""; ""; ""; 0; 1; ""; ""; ""}) |
Deixando apenas a função TEXTJOIN que junta os números restantes.
Observe que a fórmula fornecerá todos os caracteres numéricos de sua string. Por exemplo, se sua string alfanumérica for Monday01Day01, você terá 0101 como resultado.
Extrair Números - Antes do Excel 2016
Antes do Excel 2016, você podia usar um método muito mais complicado para extrair números do texto. Você pode usar a função FIND junto com as funções IFERROR e MIN para determinar a primeira posição da parte do número e a primeira posição da parte do texto após o número. Em seguida, simplesmente extraímos a parte do número com a função MID.
| 1 | = MID (B3, MIN (IFERROR (FIND ({0,1,2,3,4,5,6,7,8,9}, B3), 999999999)), MIN (IFERROR (SEARCH ({"a" , "b", "c", "d", "e", "f", "g", "h", "I", "j", "k", "l", "m", n "," o "," p "," q "," r "," s "," t "," u "," v "," w "," x "," y "," z " }, B3, MIN (IFERROR (FIND ({0,1,2,3,4,5,6,7,8,9}, B3), 999999999)))), 999999999)) - MIN (IFERROR (FIND ( {0,1,2,3,4,5,6,7,8,9}, B3), 999999999))) |
Observação: esta é uma fórmula de matriz, você deve inserir a fórmula pressionando CTRL + SHIFT + ENTER, em vez de apenas ENTER.
Vamos ver passo a passo como essa fórmula funciona.
Encontre o número do punho
Podemos usar a função FIND para localizar a posição inicial do número.
| 1 | = MIN (IFERROR (FIND ({1,2,3,4,5,6,7,8,9,0}, B3), 999999999)) |
Para o argumento find_text da função FIND, usamos a constante de matriz {0,1,2,3,4,5,6,7,8,9}, o que faz com que a função FIND execute uma pesquisa separada para cada valor em a constante da matriz.
O argumento within_text da função FIND em nosso caso é Monday01Day, em que 1 pode ser encontrado na posição 8 e 0 na posição 7, então nossa matriz de resultados será: {8, # VALUE, # VALUE, # VALUE, # VALUE, # VALUE, # VALUE, #VALUE, # VALUE, 7}.
Usando a função IFERROR, substituímos os erros #VALUE por 999999999. Em seguida, simplesmente procuramos o mínimo neste array e obtemos, portanto, o lugar do primeiro número (7).
Observe que a fórmula acima é uma fórmula de matriz, você deve pressionar Ctrl + Shift + Enter para ativá-la.
Encontre o primeiro caractere de texto após o número
Da mesma forma que localizar o primeiro número dentro da string, usamos a função FIND para determinar onde o texto começa novamente após o número, fazendo bom uso também do argumento start_num da função.
| 1 | = MIN (IFERROR (FIND ({"a", "b", "c", "d", "e", "f", "g", "h", "I", "j", "k "," l "," m "," n "," o "," p "," q "," r "," s "," t "," u "," v "," w ", "x", "y", "z"}, B3, C3), 999999999)) |
Esta fórmula funciona de forma muito semelhante à anterior usada para localizar o primeiro número, apenas usamos letras em nossa constante de matriz e, portanto, os números causam erros #VALUE. Observe que começamos a pesquisa apenas após a posição determinada para o primeiro número (este será o argumento start_num) e não a partir do início da string.
Não se esqueça de pressionar Ctrl + Shift + Enter para usar a fórmula acima.
Não há mais nada do que colocar tudo isso junto.
Extrair número de dois textos
Uma vez que temos a posição inicial da parte do número e o início da parte do texto depois disso, simplesmente usamos a função MID para extrair a parte do número desejada.
| 1 | = MID (B3, C3, D3-C3) |
Outro Método
Sem explicar com mais detalhes, você também pode usar a fórmula complexa abaixo para obter o número de dentro de uma string.
| 1 | = SUMPRODUCT (MID (0 & B3, LARGE (INDEX (ISNUMBER (- MID (B3, ROW (INDIRECT ("1:") & LEN (B3))), 1)) * ROW (INDIRECT ("1:" & LEN (B3) )), 0), ROW (INDIRETO ("1:" & LEN (B3)))) + 1,1) * 10 ROW (INDIRETO ("1:" & LEN (B3))) / 10) |
Esteja ciente de que esta fórmula acima fornecerá 0 quando nenhum número for encontrado na string e que os zeros iniciais serão omitidos.
Encontre e extraia o número da string para o texto no Planilhas Google
Os exemplos mostrados acima funcionam da mesma forma no Planilhas Google e no Excel.
