Formatação Personalizada CNPJ e CPF no Excel

Neste artigo você aprenderá como realizar a formatação personalizada de CNPJ e CPF no Excel.

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.

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.

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

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