Validar e Formatar CPF no Excel

planilha de importação NFC-e - Guia do Excel
NFCe – Planilha de importação de NFCe automática em Excel
27 de janeiro de 2018
Planilha cotações bovespa Excel - Guia do Excel
[Excel] Planilha de Cotações Bovespa
11 de fevereiro de 2018

Validar e Formatar CPF no Excel

Neste artigo é demonstrado como validar números de CPF e formatar números de CPF no Excel, é demonstrada uma função VBA para validar o dígito verificador do CPF e verificar se o mesmo está correto.

Para realizar o cálculo do dígito verificador a regra é a seguinte:

Cálculo do primeiro dígito verificador do CPF

Usando como exemplo o CPF: 038.277.936-37

1. Multiplicar os primeiros 9 números pela sequência regressiva de 2 á 10 e somamos o resultado:

Validar e formatar CPF Excel 1

2. Dividir o valor total por 11 e guardar o valor do resto da divisão:

Soma: 239, dividido por 11, o resto da divisão é 8

Se o resto da divisão for menor do que 2 o valor é 0, senão o cálculo é 11-o resto da divisão, ou seja:

1.º Dígito verificador = 11-8 = 3

Cálculo do segundo dígito verificador

1. Multiplicar os primeiros 10 números pela sequência regressiva de 2 á 10 e somamos o resultado:

Validar e formatar CPF Excel 2

2. Dividir o valor total por 11 e guardar o valor do resto da divisão:

Soma: 290, dividido por 11, o resto da divisão é 4

Se o resto da divisão for menor do que 2 o valor é 0, senão o cálculo é 11-o resto da divisão, ou seja:

2.º Dígito verificador = 11-4 = 7

Logo o dígito verificador do sistema é então 37


Instalar a função de validar CPF no Excel

Para instalar a função pressione as teclas ALT + F11, depois clique em Inserir->Módulo

Validar CPF e formatar CPF no Excel VBA

No módulo criado cole o código VBA:

'Função que valida CPF
Public Function lfValidaCPF(ByVal lNumCPF As String) As Boolean
    Application.Volatile
    
    Dim lMultiplicador  As Integer
    Dim lDv1            As Integer
    Dim lDv2            As Integer
    
    lMultiplicador = 2
    
    'Realiza o preenchimento dos zeros á esquerda
    lNumCPF = String(11 - Len(lNumCPF), "0") & lNumCPF
    
    'Realiza o cálculo do dividendo para o dv1 e o dv2
    For i = 9 To 1 Step -1
        lDv1 = (Mid(lNumCPF, i, 1) * lMultiplicador) + lDv1
        
        lDv2 = (Mid(lNumCPF, i, 1) * (lMultiplicador + 1)) + lDv2
        
        lMultiplicador = lMultiplicador + 1
    Next
    
    'Realiza o cálculo para chegar no primeiro dígio
    lDv1 = lDv1 Mod 11
    
    If lDv1 >= 2 Then
        lDv1 = 11 - lDv1
    Else
        lDv1 = 0
    End If
    
    'Realiza o cálculo para chegar no segundo dígido
    lDv2 = lDv2 + (lDv1 * 2)
    
    lDv2 = lDv2 Mod 11
    
    If lDv2 >= 2 Then
        lDv2 = 11 - lDv2
    Else
        lDv2 = 0
    End If
    
    'Realiza a validação e retorna na função
    If Right(lNumCPF, 2) = CStr(lDv1) & CStr(lDv2) Then
        lfValidaCPF = True
    Else
        lfValidaCPF = False
    End If
End Function

Para usar a função clique em qualquer lugar na sua planilha do Excel e digite lfValidaCPF, e passe o número, conforme abaixo:

Validar e formatar CPF Excel 3

A função retornará VERDADEIRO caso o dígito verificador estiver correto e caso esteja incorreto ele retornará FALSO.

No vídeo neste artigo tem um passo-a-passo demonstrando como desenvolver do zero a função e como utilizá-la para validar quantos números você precisar.

Veja também como criar um Suplemento no Excel para que a função funcione no seu Excel sempre que precisar: https://www.guiadoexcel.com.br/como-criar-funcoes-proprias-no-excel-com-vba/

Formatar CPF

  1. Para formatar o CPF no Excel selecione o intervalo de células e pressione CTRL+1
  2. Na tela que segue clique em Formatar->Personalizado
  3. Digite a formatação: 000\.000\.000-00, conforme abaixo:

Validar e formatar CPF Excel 4

5. Veja como fica a formatação das células.

Validar e formatar CPF Excel 5

Abraço

Marcos Rieper

Abaixo o download da planilha com o código fonte já implementado.

9 Comments

  1. VANDERLEI D AVILA RODRIGUES disse:

    Fiz Download do arquivo “validar CPF VBA” . Na planilha publicada funcionou mas não consegui fazer funcionar no meu Excel. Tentei os Suplementos, não localizei. Copiei a função =lfvalidarcpf([@CPF]), tambem não funcionou mesmo após formatar o CPF 000.000.000-00 Gostaria de uma orientação.

  2. Adriana Camargo disse:

    Vanderlei e Marcos, o nome da função está com L ao invés de I por isso apresenta erro quando tentamos executá-la. Após corrigirmos aqui conseguimos rodar sem problemas!

  3. Roberto Mazzarolo disse:

    Vanderlei e Marcos, o calculo esta perfeito, testei varios cpf que tinha em minha planilha e funcionou. o Problema é que a Função do marcos deverá receber apenas os numeros. ou seja a entrada dela o lNumcpf deve ser somente os numeros, nao deverá ter os caracter especiais.(SEPARADORES)

    Para evitar formatações diversas ou erradas desenvolvi um eliminador de caracteres indevidos deixando somente os numeros antes do processo feito pelo Marcos. segue o codigo:

    Colocar Logo no inicio do programa do Marcos , trocar todas as variaveis LnumCPF por cCpf no codigo do Marcos

    Dim lMultiplicador As Integer, lDv1 As Integer
    Dim lDv2 As Integer, nL As Integer

    nL = Len(lNumCPF)
    cCpf = “”
    For i = 1 To nL
    If InStr(1, “0123456789”, Mid(lNumCPF, i, 1)) Then
    cCpf = cCpf + Mid(lNumCPF, i, 1)
    End If
    Next
    ‘Eliminando os caracteres extras do cpf

  4. Arnaldo Gomes disse:

    Parabéns, foi de muita ajuda essa sua publicação. Muito Obrigado.

  5. ALVARO DA SILVA MARQUES disse:

    Não deu certo. aparece #NOME?

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

Inscreva-se no nosso canal do Youtube!


Junte-se ao nosso canal do Youtube. Começamos em abril de 2016, mas já temos mais de 06:00 h de treinamentos gratuitos e este número irá aumentar. Vídeos novos todos os sábados.