17 de julho de 2016

Separe CNPJs e CPFs com o filtro do Excel

Como usar o filtro do Excel para preencher uma lista de valores em uma coluna nova.

Para facilitar a apuração para uma matéria, uma tabela de doações de campanha precisava ter uma coluna identificando os registros como CNPJ ou CPF.

Normalmente tabelas de prestação de contas vêm apenas com o número, e não especificam se a doação veio de uma pessoa física ou pessoa jurídica. O mesmo acontece em tabelas de gastos. Na verdade, se o documento veio identificado com nome ou razão social, você já pode ficar feliz. A Assembleia Legislativa do Paraná, por exemplo, publica a prestação de contas dos deputados estaduais sem identificação, apenas com o número.

Com um truque simples no excel é possível acrescentar uma coluna com a diferenciação:

Passo a passo detalhado abaixo:

Padrão de formatação de CPNJ/CPF

Geralmente, os registros de pessoa física e jurídica vem no seguinte formato:

Padrão de CNPJ

XX.XXX.XXX/0001-XX

14 dígitos

Padrão de CPF

XXX.XXX.XXX-XX

11 dígitos

O CNPJ tem barra e normalmente aquele "mil ao contrário", mas não é regra. O número de dígitos também difere entre os dois, mas talvez existam cnpjs ou cpfs fora do padrão. Desconfie sempre das tabelas.

Como formatar CPF no Excel

Depois de colocar uma lista de CPFs ou CNPJs no excel, eles podem ter perdido a formatação com pontos, traços e barras. Veja abaixo como formatar um número no excel como CPF:

  1. Clique com o botão direito na célula que contém o CPF e vá na opção "Formatar células"
  2. Para isso você também pode usar o atalho Ctrl + 1 (ou Cmd + 1 no Excel for Mac) ou usar a formatação de número na barra de ferramentas


  3. Selecione a opção "Personalizado" ou "Custom"
  4. Digite ou copie e cole esse código para formatar o CPF:
    000"."###"."###-##
  5. Pronto. Qualquer número com 11 dígitos inserido nessa célula será formatado corretamente como um CPF

Como formatar CNPJ no Excel

  1. Clique com o botão direito na célula que contém o CNPJ e vá na opção "Formatar células"
  2. Selecione a opção "Personalizado"
  3. Digite ou cole esse código para formatar o CPF:
    00\.000\.000\/0000-00
  4. Pronto. Qualquer número com 14 dígitos inserido nessa célula será formatado corretamente como um CNPJ

Fórmula para separar CPF E CNPJ no Excel

Use essa máscara para formatar números que possam ser tanto CPFs como CNPJs inseridos na mesma célula. O código contém uma condicional que aplica uma ou outra formatação com base na diferença no número de dígitos
  1. Clique com o botão direito na célula com o número do CPF ou CNPJ e vá na opção "Formatar células"
  2. Selecione a opção "Personalizado"
  3. Digite ou cole esse código para formatar o CPF:
    [<=99999999999]000\.000\.000-00;00\.000\.000\/0000-00

A documentação a seguir se aplica mais a limpeza e identificação de listas já prontas de CPF e CNPJ. Foi um processo realizado com dados públicos, como tabelas de doação de campanha ou de prestadores de serviço para deputados.

Como diferenciar CNPJ de CPF

  1. Verifique a qualidade dos dados

    Passe o olho na tabela para verificar se informações parecidas estão formatadas do mesmo jeito.

    Neste caso a tabela estava bem organizada, os números estão formatados com um padrão correto (pontos, traços, barras, ...) e mesma quantidade de dígitos.

  2. Identifique um padrão para poder separar os dados

    Se a tabela não vem com uma coluna "Pessoa Física/Jurídica", é preciso criá-la. É possível fazer isso encontrando a diferença entre os dois tipos de número. Uma diferença fácil é a quantidade de dígitos, mas isso não é tão simples de "selecionar" no excel. Já a presença da barra (/) nas células de CPNJ é um padrão fácil de ser selecionado.

Como separar CNPJ e CPF no Excel

  1. Ative o filtro no Excel

    Excel - Ativar Filtro
    • Selecione a tabela toda (Ctrl + A / Cmd + A)
    • Certifique-se de que sua tabela não tem "buracos" (linhas ou colunas totalmente vazias). Se tiver, a seleção da tabela ou o filtro podem não funcionar. Se tiver dúvida, faça a seleção com o mouse.
    • Dados > Filtro (Ctrl + Shift + F / Cmd + Shift + F)
  2. Filtre os CNPJs

    Excel - Filtro ativado
    • Clique no filtro da coluna CPF/CNPJ (botão com a seta pra baixo no cabeçalho)
    • Digite a barra "/". Acho que no Windows a janela do filtro precisa de um OK a mais.
    • Quando o filtro funcionar, a tabela irá exibir apenas linhas nas quais as células da coluna selecionada contenham o caractere barra. Ou seja, neste caso, linhas referentes a CNPJs.
    • Quando o filtro está ativado, o número das linhas na esquerda muda de cor, para indicar que existem linhas que estão ocultas. O ícone do filtro também muda na coluna onde ele foi ativado.
  3. Crie uma coluna nova

    • Dê um nome à coluna nova "CNPJ ou CPF", "PF ou PJ", ou algo assim.
    • Ao lado do primeiro CNPJ encontrado, digite "CNPJ" ou "Pessoa Jurídica".
    • Preencha todas as células abaixo ("Fill down") com a mesma informação. Clique no quadradinho no canto da célula e arraste para baixo até o final da tabela.
    • Excel - Fill Down
    • Dica para um Fill Down mais rápido: se sua tabela está "sem buracos" (células vazias), dê dois cliques no quadradinho que ele irá se preencher até o final da tabela (ou até encontrar uma célula vazia à esquerda). Sempre vá até o final pra ver e garantir que deu certo.
  4. Limpe o filtro

    • abra o filtro novamente e "Limpar filtro" ou "selecionar tudo"
  5. Use o filtro na coluna nova

    • Agora que você já preencheu CNPJ na coluna nova, é só preencher "CPF" nas células que ficaram vazias.
    • Clique no filtro da coluna nova
    • Desmarque "CNPJ" e deixe marcadas a células vazias.
    • Digite CPF na primeira célula e faça o Fill Down novamente.
  6. Desative o filtro e pronto!

Avançado: Limpar listas grandes de CNPJs e CPFs

Essa parte é um epílogo um pouco mais avançado para quem usa Mac. O script limpa a formatação dos números (pontos, barras, traços) deixando só os dígitos e acrescentando uma coluna de texto identificando se é CNPJ ou CPF.

  • Use o TextWrangler (editor de texto para Mac)
  • Copie do Excel apenas a coluna de cnpjs e cpfs
  • Cole a lista no Textwrangler
  • Baixe esse applescript e aperte play.
  • A lista está pronta com os números limpos e uma coluna a mais
  • Insira uma coluna a mais no excel (já que agora são duas) e cole de volta o conteúdo lá.