Formatação Personalizada CNPJ e CPF no Excel

Quando trabalhamos com bases de dados de clientes, fornecedores ou folhas de pagamento no Excel, é comum nos depararmos com colunas de CPF e CNPJ exibidas como números brutos, sem pontos, traços ou barras. Isso dificulta a leitura, gera erros de validação e deixa a planilha com aparência amadora.

A solução definitiva é a formatação personalizada, uma funcionalidade nativa do Excel que permite criar máscaras visuais sem alterar o valor real da célula (o que é crucial para cálculos e validações posteriores). E se você está montando cadastros e precisa não apenas formatar, mas também consultar e validar milhares de CPFs e CNPJs automaticamente, a Planilha de Consulta Automática de CNPJ (Redator: inserir link do produto) é a ferramenta que vai transformar a rotina do seu escritório.

Neste artigo, você vai aprender os códigos exatos de formatação personalizada para CPF, CNPJ e até uma formatação simultânea que identifica automaticamente qual documento está na célula.

1. Introdução

No Excel dispomos de formatações padrão de células que atendem à grande maioria de nossas necessidades. Formatos como geral, contábil, moeda, porcentagem, data, hora e texto, geralmente resolvem os nossos problemas para visualização de dados em células.

No entanto, muitas vezes desejamos formatações que nativamente o Excel não apresenta e por isso precisamos recorrer às formatações personalizadas. Neste contexto, criamos códigos de formatação para representar visualmente de uma maneira mais otimizada os nossos dados.

Alguns dados notáveis, como número de celular, CPF e CNPJ, requerem formatações personalizadas para serem visualizados de maneira amigável nas células, uma vez que não estão disponíveis nativamente. Com isso, deriva o objetivo deste artigo, que visa propor formatações personalizadas para CPF e CNPJ.

Por fim, nosso maior propósito será elencado com uma formatação personalizada que permite usar uma célula para registrar um CPF e CNPJ, adaptando a formatação automaticamente de acordo com o dado.

Acompanhe este artigo em vídeo: https://www.youtube.com/watch?v=U_kVTGBhH8o.

O que é formatação personalizada de CPF e CNPJ no Excel?

A formatação personalizada de CPF e CNPJ no Excel é uma técnica que aplica máscaras visuais (pontos, traços e barras) sobre números brutos, sem alterar o valor real da célula. Para CPF, o código é 000\.000\.000-00 e para CNPJ é 00\.000\.000\/0000-00, aplicados via menu Formatar Células > Personalizado.

Por que usar formatação personalizada em vez de funções de texto?

Muitos usuários recorrem a funções como =TEXTO() ou concatenação para formatar CPFs e CNPJs. O problema é que essas funções convertem o número em texto, o que impede validações matemáticas, quebra fórmulas de PROCV e aumenta o tamanho do arquivo.

A formatação personalizada, por outro lado, mantém o valor numérico intacto e apenas altera a exibição visual. Isso significa que você pode:

• Usar o CPF/CNPJ em fórmulas de validação;

• Fazer PROCV e CORRESP sem erros;

• Exportar a base para outros sistemas sem precisar "limpar" a formatação;

• Manter a planilha leve e profissional.

2. Formatações personalizadas

2.1. Como inserir

Para inserir a formatação personalizada devemos inicialmente selecionar o intervalo desejado, que pode ser de uma ou mais células. A imagem a seguir exemplifica o intervalo de B3 a B10 selecionado para a aplicação da formatação personalizada.

Após a seleção, devemos abrir a caixa de diálogo de formatação de células. Para isso, clicamos com o botão direito na seleção e escolhemos a opção Formatar Células.

Podemos também usar o atalho CTRL + 1 para o mesmo fim (a tecla 1 que fica próxima ao ESC).

Após essa etapa, surgirá a caixa de diálogo de formatação de células, conforme exibida a seguir:

Para acessar a formatação personalizada, devemos escolher a guia Número e a categoria Personalizado:

Todas as formatações descritas a seguir serão inseridas no espaço para Tipo, conforme evidenciado na próxima imagem.

Devemos apagar o que houver em Tipo para inserirmos as nossas formatações personalizadas. Partindo desta etapa, daremos continuidade para os itens 2.2, 2.3 e 2.4.

2.2. Formatação personalizada para CPF

O nosso código de formatação para CPF deve prever a existência de 11 dígitos numéricos comuns aos CPFs. Então representamos com o dígito zero (0) 11 vezes, conforme imagem a seguir:

Agora precisamos inserir os caracteres não numéricos. Para CPF, teremos o ponto (.) e o hífen (-). Assim, nas posições apropriadas, inserimos estes caracteres.

Note que dividimos a representação do CPF adequadamente: a primeira, segunda e terceira partes com 3 dígitos, e a última com 2 dígitos. No entanto, a tarefa ainda não está finalizada. Isso ocorre porque os pontos são interpretados como divisores de milhares. Precisamos fazer com que os pontos inseridos sejam entendidos como textos no código de formatação. Uma forma de proceder é inserir uma barra invertida antes de cada ponto, conforme imagem a seguir.

Após confirmar, em Ok, os CPFs estarão devidamente formatados.

Para a formatação dos CPFs, o código de formatação inserido foi 000\.000\.000-00.

Podemos também usar aspas, em vez de barras invertidas, para fazer com que os caracteres não numéricos sejam interpretados como textos embutidos no número. Assim, o código de formatação poderia ficar 000”.”000”.”000-00.

2.3. Formatação personalizada para CNPJ

Para a formatação dos CNPJs, a primeira diferença em relação aos CPFs é que há 14 dígitos, o que requer 14 zeros no código de formatação, conforme imagem a seguir.

Agora precisamos inserir os caracteres não numéricos. Para CNPJ, teremos o ponto (.), o hífen (-) e a barra (/). Assim, nas posições apropriadas, inserimos estes caracteres.

Note que dividimos a representação do CNPJ adequadamente: a primeira e última partes possuem 2 dígitos, a segunda e terceira partes possuem 3 dígitos e o quarto grupo possui 4 dígitos. Da mesma forma que fizemos com os CPFS, devemos ajustar os pontos, que são interpretados como divisores de milhares, e a barra, que é interpretada como divisor de datas. Devemos fazer com que estes caracteres sejam entendidos como textos no código de formatação. Vamos inserir uma barra invertida antes de cada ponto e da barra, conforme imagem a seguir.

Após confirmar, em Ok, os CNPJs estarão devidamente formatados.

Para a formatação dos CNPJs, o código de formatação inserido foi 00\.000\.000\/0000-00.

Podemos também usar aspas, em vez de barras invertidas, para fazer com que os caracteres não numéricos sejam interpretados como textos embutidos no número. Assim, o código de formatação poderia ficar 00”.”000”.”000”/”0000-00.

2.4. Formatação personalizada simultânea para CPF e CNPJ

Agora chegou o grande momento: como podemos inserir as duas formatações personalizadas em uma mesma célula simultaneamente? A resposta: inserindo uma condicional no tipo da formatação personalizada.

Faremos uma espécie de função SE, que compara o valor do número e identifica se a formatação aplicada será exatamente a do CPF, descrita no item 2.2, ou se será a do CNPJ, descrita no item 2.3.

Para isso, considere os dados a seguir devidamente selecionados. Eles misturam CPFs e CNPJs na mesma coluna.

Os dados destacados em verde são CNPJs e os demais, em branco, são CPFs. Estes CNPJs são os mesmos exemplificados no item 2.3 e os CPFs são os mesmos exemplificados no item 2.2.

Para fazermos a comparação que identifica o nosso dado corretamente como CPF ou como CNPJ, devemos usar uma expressão entre colchetes. A comparação deve verificar se o dado (que é um CPF ou CNPJ) é menor ou igual ao número 99999999999. Este número possui o dígito 9 inserido 11 vezes (que é o número de dígitos de um CPF). Caso o dado em questão for um número menor ou igual a 99999999999, teremos então a conclusão de que se trata de um CPF. Caso contrário, teremos um CNPJ.

A imagem a seguir ilustra o resultado.

Note que após a comparação entre colchetes, [<=99999999999], temos o código de formatação de CPFs, já descrito no item 2.2. Após este código de formatação para CPF, temos um ponto e vírgula (;) para separar da segunda parte, que é o código de formatação para CNPJ.

Veja a seguir o resultado desejado. Note que a formatação reconheceu satisfatoriamente os CPFs e os CNPJs.

Para a formatação simultânea dos CPFs e CNPJs, o código de formatação inserido foi [<=99999999999] 000\.000\.000-00; 00\.000\.000\/0000-00.

NOTA: existe um CNPJ que “burla” a regra acima descrita. O CNPJ pertence ao Banco do Brasil, cujo número é 00.000.000/0001-91. Caso apliquemos o código de formatação personalizada para CPF e CNPJ simultaneamente, o CPNJ do Banco do Brasil seria formatado visualmente como um CPF.

Além de formatar, você também pode gerar e validar CPFs matematicamente usando a função LAMBDA. Veja como no artigo Gerar e Validar CPF no Excel.

Download Planilha Formatação CNPJ e CPF Personalizada no Excel

Clique no botão abaixo para realizar o  download do arquivo de exemplo:

FAQ: dúvidas sobre formatação de CPF e CNPJ no Excel

1. A formatação personalizada altera o valor real da célula?

R: Não. A formatação personalizada altera apenas a exibição visual do número. O valor real (sem pontos, traços ou barras) permanece intacto, o que é essencial para validações e cálculos.

2. Por que meu código de formatação não está funcionando?

R: Verifique se você está usando as barras invertidas (\) ou aspas (") para escapar os caracteres especiais. Sem isso, o Excel interpreta pontos como separadores de milhar e barras como separadores de data.

3. Posso usar formatação personalizada em colunas com texto?

R: Não. A formatação personalizada só funciona em células com valores numéricos. Se o CPF/CNPJ estiver armazenado como texto, você precisará convertê-lo para número primeiro (removendo apóstrofos ou usando a função =VALOR())

4. Como remover a formatação personalizada?

R: Selecione as células, pressione Ctrl + 1, vá em Formatar Células e escolha a categoria Geral ou Número (sem casas decimais).

5. Quero dominar outras formatações avançadas. O que estudar?

R: O domínio de formatações personalizadas, funções de texto e validação de dados é essencial para analistas de dados. O Curso Excel Master cobre todo esse ecossistema, do básico ao VBA.