1. Fórmula de validação (Microsoft 365 e Excel 2021)
Cole a fórmula abaixo em B2, considerando o CNPJ em A2 — com ou sem máscara, maiúsculas ou minúsculas. Ela devolve VERDADEIRO ou FALSO.
=LET(
txt; MAIÚSCULA(A2);
limpo; SUBSTITUIR(SUBSTITUIR(SUBSTITUIR(SUBSTITUIR(txt;".";"");"/";"");"-";"");" ";"");
base; ESQUERDA(limpo;12);
v1; CÓDIGO(EXT.TEXTO(base;SEQUÊNCIA(12);1))-48;
p1; MOD(12-SEQUÊNCIA(12);8)+2;
r1; MOD(SOMARPRODUTO(v1;p1);11);
d1; SE(r1<2;0;11-r1);
base2; base&d1;
v2; CÓDIGO(EXT.TEXTO(base2;SEQUÊNCIA(13);1))-48;
p2; MOD(13-SEQUÊNCIA(13);8)+2;
r2; MOD(SOMARPRODUTO(v2;p2);11);
d2; SE(r2<2;0;11-r2);
E(
NÚM.CARACT(limpo)=14;
ÉNÚM(VALOR(DIREITA(limpo;2)));
DIREITA(limpo;2)=d1&d2
)
)Na versão em inglês do Excel, troque os nomes das funções (
MAIÚSCULA→UPPER, SUBSTITUIR→SUBSTITUTE, ESQUERDA→LEFT, EXT.TEXTO→MID, CÓDIGO→CODE, SEQUÊNCIA→SEQUENCE, SOMARPRODUTO→SUMPRODUCT, NÚM.CARACT→LEN, DIREITA→RIGHT) e os ; por ,.Transformando em função nomeada
Em Fórmulas → Gerenciador de Nomes, crie o nome CNPJ.VALIDO com a fórmula acima trocando A2 por cnpj e envolvendo tudo em LAMBDA(cnpj; ...). Depois é só usar =CNPJ.VALIDO(A2) em qualquer célula da pasta de trabalho.
2. Função VBA (qualquer versão do Excel)
Abra o editor com Alt+F11, insira um novo módulo e cole:
Option Explicit
Private Function CnpjLimpar(ByVal valor As String) As String
Dim i As Long, c As String, saida As String
valor = UCase$(valor)
For i = 1 To Len(valor)
c = Mid$(valor, i, 1)
If (c >= "0" And c <= "9") Or (c >= "A" And c <= "Z") Then
saida = saida & c
End If
Next i
CnpjLimpar = saida
End Function
Private Function CnpjDigito(ByVal sequencia As String) As Long
Dim i As Long, soma As Long, peso As Long, resto As Long
Dim tamanho As Long: tamanho = Len(sequencia)
For i = 1 To tamanho
peso = ((tamanho - i) Mod 8) + 2
soma = soma + (Asc(Mid$(sequencia, i, 1)) - 48) * peso
Next i
resto = soma Mod 11
If resto < 2 Then
CnpjDigito = 0
Else
CnpjDigito = 11 - resto
End If
End Function
' =CNPJ_VALIDO(A2)
Public Function CNPJ_VALIDO(ByVal valor As String) As Boolean
Dim cnpj As String, base As String, dv As String
Dim i As Long
cnpj = CnpjLimpar(valor)
If Len(cnpj) <> 14 Then Exit Function
For i = 13 To 14
If Not (Mid$(cnpj, i, 1) >= "0" And Mid$(cnpj, i, 1) <= "9") Then Exit Function
Next i
base = Left$(cnpj, 12)
dv = CStr(CnpjDigito(base))
dv = dv & CStr(CnpjDigito(base & dv))
CNPJ_VALIDO = (Mid$(cnpj, 13, 2) = dv)
End Function
' =CNPJ_FORMATAR(A2) -> 12.ABC.345/01DE-35
Public Function CNPJ_FORMATAR(ByVal valor As String) As String
Dim cnpj As String: cnpj = CnpjLimpar(valor)
If Len(cnpj) <> 14 Then
CNPJ_FORMATAR = cnpj
Exit Function
End If
CNPJ_FORMATAR = Left$(cnpj, 2) & "." & Mid$(cnpj, 3, 3) & "." & _
Mid$(cnpj, 6, 3) & "/" & Mid$(cnpj, 9, 4) & "-" & Right$(cnpj, 2)
End FunctionSalve a planilha como .xlsm, senão as funções somem ao fechar o arquivo.
3. Aplicar a máscara com letras
Formato personalizado de célula (00"."000"."000"/"0000"-"00) só funciona com números. Como o CNPJ agora é texto, use uma coluna calculada:
=SE(NÚM.CARACT(A2)<>14;A2;
ESQUERDA(A2;2)&"."&EXT.TEXTO(A2;3;3)&"."&EXT.TEXTO(A2;6;3)&"/"&EXT.TEXTO(A2;9;4)&"-"&DIREITA(A2;2))4. Não perder zeros à esquerda nem transformar em número
- Antes de colar: selecione a coluna e defina Formato → Texto. Depois cole com Colar Especial → Valores.
- Ao importar CSV: use Dados → De Texto/CSV, clique em Transformar Dados e marque a coluna do CNPJ como Texto antes de carregar.
- Ao exportar: arquivos gerados pelo Excel podem sair sem os zeros à esquerda se a coluna virou número em algum momento. Confira sempre com
=NÚM.CARACT(A2), que precisa devolver 14. - Validação de dados: em Dados → Validação de Dados → Personalizado, use
=CNPJ_VALIDO(A2)para impedir a digitação de CNPJ inválido já na entrada.
5. Power Query (limpeza em massa)
let
Origem = Excel.CurrentWorkbook(){[Name="Empresas"]}[Content],
ComoTexto = Table.TransformColumnTypes(Origem, {{"CNPJ", type text}}),
Limpo = Table.TransformColumns(ComoTexto, {{"CNPJ", each
Text.Select(Text.Upper(_), {"0".."9", "A".."Z"}), type text}}),
Filtrado = Table.SelectRows(Limpo, each Text.Length([CNPJ]) = 14)
in
FiltradoDepois de limpar, use a coluna calculada com =CNPJ_VALIDO() para separar o que reprova. Se você tem uma base grande para auditar, o gerador ajuda a testar a planilha antes de rodar no arquivo de verdade.