Excel - como manter apenas os dígitos de uma célula, não as letras.
Assistir vídeo
- Verifique uma coluna para ver se há algum dígito no código
- Método de cobrança 1:
- Preenchimento Flash
- Método Mike:
- Use a função SUBSTITUTE com uma Array Constant.
- Você não terá que usar Ctrl + Shift + Enter porque é uma constante de matriz
- Isso removerá um dígito de cada vez
- Use a função AND para ver se cada item na matriz resultante é igual ao item original
- Método de cobrança 3:
- Use uma função VBA para verificar os dígitos
Transcrição de vídeo
Bill: Ei. Bem vindo de volta. É hora de outro Podcast de duelo do Excel. Sou Bill Jelen de. Estarei com a presença de Mike Girvin da ExcelIsFun. Este é o nosso episódio 186: sim, se houver dígitos na célula. A pergunta de hoje é enviada por Jen, tem 13.000 linhas de dados, precisa olhar através de uma célula, se algum caractere for um dígito, marque como Sim, caso contrário, não. Tudo bem. Bem, Mike, espero que você tenha uma maneira incrível de fazer isso, porque eu não tenho.
Vou usar o flash fill e, no flash fill, vou apenas dar um padrão aqui com algumas letras e números. Quero ter certeza de incluir todos os números possíveis apenas para que ele entenda o que estou fazendo, e 0 assim. Então, há os dados originais, e então vou fazer o flash fill corrigi-los para mim e, na versão fixa, vamos nos livrar de todos os dígitos. Então, vou olhar e ver se há um dígito. Se houver, livre-se dele assim e pressione CONTROL + E para preencher o flash, e o que devemos ter agora são apenas as letras, apenas as letras.
E então a questão é: isso mudou? Então = SE isto é = o que estamos procurando, então isso significa que não houve mudanças, nem dígitos, então estamos dizendo Não, caso contrário, sim, assim, e clique duas vezes, copie isso, certo, e então, qualquer coisa com um Não não tem dígitos. Nós nos livramos de nossa linha original e, uma vez que copiamos, CONTROL + C, ALT + E, S, V, e podemos nos livrar do preenchimento do flash. Tudo bem, Mike. Vamos ver o que você tem. (= SE (A2 = B2, “Não”, “Sim”))
Mike: Uau ,. Esse deve ser o uso único e criativo mais incrível de preenchimento em flash que eu acho que já vi. Você inventou um texto, tirou os números reais, CONTROL + E, e imediatamente extraiu todos os números e entregou uma nova sequência de texto sem os números, e então você fez seu IF. Absolutamente lindo.
Tudo bem. Vou acessar esta planilha bem aqui e vou usar a função SUBSTITUIR. Agora, SUBSTITUIR, vou dizer a ele para olhar para aquele texto bem ali, e o OLD_TEXT que quero localizar e remover, bem, são todos dígitos. Então, vou criar uma constante de matriz (1, 2, 3, 4, 5 todos os dígitos e). Agora, isso é uma constante de matriz e está no OLD_TEXT porque não estou colocando um único item lá, mas em vez disso, estou colocando um monte de itens. Esta é uma operação de matriz de argumento de função. Existem 10 itens diferentes aqui que instruirão o SUBSTITUTE a entregar 10 itens separados, certo, e, se encontrar um desses itens, o que eu quero? “”. Isso dirá à função para não colocar nada lá). (= SUBSTITUIR (A2, (1,2,3,4,5,6,7,8,9,0), “”))
Agora, meu cursor está no final. Quando eu pressiono a tecla F9, com certeza, porque há apenas um 0, todos eles são exatamente iguais, exceto o último. Para o último, o SUBSTITUTE encontrou o 0 e não colocou nada em seu lugar. Agora, CONTROL-Z, CONTROL-ENTER e vou copiá-los aqui mesmo, F2 e F9. Então, se formos para o 6, tem a versão 5, tirou o 5 ali, tirou o 6 ali e tirou o 8 ali, então vai ter 1, 2, 3 itens diferentes que são diferentes. Somente quando todos os itens forem exatamente iguais ao item original, ele nos dirá que não há dígitos. FUGA.
Vou voltar ao topo. Parece que, F2, este é um teste lógico AND. Função AND. Eu quero verificar se cada um desses itens na matriz resultante é = ao item original. Quando forem todas verdadeiras, isso me dirá que não há números naquele anel de texto. ), CONTROL + ENTER, recebo FALSE porque um deles está faltando internamente esse 0. Vou copiar isso aqui. Este, é claro, obterá um TRUE - o mesmo com estes - porque todos os itens gerados internamente, se eu F2 bem aqui, todos esses, F9, são exatamente iguais ao original. FUGA. Agora, eu chego ao topo. A propósito, eu não tive que usar CONTROL + SHIFT + ENTER porque quando você usa esta constante de matriz em sua fórmula de matriz, você não precisa usar CONTROL + SHIFT + ENTER. (= E (SUBSTITUIR (A2, (1,2,3,4,5,6,7,8,9,0), “”) = A2))
Tudo bem. Eu vou voltar para o começo. Esse é o meu teste lógico. Se tudo isso for verdadeiro, valor se verdadeiro, em ”NÃO”, caso contrário, coloque um SIM ”). CONTORL + ENTER. Clique duas vezes nele. Vou ter que clicar duas vezes neste e enviá-lo. Tudo bem. Foi um pouco divertido com o SUBSTITUTE, uma constante de array, um teste lógico AND e o IF, mas, vou lhe dizer uma coisa, ainda não consigo acreditar naquele flash fill, como você o usou para, em essência, extrair todos os números disso. Tudo bem, vou devolver para você, MrExcel. (= SE (E (SUBSTITUIR (A2, (1,2,3,4,5,6,7,8,9,0), “”) = A2), “Não”, “Sim”))
Bill: Bem, aquela fórmula com SUBSTITUTE e a constante de matriz e AND, isso é incrível. Eu tive que usar flash fill porque eu não poderia ter descoberto este aqui. Isso é brilhante. Agora, eu tenho uma terceira via. Vamos dar uma olhada nisso.
Agora, aqui está a maneira que eu realmente resolveria, apenas um pouco de VBA. Então, eu uso ALT + F11 para alternar para VBA, INSERT, MODULE e, em seguida, digito este código. Vamos criar uma nova função chamada HASNUMBERS e passá-la para o valor da célula, e vamos começar com a palavra ALPHA. Olhamos para cada caractere, e se aquele código, se o (código ASC - 06:35) desse caractere estiver entre 48 e 57, então dizemos que é NÚMEROS, FUNÇÃO DE SAÍDA e apenas continuar. Certo, então, parece até encontrar um dígito. Quando isso acontecer, ele retornará HASNUMBERS. Então, aqui, vamos dizer = HASNUMBERS, aponte para essa célula e clique duas vezes para copiá-la. Sempre que ele vir um dígito ali, obterá os NÚMEROS, ALPHA, fáceis de separar. (= HasNumbers (A2))
Tudo bem, recapitulação rápida do episódio. Objetivo: verificar a coluna para ver se há algum dígito no código do caractere, o código na célula. Usei o preenchimento do flash para remover os dígitos, depois a função de comprimento para ver se mudou ou não. Mike tinha uma fórmula brilhante, função SUBSTITUTE com uma constante de matriz. Você não precisa de CONTROL + SHIFT + ENTER. Você tem que remover um dígito por vez e então usar a função AND para ver todos os 10 resultados e ver se cada um é igual ao item original. Excelente caminho a seguir e, em seguida, meu substituto, use uma função VBA para verificar se há dígitos.
Bem, ei. Quero agradecer a todos pela visita. Nos vemos na próxima vez para outro netcast de e ExcelIsFun.
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Duel186.xlsm
