Como extrair um número de uma cadeia de caracteres no Excel: Encontrando a abordagem correta

Ao trabalhar no Excel para limpeza de dados, você pode se deparar com a tarefa de extrair números de cadeias alfanuméricas. Esse problema é comum se você tiver dados em que os números estão incorporados em cadeias de caracteres, como códigos de produtos ou respostas de pesquisas.

Neste artigo, mostrarei a você os diferentes métodos para extrair números de cadeias de caracteres usando fórmulas como RIGHT(), LEN() e FIND() para diferentes casos de uso. Se você não estiver familiarizado com alguns dos conceitos básicos, eu recomendo que você faça nosso curso de Introdução ao Excel para construir uma base sólida no Excel. Você também deve conferir a nossa Preparação de dados no Excel para saber mais sobre a transformação de dados brutos.

Fórmulas do Excel para extrair números de uma cadeia de caracteres

O Excel oferece diferentes métodos baseados em fórmulas para extrair números de cadeias de caracteres. Cada método é adequado para diferentes necessidades com base na localização do número na cadeia. Se você estiver com pouco tempo, fique à vontade para pular para a seção que resolve sua necessidade mais imediata.

Extração de números do início de uma cadeia de caracteres no Excel

Às vezes, você pode encontrar cenários em que os números estão no início da cadeia de caracteres. Nesses casos, você usará LEFT() e FIND() para extrair os números da cadeia de caracteres. Quando você combina as funções em uma fórmula, os números são extraídos usando a seguinte lógica:

• FIND(): Encontra a posição da primeira letra na cadeia de caracteres.

• LEFT(): Extrai os caracteres do início da cadeia de caracteres até a posição da primeira letra menos um.

A primeira parte da fórmula abaixo encontra a primeira letra da cadeia de caracteres. Em seguida, a segunda parte extrai tudo antes dessa letra, deixando apenas os números iniciais. Se não houver letras, ele retornará a string inteira.

=LEFT(A2, FIND(MID(A2, MIN(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"}, A2&"ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1), A2) - 1)

Extração de números do início de uma cadeia de caracteres no Excel. Imagem do autor.

Esse método é apropriado quando você trabalha com conjuntos de dados que normalmente usam prefixos numéricos, como números de pedidos, IDs de membros e números de lotes.

Extração de números do final de uma cadeia de caracteres no Excel

Se quiser extrair números do final de uma cadeia de caracteres no Excel, você precisará usar as funções RIGHT(), LEN() e FIND(). Suponha que você tenha os seguintes dados na planilha do Excel. Você combinará as funções para extrair o número onde:

• FIND(): Encontra a posição do primeiro dígito na cadeia de caracteres.

• LEN(): Calcula o número de caracteres do primeiro dígito até o final da cadeia de caracteres.

• RIGHT(): Extrai os caracteres do primeiro dígito até o final da cadeia de caracteres.

A fórmula abaixo remove todo o texto inicial e retorna o primeiro número junto com tudo o que segue na string.

=RIGHT(A2, LEN(A2) - MIN(FIND({0,1,2,3,4,5,6,7,8,9}, A2&"0123456789")) + 1)

Extração de números do final de uma cadeia de caracteres no Excel. Imagem do autor.

Esse método é mais útil em conjuntos de dados com caracteres alfanuméricos consistentes, como SKUs de produtos, IDs de pedidos e números de série.

Extrair números de qualquer posição em uma cadeia de caracteres no Excel

Se você tiver um conjunto de dados em que os números aparecem em qualquer lugar da cadeia, precisará de fórmulas avançadas usando TEXTJOIN(), MID() e ROW() para extrair os números.

A combinação dessas funções em uma fórmula extrairá um número de uma cadeia de caracteres usando a seguinte lógica: 

• ROW() e MID(): Itera através de cada caractere de cadeia de caracteres para determinar se ele é numérico.

• IFERROR(): Teste lógico para isolar os números identificados de outros caracteres.

• TEXTJOIN(): Combina todos os caracteres numéricos extraídos em uma única cadeia numérica.

=TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)), MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1), ""))

Extrai o número de qualquer posição em uma cadeia de caracteres. Imagem do autor.

Esse método é mais adequado quando o conjunto de dados tem caracteres alfanuméricos com números que aparecem em qualquer parte da cadeia. Como observação, nem todas essas funções estão disponíveis em versões mais antigas do Excel. Confira nosso curso de habilidades em Fundamentos do Excel para saber mais sobre as funções e fórmulas disponíveis nas versões mais recentes do Excel.

Extração de números filtrando caracteres indesejados

Às vezes, seus dados são confusos. Felizmente, você pode combinar as funções SUBSTITUTE() e TEXTJOIN(). Basta você seguir estas etapas:

• SUBSTITUTE(): A função SUBSTITUTE() pode ser usada para substituir todos os caracteres não numéricos em uma cadeia de caracteres por um delimitador, como um espaço ou uma cadeia vazia. Por exemplo, você pode substituir letras e caracteres especiais por espaços.

• TEXTJOIN(): A função TEXTJOIN() pode então ser usada para concatenar os caracteres restantes (que agora devem ser apenas números) em uma única cadeia de caracteres.

=TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)), MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1), ""))

Extrair o número de uma cadeia de caracteres filtrando os caracteres. Imagem do autor.

Quando você pode precisar extrair números de uma cadeia de caracteres no Excel

Vamos analisar dois pequenos estudos de caso para que você possa praticar a extração de números de cadeias de caracteres.

Exemplo 1: Extração de números de códigos de produtos

Suponha que você esteja gerenciando um inventário de produtos em que um código de produto exclusivo identifica cada item. Esses códigos de produto são cadeias alfanuméricas como "PROD1234", "ITEM5678" ou "SKU91011".

Portanto, para extrair o ID do produto numérico de códigos de produto alfanuméricos, usamos funções baseadas na posição do número. Lembre-se, se os números estiverem no final, aplique RIGHT(), LEN(), e FIND(). Se eles aparecerem no início, use LEFT() e FIND().

O exemplo abaixo extrai a parte numérica dos códigos de produto do lado direito do código.

=RIGHT(A1, LEN(A1) - FIND("-", SUBSTITUTE(A1, MID(A1, MIN(FIND({"0","1","2","3","4","5","6","7","8","9"}, A1&"0123456789")), 1), "-")))

Extração de números de códigos de produtos no Excel. Imagem do autor.

Exemplo 2: Dados de pesquisa com números incorporados

Às vezes, os dados da pesquisa contêm texto e números, como "Avaliado em 5 de 10", "Pontuação": 8" ou "3 estrelas". Teremos que trabalhar um pouco antes de calcular as médias ou identificar tendências.

Como os números podem aparecer em qualquer lugar do texto, é necessária uma fórmula dinâmica usando TEXTJOIN(), MID() e ROW().

=TEXTJOIN("", TRUE, IFERROR(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1) * (ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1))), ""))

Extração de números de qualquer posição no Excel. Imagem do autor.

Exemplos de código de copiar e colar para casos especiais

Para a seção final, gostaria de apresentar a você várias fórmulas adicionais que lidam com cenários diferentes e específicos. Sinta-se à vontade para copiar e colar, e espero que isso resolva qualquer problema específico que você possa estar tendo. 

Extraia o primeiro bloco contíguo de números

Se a cadeia de caracteres contiver várias sequências numéricas, mas você precisar apenas do primeiro grupo, tente esta solução usando as funções LET() e SEQUENCE():

=LET(
txt, A2,
pos, MATCH(TRUE, ISNUMBER(--MID(txt, SEQUENCE(LEN(txt)), 1)), 0),
len, MATCH(FALSE, ISNUMBER(--MID(txt, SEQUENCE(LEN(txt)-pos+1)+pos-1, 1)), 0) - 1,
VALUE(MID(txt, pos, len))
)

Extraindo o primeiro bloco contíguo de uma cadeia de caracteres no Excel. Imagem do autor.

Números de extrato entre parênteses

Nos casos em que os números aparecem dentro de parênteses, use:

=MID(A2, FIND("(", A2)+1, FIND(")", A2)-FIND("(", A2)-1)

Extraia os números entre parênteses no Excel. Imagem do autor.

Extrair números após um caractere específico

Quando seus dados usam dois pontos como delimitador (por exemplo, "Score: 8"), capture os números com:

=TRIM(MID(A2, FIND(":", A2)+1, LEN(A2)))

Extrair número após um caractere especial no Excel. Imagem do autor.

Isso puxa tudo após o cólon. Envolva o resultado com VALUE() se você precisar de um valor numérico. Substitua os dois pontos se você tiver um delimitador diferente.

Extraia todos os números e combine-os em um único valor

Para remover todos os caracteres que não sejam dígitos e combinar todos os caracteres numéricos em uma cadeia de caracteres, use o seguinte:

=TEXTJOIN("", TRUE, IFERROR(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)*1, ""))

Extraindo todos os números e combinando-os em um único valor no Excel. Imagem do autor.

Dica: Em versões mais antigas do Excel, insira isso como uma fórmula de matriz (Ctrl+Shift+Enter).

Extrair números incluindo pontos decimais

Para cadeias de caracteres numéricas que podem incluir um decimal (por exemplo, "Price: 45,99"), use esta fórmula para manter os dígitos e o ponto decimal:

=TEXTJOIN("", TRUE, IFERROR(
  IF(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)=".", ".",
     IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)),
        MID(A2, ROW(INDIRECT("1:" & LEN(A1))), 1),
        "")
  ),""))

Extrair números incluindo pontos decimais no Excel. Imagem do autor.

Observação: Essa fórmula pressupõe a presença de apenas um ponto decimal.

Conclusão

A extração de números de cadeias de caracteres é uma técnica importante para a limpeza de dados. Como analista de dados, incentivo você a aprender a aplicar as diferentes fórmulas discutidas neste artigo.

Se você quiser aprimorar suas habilidades em Excel, recomendo que faça nossos cursos Análise de dados em Excel e Visualização de dados em Excel para dominar a análise e a apresentação de dados. Experimente também nosso curso Modelagem financeira em Excel, que pode abrir muitas portas para você.