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 com novos brinquedos, quero dizer, funções.
O Excel 2019 e 365 apresentam algumas novas funções (TEXTJOIN e SEQUENCE) que podem facilitar a tarefa de remover caracteres numéricos e recuperar apenas valores não 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,E SE(ISERROR(MID(confundido_texto,SEQÜÊNCIA(NumChars), 1) +0),MID(confundido_texto,SEQÜÊNCIA(NumChars), 1), ""))) |
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: Remova os caracteres numéricos e extraia todos os caracteres alfabéticos e não numéricos.
Portanto, aqui temos algum texto alfanumérico. Este texto contém alguns números e alguns caracteres não numéricos. Preciso me livrar dos caracteres numéricos e obter alfabetos e outros valores de caracteres apenas na coluna D.
Não espero que o número total de caracteres em um texto embaralhado seja superior a 100. Portanto, o valor de NumChars é 100 aqui. Você pode aumentar ou diminuir esse número, se necessário.
Aplique a fórmula genérica acima aqui para remover os caracteres numéricos.
| =TEXTJOIN("",VERDADE,E SE(ISERROR(MID(B3,SEQÜÊNCIA(100),1)+0),MID(B3,SEQÜÊNCIA(100),1),""))) |
Quando você pressiona o botão Enter, todos os caracteres numéricos são removidos e apenas os valores alfabéticos permanecem. Arraste esta fórmula para baixo para remover os números da string de todas as células na coluna B.
Como funciona?
Primeiro, vamos ver como essa fórmula é resolvida passo a passo.
| 1-> TEXTJOIN("",VERDADE,E SE(ISERROR(MID(B3,SEQÜÊNCIA(100),1)+0),MID(B3,SEQÜÊNCIA(100),1),""))) |
| 2-> TEXTJOIN("",VERDADE,E SE(ISERROR(MID("This1 is… site",{1,2,3,… 100},1)+0),MID(B3,SEQÜÊNCIA(100),1),""))) |
| 3-> TEXTJOIN("",VERDADE,E SE(ISERROR({"T"; "h"; "i"; "s"; "", "1" … ""; ""}+0),MID(B3,SEQÜÊNCIA(100),1),""))) |
| 4-> TEXTJOIN("",VERDADE,E SE(ISERROR({#VALOR!; # VALOR!; # VALOR!; # VALOR!; 1…; #VALOR!}),MID(B3,SEQÜÊNCIA(100),1),""))) |
| 5-> TEXTJOIN("",VERDADE,E SE({VERDADEIRO; VERDADEIRO; VERDADEIRO; VERDADEIRO; FALSO …; VERDADEIRO},MID(B3,SEQÜÊNCIA(100),1),""))) |
| 6-> TEXTJOIN("",VERDADE,{"Esse";""… .;""}) |
| 7-> "Este é o site número um" |
Para facilitar a leitura, não escrevi toda a série. Usei pontos (…) para indicar matrizes longas.
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 100 como o número de caracteres. Ele retorna uma matriz de números começando de 1 a 100.
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 em um array. Você pode ver isso na etapa 3. Não mostrei a matriz inteira, pois vai ocupar muito espaço. {"T"; "h"; "i"; "s"; "", "1"… ""; ""}
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. {#VALOR!; # VALOR!; # VALOR!; # VALOR!; 1…; #VALOR!}
Esta matriz é servida ao ISERROR função. Como você sabe, a função ISERROR retorna TRUE para erros e FALSE para valores sem erro. Portanto, obtemos um array de TRUE e FALSE. TRUE para caracteres não numéricos e FALSE para números. {VERDADEIRO; VERDADEIRO; VERDADEIRO; VERDADEIRO; FALSO…; VERDADEIRO}.
A mesma coisa acontece com as declarações de seção TRUE de IF (MID(B3,SEQÜÊNCIA(100),1)) Ele retorna uma matriz de todos os caracteres da string alfanumérica em B3.
Agora, para cada instrução TRUE, a função IF retorna o caractere correspondente do array da seção verdadeira. Para FALSE, IF retorna em branco (""). Agora você terá uma matriz que não contém nenhum caractere numérico. {"Esse";""… .;""}.
Finalmente, esta matriz é servida pelo TEXTJOIN função. Esta função junta determinados textos entre si, ignorando os valores em branco, com um determinado delimitador. Portanto, obtemos uma string que não contém nenhum caractere numérico. Este é o site número um.
Aprimorando a Fórmula
A fórmula acima usa um número codificado para processar o número de caracteres (pegamos 100). Mas você pode querer que seja dinâmico. Nesse caso, você adivinhou certo, você pode usar a função LEN. Levará o número exato de caracteres para processamento. Então a fórmula será.
| = TEXTJOIN ("", TRUE, IF (ISERROR (MID (jumbled_text, SEQUENCE (LEN(texto confuso), 1) +0), MID (texto_ confuso, SEQUÊNCIA (LEN(texto confuso),1),""))) |
Aqui, a função LEN detectará automaticamente o número exato de caracteres na sequência alfanumérica. Isso aliviará o fardo 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 ("", TRUE, IF (ISERROR (MID (jumbled_text,FILEIRA(INDIRETO("1:"&LEN(NumChars))), 1) +0), MID (texto_ confuso,FILEIRA(INDIRETO("1:"&LEN(NumChars))),1),""))) |
O INDIRETO converterá o texto ("1: 100") no intervalo real e a função ROW listará todos os números de linha de 1 a 100. (100 é apenas um 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.
Como remover caracteres não numéricos de células no Excel 2019: Para remover caracteres não numéricos de uma string alfanumérica no Excel, usamos a nova função TEXTJOIN. A faixa de caracteres não numéricos de uma string pode nos ajudar a limpar nossos dados para uma melhor análise de dados. Então aqui está como você faz
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.