Extraindo números de seqüências alfanuméricas


Ícone do Most Valuable Professional

Este artigo foi escrito por , um MVP (Most Valuable Professional) da Microsoft. Visite o site dos MVP da Microsoft para obter mais informações.

Neste artigo, explicamos uma fórmula que extrai números de seqüências nas seguintes situações:

  • Quando caracteres alfabéticos e numéricos estão agrupados, como em abc123 ou 678sfr.
  • Quando caracteres alfabéticos e numéricos não estão agrupados, como em abc15tni.

Problema

Como extrair a parte numérica de uma seqüência alfanumérica? Por exemplo: Se a célula A1 contém a seqüência "abc123", como extrair o valor 123 para a célula B1?

Solução

A lógica aqui é pesquisar e extrair o primeiro número da seqüência alfanumérica e retornar então somente os números que se seguem.

Algoritmo

A solução envolve a criação de fórmulas que executem as seguintes tarefas:

  1. Quebre a seqüência alfanumérica em caracteres separados.
  2. Verifique se há um número na seqüência decomposta.
  3. Verifique a posição do número na seqüência alfanumérica.
  4. Conte os números na seqüência alfanumérica.

Abordaremos cada uma dessas tarefas separadamente e, em seguida, integraremos as fórmulas para obter o resultado final.

Quebrar a seqüência alfanumérica em caracteres separados

Use a função EXT.TEXTO. Essa função retorna um número específico de caracteres de uma seqüência de texto, começando na posição em que você indicar, com base no número de caracteres especificado. A sintaxe da função é :

EXT.TEXTO(texto,núm_inicial,núm_caract)

  • texto    A seqüência texto contém os caracteres que você deseja extrair.
  • núm_inicial    A posição do primeiro caractere a ser extraído do texto. O primeiro caractere no texto tem núm_inicial 1 e assim por diante.
  • núm_caract    Especifica o número de caracteres que a função EXT.TEXTO deve retornar do texto.

Para o nosso exemplo, a fórmula é:

=EXT.TEXTO(A1,LINHA($1:$9),1)

Essa fórmula decompõe a seqüência alfanumérica e coloca os caracteres — virtualmente — em diferentes linhas da planilha. Por exemplo, na seqüência alfanumérica abc123, todos os 6 caracteres seriam separados.

 Observação   O número 9 pode ser substituído por qualquer número maior de acordo com o tamanho da seqüência. Neste exemplo, o tamanho máximo da seqüência é 9.

É importante notar que quando a seqüência é decomposta, "1", "2" e "3" são tratados como texto — não como números. Para convertê-los em números, multiplique a fórmula por 1. Por exemplo:

=1*EXT.TEXTO(A1,LINHA($1:$9),1)

Verificar se há um número na seqüência decomposta

Aqui empregamos a função ÉNÚM, que verifica se há um número na seqüência alfanumérica. A fórmula agora se torna:

=ÉNÚM(1*EXT.TEXTO(A1,LINHA($1:$9),1))

Se houver um número na seqüência, o resultado será VERDADEIRO; caso contrário, será FALSO.

Verificar a posição do número na seqüência alfanumérica

Agora, verificaremos a posição do número localizando um valor VERDADEIRO no resultado da seqüência decomposta mencionada no parágrafo acima. Empregaremos a função CORRESP. A nova fórmula agora é:

=CORRESP(ÉNÚM(1*EXT.TEXTO(A1,LINHA($1:$9),1)),0)

 Importante   Você deve digitar essa fórmula como uma matriz pressionando CTRL+SHIFT+ENTER.

Se a seqüência for abc123, a fórmula resultará 4 — sendo essa a posição do primeiro caractere numérico na seqüência alfanumérica.

Contar os números na seqüência alfanumérica

A tarefa agora é contar os números na seqüência, para determinar os caracteres a serem retornados após o primeiro número na seqüência alfanumérica.

Como mencionado acima, os números armazenados como texto na seqüência alfanumérica podem ser convertidos para números multiplicando-os por 1. Por exemplo, =1*EXT.TEXTO(A1,LINHA($1:$9),1)

Depois de convertê-los para número, é possível contar os números com a função CONT.NÚM. Os números podem ser contados utilizando-se a seguinte fórmula:

=CONT.NÚM(1*ENT.TEXTO(A1,LINHA($1:$9),1))

Integrar as fórmulas

Agora, empregaremos a função ENT.TEXTO para integrar as várias partes da fórmula, como mostrado no seguinte exemplo.

=ENT.TEXTO(A1,CORRESP(VERDADEIRO,ÉNÚM(1*ENT.TEXTO(A1,LINHA($1:$9),1)),0),CONT.NÚM(1*ENT.TEXTO(A1,LINHA($1:$9),1)))

Em termos básicos, o problema pode ser agora resumido como: Verificar a posição do primeiro número na seqüência alfanumérica (na célula A1). Retornar esse número e os seguintes.

Para converter os caracteres do resultado em número, multiplique por 1. Embora isso não seja estritamente necessário, deve ser feito se você for executar operações matemáticas sobre os resultados. Esta é a fórmula final a ser digitada na célula B1:

=1*ENT.TEXTO(A1,CORRESP(VERDADEIRO,ÉNÚM(1*ENT.TEXTO(A1,LINHA($1:$9),1)),0),CONT.NÚM(1*ENT.TEXTO(A1,LINHA($1:$9),1)))

 Importante   Você deve inserir essas fórmulas como matrizes pressionando CTRL+SHIFT+ENTER.

Mais exemplos

Para testar a fórmula, insira os dados da figura a seguir nas células A1:A7 de uma planilha em branco.

Exemplo de conversão de seqüência

Insira a fórmula na célula B1 e use o recurso de AutoPreenchimento para copiar a fórmula nas células B2:B7. (Lembre-se de pressionar CTRL+SHIFT+ENTER.)

Vale ressaltar aqui que se a seqüência for yur09875reew e você usar a fórmula multiplicada por 1, o resultado na coluna B será 9875 e não 09875. Uma vez que 0*1=0, o 0 é ignorado e o resultado retornado é 9875. Se desejar que o resultado seja 09875, não multiplique toda a fórmula por 1.

 
 
Aplica-se a:
Excel 2003