[Dicas] Formatação personalizada para CPF e CNPJ na mesma célula

O Excel oferece muitas opções para personalizar a formatação do texto de uma célula. O que poucos sabem é que existe maneiras de criar formatações diferentes conforme critérios desejados. Infelizmente os critérios possíveis de aplicar são bastante simples, mas pode ser de grande utilidade para a sua planilha.

Um exemplo é o campo CNPJ e CPF, onde em muitos formulários pode ser requerido que eles estejam na mesma célula, mas que a formatação corresponda ao tipo inserido na célula. É possível fazer essa formatação apenas com ferramentas nativas do Excel.

O que sabemos é que o CPF possui o padrão 999.999.999-99 e o CPNJ 99.999.999/9999-99, ou seja, o CPF terá 11 caracteres numéricos e o CNPJ terá 14 caracteres numéricos. Sabendo disso, podemos criar a formatação condicional adicional o critério numérico entre chaves: [seu critério]. Para fazer isso você deve selecionar as células em que serão digitadas os CPF’s ou CNPJ’s, clicar com o botão direito do mouse e selecionar formatar células. Na guia Número, escolha personalizado e no campo “Tipo” digite a seguinte condição:

[<=99999999999]000\.000\.000-00;00\.000\.000\/0000-00

Excelmaniacos - CPF ou CNPJ

Eu também recomendo você fazer uma validação nestas células para aceitarem somente 11 ou 14 dígitos, que corresponde ao CPF ou CNPJ respectivamente. Para isso, com o mesmo intervalo de células selecionado, vá na guia “DADOS” -> “VALIDAÇÃO DE DADOS”, na lista “Permitir” selecione “Personalizado” e no campo Fórmula digite:

=OU(NÚM.CARACT(C2)=11;NÚM.CARACT(C2)=14)

Excelmaniacos - CPF ou CNPJ2

Substitua C2 pela primeira célula da sua tabela onde serão digitados os CPF/CNPJ.

Recomendo colocar na Guia “Alerta de Erro” uma mensagem avisando para digitar corretamente o número de CPF ou CNPJ, sendo 11 dígitos para CPF ou 14 dígitos para CNPJ.

***ATUALIZADO EM 01/12/2016***

Como existe a possibilidade de CPF’s e CNPJ’s iniciados com zero, o procedimento acima não permitirá a inclusão nestes casos. Por isso estou postando um segundo método utilizando um pequeno código em VBA no evento Worksheet_Change da planilha, para que a formatação condicional e validação sejam 100% efetivas.

Para isso, siga mais estes passos:

1º) Formate o intervalo ou coluna que receberão os valores como “Texto”;

2º) Pressione ALT+F11 para abrir o editor do Visual Basic e insira o código abaixo no módulo da planilha (No código estou supondo que o intervalo de preenchimento de CNPJ e CPF será “A2:A1000”):


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2:A1000")) Is Nothing Then
Target.NumberFormat = _
"[<=99999999999]000\.000\.000-00;00\.000\.000\/0000-00"
If Target.Cells.Count > 1 Then
Selection.NumberFormat = "@"
Else
If Target.Value <> 0 Then
Application.EnableEvents = False
Target.Value = Target.Value * 1
Application.EnableEvents = True
Else
Target.NumberFormat = "@"
End If
End If
End If
End Sub

Veja a imagem:

vba-cnpj-e-cpf

3º) Salve o arquivo como .xlsm (Pasta de trabalho habilitada para macro do Excel) para que o recurso em VBA funcione sempre que o arquivo for aberto.

Desta forma fica perfeito!

Faça o Download do modelo deste artigo: Modelo Excelmaniacos – Formatação Personalizada CPF e CNPJ V.2

Anúncios

9 comentários sobre “[Dicas] Formatação personalizada para CPF e CNPJ na mesma célula

    1. Eu coloquei a seguinte fórmula na validação para limitar os caracteres de 10 a 14 digitos. Não é 100% mas já ajuda. =E(NÚM.CARACT(I1)>=10;NÚM.CARACT(I1)<=14)

      Curtir

  1. Muito boa mesmo, mas por algum motivo o Excel não reconhece o início do número com zero como um dígito válido, pois a caixa de diálogo aparece quando o número do cpf ou do cnpj começam com zero.

    Curtir

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s