terça-feira, 8 de janeiro de 2013

Dr. Planilha: Como tirar pontos e traços do CPF no #Excel

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:
"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?"
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.

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!

3 comentários:

  1. Doutor Planilha,

    Tentei 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

    ResponderExcluir
    Respostas
    1. Olá Delson!

      Considerando 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!

      Excluir
  2. 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

Use esse espaço para enviar suas dúvidas ou fazer críticas e sugestões. Todos os comentários serão postados após passarem pela moderação do Dr. Planilha para que seja mantido o alto nível do conteúdo do blog.

Related Posts Plugin for WordPress, Blogger...