Note: The other languages of the website are Google-translated. Back to English

Retire ou remova caracteres não numéricos de strings de texto

Às vezes, você pode precisar remover todos os caracteres não numéricos das sequências de texto e apenas manter os números conforme a imagem mostrada abaixo. Este artigo apresentará algumas fórmulas para resolver essa tarefa no Excel.


Retire ou remova todos os caracteres não numéricos de strings de texto com fórmulas

No Excel 2019 e no Office 365, uma nova função TEXTJOIN combinada com as funções IFERROR, MID, ROW e INDIRECT pode ajudá-lo a extrair apenas números de uma string de texto, a sintaxe genérica é:

=TEXTJOIN("",TRUE,IFERROR(MID(text,ROW(INDIRECT("1:100")),1)+0,""))
  • text: a string de texto ou valor de célula da qual você deseja remover todos os caracteres não numéricos.

1. Copie ou insira a fórmula abaixo em uma célula em branco onde deseja produzir o resultado:

=TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0,""))

2. E então, pressione Ctrl + Shift + Enter chaves juntas para obter o primeiro resultado, veja a captura de tela:

3. Em seguida, selecione a célula da fórmula e arraste a alça de preenchimento até as células às quais deseja aplicar esta fórmula, apenas os números foram extraídos e todos os outros caracteres não numéricos foram removidos, consulte a captura de tela:


Explicação da fórmula:

ROW (INDIRETO ("1: 100"): Número 1: 100 na fórmula INDIRETA significa que a função MID avalia 100 caracteres da string de texto. Essa matriz conterá 100 números como este: {1; 2; 3; 4; 5; 6; 7; 8 .... 98; 99; 100}.
Nota: Se a sequência de texto for muito mais longa, você pode alterar o número 100 para um número maior conforme necessário.

MID (A2, ROW (INDIRETO ("1: 100")), 1: Esta função MID é usada para extrair texto na célula A2 para obter um caractere, e será uma matriz como esta:
{"5"; "0"; "0"; ""; "K"; "u"; "t"; "o"; "o"; "l"; "s"; ""; "f" ; "o"; "r"; ""; "E"; "x"; "c"; "e"; "l"; ""; ""; ""; ""; ""; "". ..}

MID(A2,ROW(INDIRECT("1:100")),1)+0: Adicionando o valor 0 após esta matriz ser usada para forçar o texto a um número, o valor numérico do texto será convertido em número e os valores não numéricos serão exibidos como #VALUE valor de erro como este:
{"5"; "0"; "0"; # VALOR!; # VALOR!; # VALOR!; # VALOR!; # VALOR!; # VALOR! !;#VALOR! !;#VALOR!...}

IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0: Esta função IFERROR é usada para substituir todos os valores de erro por uma string vazia como esta:
{"5"; "0"; "0"; ""; ""; ""; ""; ""; ""; ""; ""; …}

TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0,"")): Por fim, esta função TEXTJION combinará todos os valores não vazios na matriz que retornou pela função IFFERROR e retornará o resultado.


Notas:

1. Com a fórmula acima, os números serão retornados em formato de texto, se você precisar de um valor numérico real, aplique esta fórmula, lembre-se de pressionar Ctrl + Shift + Enter juntas para obter o resultado correto.

=TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0,""))+0

2. Em versões anteriores do Excel, esta fórmula não funcionará, neste caso, a seguinte fórmula pode ajudá-lo, copie ou insira esta fórmula em uma célula em branco:

=SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)) * ROW(INDIRECT("1:"&LEN(A2))), 0), ROW(INDIRECT("1:"&LEN(A2))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A2)))/10)


Retire ou remova todos os caracteres não numéricos de strings de texto com um recurso fácil

Talvez seja muito longo para lembrar as fórmulas acima, aqui, vou apresentar o Kutools for Excel para você, com o seu Remover Personagens recurso, você pode remover caracteres numéricos, alfabéticos, não imprimíveis ou alfanuméricos de strings de texto com apenas alguns cliques. Clique para baixar Kutools para Excel para avaliação gratuita!


Funções relativas usadas:

  • TEXTJOIN:
  • A função TEXTJOIN une vários valores de uma linha, coluna ou intervalo de células com delimitador específico.
  • MID:
  • A função MID é usada para encontrar e retornar um número específico de caracteres do meio de uma determinada sequência de texto.
  • ROW:
  • A função ROW do Excel retorna o número da linha de uma referência.
  • INDIRECT:
  • A função INDIRETO do Excel converte uma string de texto em uma referência válida.
  • IFERROR:
  • A função IFERROR é usada para retornar um resultado personalizado quando uma fórmula avalia um erro e retornar um resultado normal quando nenhum erro ocorre.

Mais artigos:

  • Remover quebras de linha de células no Excel
  • Este tutorial fornece três fórmulas para ajudá-lo a remover quebras de linha (que ocorrem pressionando as teclas Alt + Enter em uma célula) de células específicas no Excel.

As melhores ferramentas de produtividade para escritório

Kutools for Excel - ajuda você a se destacar da multidão

Você gostaria de completar seu trabalho diário de forma rápida e perfeita? O Kutools for Excel traz 300 recursos avançados poderosos (combinar pastas de trabalho, somar por cor, dividir o conteúdo da célula, converter data e assim por diante ...) e economizar 80% do tempo para você.

  • Projetado para 1500 cenários de trabalho, ajuda você a resolver 80% dos problemas do Excel.
  • Reduza milhares de cliques de teclado e mouse todos os dias, alivie seus olhos e mãos cansados.
  • Torne-se um especialista em Excel em 3 minutos. Não é mais necessário se lembrar de fórmulas dolorosas e códigos VBA.
  • Avaliação gratuita ilimitada de 30 dias. Garantia de devolução do dinheiro em 60 dias. Atualização e suporte gratuitos por 2 anos.
Faixa de opções do Excel (com Kutools para Excel instalado)

Guia Office - Habilitar leitura e edição com guias no Microsoft Office (incluindo Excel)

  • Um segundo para alternar entre dezenas de documentos abertos!
  • Reduza centenas de cliques do mouse para você todos os dias, diga adeus à mão do mouse.
  • Aumenta sua produtividade em 50% ao visualizar e editar vários documentos.
  • Traz guias eficientes para o Office (incluindo Excel), assim como o Chrome, Firefox e o novo Internet Explorer.
Captura de tela do Excel (com guia Office instalado)
Comentários (4)
Ainda não há classificações. Seja o primeiro a avaliar!
Este comentário foi feito pelo moderador no site
Obrigado por isso. Boa fórmula. Como eu poderia alterá-la para que, se a célula contiver apenas letras, a fórmula insira um 0 na célula de resultados (em vez de apenas em branco como está no momento)? Achei que poderia fazer isso envolvendo a fórmula em outra instrução IF, mas não estou indo muito longe.
Este comentário foi feito pelo moderador no site
Olá, Glenn, Para exibir os resultados como espaços em branco em vez de zeros, aplique a seguinte fórmula:=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2))),1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A2)))/10),"")

Por favor, tente, espero que possa ajudá-lo!
Este comentário foi feito pelo moderador no site
Uau, vai demorar um pouco para digerir 😋 Obrigado por ter tempo para responder 👍
Este comentário foi feito pelo moderador no site
Qual é essa fórmula para configurações francesas?
Não há comentários postados aqui ainda
Deixe o seu comentário
Postando como convidado
×
Avalie esta postagem:
0   Personagens
Locais sugeridos

Siga-nos

Copyright © 2009 - www.extendoffice.com. | Todos os direitos reservados. Distribuído por ExtendOffice. | | | Mapa do site
Microsoft e o logotipo do Office são marcas comerciais ou marcas registradas da Microsoft Corporation nos Estados Unidos e / ou em outros países.
Protegido por Sectigo SSL