Pular para o conteúdo principal

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!

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

Postar um comentário

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.

Postagens mais visitadas deste blog

Dr. Planilha: Como Remover Linhas de Grade no #Excel #dica

As linhas de grade do Excel não aparecerão em relatórios impressos, mas pode ser que você também deseje que elas fiquem ocultas quando estiver trabalhando com a planilha por uma questão de estética ou Antes de mais nada é importante ainda que se diga que "linha de grade" não é a mesma coisa que "borda".

Dr. Planilha: Como Fazer Cálculo do IMC no #Excel

Antes de mais nada, vamos entender melhor o que é IMC, emprestando a definição da wikipédia: O índice de massa corporal (IMC) é uma medida internacional usada para calcular se uma pessoa está no peso ideal. Ele foi desenvolvido pelo polímata Lambert Quételet no fim do século XIX. Trata-se de um método fácil e rápido para a avaliação do nível de gordura de cada pessoa, ou seja, é um preditor internacional de obesidade adotado pela Organização Mundial da Saúde (OMS). A fórmula para calcular o IMC é bastante simples, vamos precisar apenas de duas informações, seu Peso em quilogramas e sua Altura em metros. O cálculo consiste em dividir seu peso pela sua altura ao quadrado. Vejamos como ficaria: Ora, isso parece bastante fácil de se fazer no Excel, e de fato é. Vamos à prática! Para começar, faça uma planilha igual à do exemplo abaixo. Agora preencheremos a fórmula para o cálculo na célula B3, ela deve ficar da seguinte forma: =B1/B2^2 Para entender entender melhor a fórm