none
Fórmula para extrair número de texto - EXCEL RRS feed

  • Pergunta

  • Bom dia Galera,

    Preciso de uma ajuda.
    Tenho uma tabela com 20 registros de endereço que importei do banco de dados para migrar para outro sistema.
    No campo de endereço do ERP 1, o número da residência é junto com o logradouro e no ERP 2 o campo de logradouro e número são distintos.

    Gostaria de saber se existe alguma fórmula no Excel para extrair apenas os números da coluna endereço.

    Abraço
    Márcio Ferroni Microsoft Certified Professional - Windows 2003
    terça-feira, 13 de setembro de 2011 14:58

Respostas

  • Olá Márcio Ferroni

    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.

    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)

     

    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.

     

     

    Espero ter ajudado, caso tenha mais alguma dúvida poste aqui novamente.

    Se sua dúvida foi respondida, marque este thread como “útil”. Um abraço e até mais!


    Mauricio Cassemiro, Microsoft Answers Voluntário de Suporte e Entusiasta de Office Brasil. 

    Microsoft Answers. Você tem dúvida? Nós temos a resposta! 


    Mauricio Cassemiro - Entusiasta de Office Brasil
    • Marcado como Resposta Richard Juhasz segunda-feira, 10 de outubro de 2011 17:33
    domingo, 25 de setembro de 2011 01:56

Todas as Respostas

  • Olá Márcio Ferroni

    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.

    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)

     

    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.

     

     

    Espero ter ajudado, caso tenha mais alguma dúvida poste aqui novamente.

    Se sua dúvida foi respondida, marque este thread como “útil”. Um abraço e até mais!


    Mauricio Cassemiro, Microsoft Answers Voluntário de Suporte e Entusiasta de Office Brasil. 

    Microsoft Answers. Você tem dúvida? Nós temos a resposta! 


    Mauricio Cassemiro - Entusiasta de Office Brasil
    domingo, 25 de setembro de 2011 01:56
  • Olá Márcio Ferroni

    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.

    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)

     

    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.

     

     

    Espero ter ajudado, caso tenha mais alguma dúvida poste aqui novamente.

    Se sua dúvida foi respondida, marque este thread como “útil”. Um abraço e até mais!


    Mauricio Cassemiro, Microsoft Answers Voluntário de Suporte e Entusiasta de Office Brasil. 

    Microsoft Answers. Você tem dúvida? Nós temos a resposta! 


    Mauricio Cassemiro - Entusiasta de Office Brasil
    • Marcado como Resposta Richard Juhasz segunda-feira, 10 de outubro de 2011 17:33
    domingo, 25 de setembro de 2011 01:56
  • Olá Maurício.

    Tentei resolver o mesmo problema do amigo acima através desta solução sua.

    Não funcionou. 

    Poderia me passar alguma explicação a respeito da função ENT.TEXTO?

    Acredito que esteja nela o erro na minha fórmula. Não entendi muito bem o a parte LINHA($1:$9), se essas linhas deveriam estar livres ou se são temporárias utilizadas na fórmula.

    Engraçado que não encontrei a função ENT.TEXTO no HELP do excel 2007 nem no google.

    Abraço.

    Caso possa responder no meu e-mail, segue: calvache.led@gmail.com

    sábado, 11 de fevereiro de 2012 14:03
  • Pessoal, mesmo sendo de 2012 a publicação, eu precisei utilizar esta fórmula e existe algumas correções para ela funcionar referente aos nomes das funções:

    ENT.TEXTO - CORRETO EXT.TEXTO

    LINHA - CORRETO LIN

    ONDE TEM VÍRGULA É PONTO E VÍRGULA.

    Esperto ter ajudado


    quarta-feira, 29 de novembro de 2017 10:43