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
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)
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:
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
Rafa essa foi show é muito útil pra mim. Valeu!! Rsrsrs
CurtirCurtir
Parabens, jovem. Acredito serem bastante uteis em diversos casos.
CurtirCurtir
Usei a formatação e validação (Tudo certo). Quando coloco um CPF ou CNPJ iniciando com 0(zero), dá erro. Como solucionar esse erro ?
CurtirCurtir
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)
CurtirCurtir
Olá amigo, atualizei o artigo para corrigir esta exceção! Abraços!
CurtirCurtir
simplesmente sensacional.
CurtirCurtir
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.
CurtirCurtir
Tive o mesmo problema do Sidney. Como solucionar esse erro?
CurtirCurtir
Usei o CNPJ: 00.057.859/0001-37, colocando na célula: 0005759000137, mas deu certo, identificou como m CPF.
CurtirCurtir
O VB funciona para o CPF, mas quando inicio com 0 no CNPJ, não reconhece.
CurtirCurtir
Olá testei com o excel 2016 (office 365) e não funcionou?
Alguma dica para esta versão ?
CurtirCurtir
Sou leigo e estou finalizando um a planilha de cadastro de clientes no excel vba. Porém, não encontrao quase nada sobre o que preciso, vejamos:
Quero cadastrar numa mesma célula o CPF e/ou CNPJ, detalhe, isto deve ocorrer depois que eu selecionar na célula que tenho o TIPO DE PESSOA, ou seja, FÍSICA ou JURÍDICA. Quando FÍSICA o cursor leva primeiro para a célula da DATA DE NASCIMENTO e posteriormente vai para, registrar o CPF. Quando eu seleciono na célula TIPO DE PESSOA “JURÍDICA” o cursor ignora a célula da DATA DE NASCIMENTO e leva direto para regustrar o CNPJ. ALERTA = o CPF e CNPJ devem se alternar na mesma célula. Preciso também de orientação sobre onde devo “alojar” ou códigos.
Desde já figo agradecido a quem puder me ajudar.
CurtirCurtir