Ir para o conteúdo principal

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}.
Note: 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 o Kutools for Excel para teste gratuito!


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 para Excel - ajuda você a se destacar na multidão

🤖 Assistente de IA do Kutools: Revolucionar a análise de dados com base em: Execução Inteligente   |  Gerar Código  |  Crie fórmulas personalizadas  |  Analise dados e gere gráficos  |  Invocar funções do Kutools...
Recursos mais comuns: Encontre, destaque ou identifique duplicatas  |  Excluir linhas em branco  |  Combine colunas ou células sem perder dados  |  Rodada sem Fórmula ...
Super VLookup: Vários critérios  |  Valor múltiplo  |  Em várias folhas  |  Pesquisa Difusa...
Av. Lista suspensa: Lista suspensa fácil  |  Lista suspensa de dependentes  |  Lista suspensa de seleção múltipla...
Gerenciador de colunas: Adicione um número específico de colunas  |  Mover colunas  |  Alternar status de visibilidade de colunas ocultas  Comparar colunas com Selecionar células iguais e diferentes ...
Recursos em destaque: Foco da Grade  |  Vista de Design  |  Grande Barra de Fórmula  |  Gerenciador de pastas de trabalho e planilhas | Biblioteca (Auto texto)  |  Data Picker  |  Combinar planilhas  |  Criptografar/Descriptografar Células  |  Enviar e-mails por lista  |  Super Filtro  |  Filtro Especial (filtro negrito/itálico/tachado...) ...
15 principais conjuntos de ferramentas12 Texto Ferramentas (Adicionar texto, Remover Personagens ...)  |  50+ de cores Tipos (Gráfico de Gantt ...)  |  Mais de 40 práticos Fórmulas (Calcule a idade com base no aniversário ...)  |  19 Inclusão Ferramentas (Insira o código QR, Inserir imagem do caminho ...)  |  12 Conversão Ferramentas (Números para Palavras, Conversão de moedas ...)  |  7 Unir e dividir Ferramentas (Combinar linhas avançadas, Dividir células do Excel ...)  |  ... e mais

Kutools para Excel possui mais de 300 recursos, Garantindo que o que você precisa está a apenas um clique de distância...

Descrição


Guia Office - Habilite leitura e edição com guias no Microsoft Office (inclui 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 (inclui Excel), assim como Chrome, Edge e Firefox.
Comments (4)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
What is this formula for french settings?
This comment was minimized by the moderator on the site
Thanks for this. Nice formula.How would I alter it so that if the cell contains only letters the formula enters a 0 the results cell (rather than just blank as it is at the moment)?Thought I might be able to do it by wrapping the formula in another IF statement but I’m not getting very far.
This comment was minimized by the moderator on the site
Hello, Glenn,To display the results as blanks rather than zeros, please apply the following formula:=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),"")

Please try, hope it can help you!
This comment was minimized by the moderator on the site
Wow that’ll take some digesting 😋 Thanks for taking the time to reply 👍
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations