Aprendemos como retirar valores numéricos de uma célula no Excel 2016 e anteriores. As fórmulas que usamos eram um pouco complexas, mas agora o Excel 2019 e o 365 estão no jogo.
O Excel 2019 e 365 apresentam algumas novas funções que facilitam a tarefa de remover caracteres não numéricos e recuperam apenas valores numéricos em uma nova célula. Usaremos fórmulas que podem nos ajudar a fazer isso, de forma mais conveniente.
Fórmula Genérica
| =TEXTJOIN("",VERDADE,IFERROR(MID(confundido_texto,SEQÜÊNCIA(NumChars), 1) +0, "")) |
Jumbled_text: Este é o texto de origem do qual você deseja extrair todos os valores numéricos.
NumChars: Este é o número total de caracteres que você deseja processar. O jumbled_text não deve ter mais caracteres do que este número (chars e numérico combinados).
Vamos ver um exemplo para deixar as coisas claras.
Exemplo: remover caracteres não numéricos e extrair todos os números
Portanto, aqui temos algum texto confuso. Este texto contém alguns números e alguns caracteres não numéricos. Preciso me livrar de caracteres não numéricos e obter valores numéricos apenas na coluna D.
Não espero que o número total de caracteres em um texto embaralhado seja superior a 20. Portanto, o valor de NumChars é 20 aqui. Você pode aumentar esse número se precisar.
Aplique a fórmula genérica acima aqui para remover os caracteres não numéricos.
| =TEXTJOIN("",VERDADE,IFERROR(MID(C3,SEQÜÊNCIA(20),1)+0,"")) |
E quando você aperta o botão Enter. Você obtém todos os caracteres não numéricos removidos. Arraste esta fórmula para baixo para remover os caracteres da string de todas as células na coluna C3.
Como funciona?
Primeiro, vamos ver como essa fórmula é resolvida passo a passo.
| 1-> TEXTJOIN("",VERDADE,IFERROR(MID(C3,SEQÜÊNCIA(20),1)+0,"")) |
| 2-> TEXTJOIN("",VERDADE,IFERROR(MID("12asw12w123",{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20},1)+0,"")) |
| 3-> TEXTJOIN("",VERDADE,IFERROR({"1"; "2"; "a"; "s"; "w"; "1"; "2"; "w"; "1"; "2"; "3"; ""; ""; ""; ""; ""; ""; ""; ""; ""}+0,"")) |
| 4-> TEXTJOIN("",VERDADE,IFERROR({1; 2; #VALOR!; # VALOR!; # VALOR!; 1; 2; #VALOR!; 1; 2; 3; #VALOR!; # VALOR!;…; #VALOR!}+0,"")) |
| 5-> TEXTJOIN("",VERDADE,{1;2;"";"";"";1;2;"";1;2;3;"";"";"";"";"";"";"";"";""}) |
| 6-> "1212123" |
Como você pode ver, a fórmula começa a ser resolvida de dentro para fora. No início, a função SEQUENCE é resolvida. Já que passamos de 20. Ele retorna uma matriz de números começando de 1 a 20.
Esta matriz é servida para a função MID como o número inicial. A função mid vai para cada índice em string e divide cada caractere. Você pode ver isso na etapa 3.
Em seguida, adicionamos 0 a cada caractere. No Excel, se você tentar adicionar um número a caracteres não numéricos, resultará em #VALUE! Erro. Portanto, obtemos uma matriz de números e #VALUE! Erros. Os caracteres não numéricos sumiram agora.
A próxima função IFERROR substitui todos os erros #VALUE por "" (em branco). Agora ficamos com valores numéricos e espaços.
Finalmente, esta matriz é servida para a função TEXTJOIN. A função TEXTJOIN os concatena e obtemos uma string que contém apenas números.
Aprimorando a Fórmula
A fórmula acima usou um número codificado para processar o número de caracteres (pegamos vinte). Mas você pode querer que seja dinâmico. Nesse caso, você adivinhou certo, usaremos a função LEN. Levará um número exato de caracteres para o processamento. Então a fórmula será.
| = TEXTJOIN ("", VERDADEIRO, IFERROR (MID (texto_ confuso, SEQUÊNCIA (LEN(texto confuso)),1)+0,"")) |
Aqui, a função LEN detectará automaticamente o número exato de caracteres na sequência alfanumérica. Isso aliviará o trabalho de determinar o número máximo de caracteres.
Alternativa de função SEQUENCE
Se você não quiser usar a função SEQUENCE, poderá usar uma combinação das funções ROW e INDIRECT para gerar números sequenciais.
| = TEXTJOIN ("", VERDADEIRO, IFERROR (MID (texto_ confuso,FILEIRA(INDIRETO("1:"&LEN(jumbled_text))),1)+0,"")) |
O INDIRETO converterá o texto ("1:20") no intervalo real e a função ROW listará todos os números de linha de 1 a 20. (20 é apenas por exemplo. Pode ser qualquer número).
Então, sim pessoal, é assim que você pode extrair os caracteres não numéricos de uma string alfanumérica no Excel. Espero ter sido explicativo o suficiente e este artigo ajudou você. Se você tiver alguma dúvida sobre este tópico ou qualquer outro tópico do Excel / VBA. Até então, mantenha a excelência.
Divida os números e o texto da string no Excel 2016 e anteriores: Quando não tínhamos a função TEXTJOIN, usamos as funções LEFT e RIGHT com algumas outras funções para separar caracteres numéricos e não numéricos de uma string.
Extraia texto de uma string no Excel usando as funções ESQUERDA e DIREITA do Excel: Para remover texto da string no excel, podemos usar as funções LEFT e RIGHT do excel. Essas funções nos ajudam a cortar strings dinamicamente.
Remova os espaços iniciais e finais do texto no Excel: Espaços à esquerda e à direita são difíceis de reconhecer visualmente e podem bagunçar seus dados. Retirar esses caracteres da string é uma tarefa básica e mais importante na limpeza de dados. Veja como você pode fazer isso facilmente no Excel.
Remover caracteres da direita: Para remover caracteres à direita de uma string no Excel, usamos a função LEFT. Sim, a função ESQUERDA. A função ESQUERDA retém o número dado de caracteres da ESQUERDA e remove tudo da direita.
Remova caracteres indesejados no Excel: Para remover caracteres indesejados de uma string no Excel, usamos a função SUBSTITUTE. A função SUBSTITUTE substitui os caracteres fornecidos por outro caractere fornecido e produz uma nova string alterada.
Como remover texto no Excel começando de uma posição no Excel: Para remover o texto de uma posição inicial em uma string, usamos a função REPLACE do Excel. Essa função nos ajuda a determinar a posição inicial e o número de caracteres a serem removidos.
Artigos populares:
50 atalhos do Excel para aumentar sua produtividade | Torne sua tarefa mais rápida. Esses 50 atalhos farão você trabalhar ainda mais rápido no Excel.
Como usar a função VLOOKUP do Excel| Esta é uma das funções mais usadas e populares do Excel, que é usada para pesquisar valores em diferentes intervalos e planilhas.
Como usar o Excel Função CONT.SE| Conte valores com condições usando esta função incrível. Você não precisa filtrar seus dados para contar valores específicos. A função Countif é essencial para preparar seu painel.
Como usar a função SUMIF no Excel | Esta é outra função essencial do painel. Isso ajuda você a somar valores em condições específicas.