Um leitor do blog postou uma dúvida recentemente sobre formatação de números de CPF. Na verdade, ele desejava remover todos os caracteres diferentes de números, no caso pontos e hífen, mantendo apenas os números.
Segue a pergunta:
Costumeiramente eu solucionaria essa necessidade usando uma fórmula para substituir os caracteres "." e "-" por <vazio>, o que resulta em uma sequência de apenas números. A seguir descrevo como ficaria essa fórmula.Segue a pergunta:
"A minha planilha tem uma coluna de números de CPF.
Gostaria de tirar os pontos e o traço de todos os números da coluna.
Como faço isso?"
A ideia é utilizar a fórmula chamada "SUBSTITUIR" para encontrar os caracteres indesejados, substituindo-os por <vazio>. Essa fórmula pede 3 parâmetros obrigatórios e 1 opcional, para nosso problema não precisaremos do parâmetro opcional.
Dissecando a função:
SUBSTITUIR(texto;texto_antigo;novo_texto)
texto: nesse parâmetro informaremos o texto para o qual desejamos remover os caracteres indesejados;
texto_antigo: entre aspas, informaremos que carácter pretendemos remover do texto completo;
texto_novo: entre aspas, informaremos que carácter substituirá o indesejado
Um exemplo de como isso ficaria para o CPF 999.999.999-99:
CÉLULA A1-> 999.999.999-99
CÉLULA B1-> =SUBSTITUIR(A1;".";"")
CÉLULA C1-> =SUBSTITUIR(B1;"-";"")
RESULTADO: 99999999999
Funciona, mas convenhamos, resolver essa fórmula em duas células não é lá muito elegante. Para simplificar a solução em uma única célula, faremos uma "união" das duas fórmulas "SUBSTITUIR". A solução ficaria da seguinte forma:
CÉLULA A1-> 999.999.999-99
CÉLULA C1-> =SUBSTITUIR(SUBSTITUIR(A1;".";"");"-";"")
Trocando em miúdos, o que fiz foi trocar o "B1" que aparecia na fórmula da célula C1 pela fórmula que usamos no exemplo anterior na célula B1. Ou seja, primeiro removemos os pontos e depois os hifens. Se houvesse um terceiro carácter diferente, usaríamos uma sequência de 3 fórmulas "SUBSTITUIR" encadeadas, cada uma retirando um carácter diferente.
Nosso exemplo envolve CPF, no entanto, poderíamos aplicar essa mesma regra para qualquer situação do dia-a-dia, como remover parênteses, hifens e espaços em branco de números de telefone. Essa é uma solução simples para uma necessidade que envolva poucas variações de caracteres indesejados. Quando tivermos muitos caracteres diferentes para remover, o melhor é adotar uma solução diferente, um pouco mais complexa e trabalhosa de se elaborar, mas que atenderia uma variação ilimitada de caracteres indesejados.
Os comentários estão à disposição para tirar dúvidas ou para postar exemplos diferentes para os quais essa solução possa não se aplicar integralmente.
Um grande abraço do Dr. Planilha!
Doutor Planilha,
ResponderExcluirTentei usar sua explicação para remover os parentese e traços em uma lista de contatos por telefone, porém não tive sucesso. A baixo, segue alguns exemplos de telefones com os caracteres que desejo remover.
(63)8444-9258
(63)9233-5361
(63)3576-1099
(63)9239-8708
Olá Delson!
ExcluirConsiderando que sua lista começa em A1, em B1 eu colocaria a seguinte fórmula:
=SUBSTITUIR(A1;"(";"")
Observe que essa fórmla remove apenas o "(" e o restante dos caracteres ainda está lá. O segredo é criar uma fórmula igual à que fiz acima, para cada um dos caracteres que precisam ser removidos.
Veja como eu faria isso, colocando as fórmulas nas colunas B1 à D1:
B1 = SUBSTITUIR(A1;"(";"") <-- remove (
C1 = SUBSTITUIR(A1;")";"") <-- remove )
D1 = SUBSTITUIR(A1;"-";"") <-- remove -
Ok, mas isso não resolve o seu problema. O segredo agora é inserir uma fórmula dentro da outra, para que o Excel vá removendo os caracteres de forma incremental.
Resumindo, você colocará a fórmula de D1 dentro de C1 e depois, a de C1 dentro de B1, substituindo o A1 pela fórmula da coluna anterior. Em dois passos ficaria assim:
Passo 1: D1 dentro de C1 no lugar de A1
C1 = SUBSTITUIR(SUBSTITUIR(A1;"-";"");")";"")
Passo 2: C1 modificado dentro de B1 no lugar de A1
B1 = SUBSTITUIR(SUBSTITUIR(SUBSTITUIR(A1;"-";"");")";"");"(";"")
Significa dizer que vamos removendo os 3 caracteres indesejados de "dentro pra fora", por isso informamos o A1 apenas no primeiro SUBSTITUIR() mais interno, pois ele irá devolver para o SUBSTITUIR() seguinte o texto já sem o caractere "-", e assim sucessivamente.
Espero que tenha ficado mais claro. Se gostou do blog e da solução, compartilhe com seus amigos e colegas.
Abraços do Dr. Planilha!
Dr. a função deu certo, mas o resultado depende as formulas e do numero original. Tem alguma outra maneira de extrair esses números que foram filtrados para uma nova coluna, para que eu possa apagar da planilha os que estão com os pontos e traços?? Obrigado.
ResponderExcluir