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.


Clique aqui e leia mais sobre Excel VBA. https://www.guiadoexcel.com.br/vba/ O Guia do Excel foi criado por Marcos Rieper e oferece artigos, dicas, tutoriais e modelos de planilhas prontas. Aqui você encontra tudo sobre Excel, seja de nível básico, intermediário,  avançado e VBA. O Guia do Excel oferece diversos materiais completamente gratuitos para download. Navegue em nosso site e confira! Conheça também a nossa Loja do Excel https://loja.guiadoexcel.com.br/
Cursos

Curso Excel Completo – Curso Excel Básico + Curso Excel Avançado – Acesso Vitalício

R$218,00 R$179,00

COMPRAR
Cursos

Curso Excel Master – Curso Excel Básico + Curso Excel Avançado + Curso VBA Excel + LP – Acesso Vitalício

R$357,00 R$249,00

COMPRAR
Cursos

Curso Excel PRO – Curso Excel Avançado + Curso VBA Excel + Lógica de programação – Acesso Vitalício

R$258,00 R$199,00

COMPRAR
Cursos

Curso Excel Web – Curso VBA Excel + Lógica de programação + Curso Web Scraping VBA- Acesso Vitalício

R$388,90 R$309,00

COMPRAR

4 Comentários

  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.

Deixe uma resposta

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

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.