TUTORIAL: COMO USAR O REGEX NO EXCEL

Tem alguma dica para o uso do Excel? Algum atalho especial? Algum macete? Aqui é o lugar para você compartilhar!

TUTORIAL: COMO USAR O REGEX NO EXCEL

Mensagempor Syrax » Dom Abr 02, 2017 12:01 am

Vou ensinar nesse tutorial, para quem ainda não conhece, uma das ferramentas mais poderosas e complexas de texto do mundo
o Regex ou expressão regular (regular expression).
Já conhecia o Regex no mIRC, depois em PHP, HTML, e depois no VBA.
No começo você vai olhar e falar: "pra que eu vou usar isso?", em algumas linguagens o uso do Regex é muito comum
Java, Javascript, C++, e PHP, quem programa nessas linguagens, conhece e sabe do que estou falando

Primeiro, você precisa ativar essa biblioteca pelas referências do VBA, indo em
Ferramentas > Referências > clique em "Microsoft VBScript Regular Expressions 5.5 conforme a imagem abaixo

regex1.jpg



Se você não quiser carregar todas as vezes, você pode usar a função CreateObject para criar o objeto "VBScript.regExp"

Vamos ver nesse exemplo abaixo, como conseguir apenas os valores númericos de uma string

Código: Selecionar todos
Sub teste()
Dim reg As Object ' identificamos que reg é um objeto
Dim texto As String ' identificamos que texto é onde está o nosso texto como string

Set reg = CreateObject("vbscript.regexp") ' criamos o objeto regex
texto = "teste 123" ' informamos que o texto que vamos usar é "teste 123"
reg.Pattern = "(\d+)" ' informamos o que vai ser buscado nesse texto, \d significa "digit" o que são os números, quando voce _
informa \d+ procura mais de um número na string

Set match = reg.Execute(texto) ' voce informa que match agora é um objeto e carrega as informações do que está nos resultados do regex _
a função do "Execute" é executar o comando, ou seja, buscar o que está no pattern na variável texto
MsgBox match.Item(0) ' use a propriedade "item" e o número da referência para saber o resultado, nesse caso 123

End Sub


mas, se você quiser pegar apenas as letras e não os números?
Nesse caso, usaria pattern ([a-z]+)

Código: Selecionar todos
Sub teste()
Dim reg As Object ' identificamos que reg é um objeto
Dim texto As String ' identificamos que texto é onde está o nosso texto como string

Set reg = CreateObject("vbscript.regexp") ' criamos o objeto regex
texto = "teste 123" ' informamos que o texto que vamos usar é "teste 123"
reg.Pattern = "([a-z]+)" ' informamos o que vai ser buscado nesse texto, [a-z]+ significa uma sequência de letras de "a" a "z", quando voce _
informa [a-z]+ procura mais de uma letra na string

Set match = reg.Execute(texto) ' voce informa que match agora é um objeto e carrega as informações do que está nos resultados do regex _
a função do "Execute" é executar o comando, ou seja, buscar o que está no pattern na variável texto
MsgBox match.Item(0) ' use a propriedade "item" e o número da referência para saber o resultado, nesse caso 123

End Sub


Ficou um pouco confuso?
Simples

\d significa um digito, \d+ uma sequência de digitos
\w significa uma letra ou número, \w+ uma sequência de letras ou números
\D significa qualquer coisa menos um digito
\W significa qualquer coisa menos uma letra ou número
\s signifca espaço
\S significa qualquer coisa, menos um espaço

No Regex, minúsculo é diferente de maiúsuclo, ou seja TESTE é diferente de teste

No exemplo abaixo vamos procurar "teste" na string "TESTE 123"

O exemplo abaixo retorna "Nenhuma referencia encontrada"
Código: Selecionar todos
Sub teste()
Dim reg As Object
Dim texto As String

Set reg = CreateObject("vbscript.regexp")
texto = "TESTE 123"
reg.Pattern = "teste"
If reg.Execute(texto).Count > 0 Then ' a propriedade count informa quantas referências foram encontradas
mgsbox reg.Execute(texto).Item(0)
Else
MsgBox "Nenhuma referencia encontrada"
End If
End Sub


Para que isso não aconteça, use a propriedade "ignorecase" que ignora se a string contém letras maiúsculas ou minúsculas

Código: Selecionar todos
Sub teste()
Dim reg As Object
Dim texto As String

Set reg = CreateObject("vbscript.regexp")
texto = "TESTE 123"
reg.Pattern = "teste"
reg.IgnoreCase = True ' informa que o regex pode ignorar se as letras estão maiúsculas ou minúsculas
If reg.Execute(texto).Count > 0 Then ' a propriedade count informa quantas referências foram achadas
MsgBox reg.Execute(texto).Item(0)
Else
MsgBox "Nenhuma referencia encontrada"
End If
End Sub



Há também, uma outra função, chamada "Global", que informa ao regex, para não parar na primeira referência encontrada

Veja abaixo

Código: Selecionar todos
Sub teste()
Dim reg As Object
Dim texto As String

Set reg = CreateObject("vbscript.regexp")
texto = "teste 123 456"
reg.Pattern = "(\d+)"
For Each matches In reg.Execute(texto)
MsgBox matches ' retorna apenas 123
Next matches
End Sub


enquanto

Código: Selecionar todos
Sub teste()
Dim reg As Object
Dim texto As String

Set reg = CreateObject("vbscript.regexp")
texto = "teste 123 456"
reg.Pattern = "(\d+)"
reg.Global = True ' informa para o regex não parar na primeira referência encontrada
For Each matches In reg.Execute(texto)
MsgBox matches ' retorna 123 456
Next matches
End Sub


Ficou mais fácil? você ainda está se perguntando porque usar o Regex?
Muitos sites utilizam o regex para válidar emails

exemplo


Código: Selecionar todos
Sub teste()
Sub teste()
Dim reg As Object
Dim texto As String

Set reg = CreateObject("vbscript.regexp")
texto = "email@provedor.com.br" ' informa um email
reg.Pattern = "\w+[^\s@%$()#!]@\w+\.\b(?:com|co|edu|net|org)\b(?:\.br)?$" ' informa as condições de busca
reg.Global = True
If reg.Test(texto) = True Then ' executa o regex, nesse caso a propriedade test, retorna true ou false
MsgBox "Este é um email válido"
Else
MsgBox "Este NÃO é um email válido"
End If
End Sub



Vamos entender o que a pattern informa que precisa ser buscado

reg.Pattern = "\w+[^\s@%$()#!]@\w+\.\b(?:com|co|edu|net|org)\b(?:\.br)?$" ' informa as condições de busca
\w+ informa qualquer sequência de letras ou números
agora vamos aprender algo novo
quando você colocar uma combinação de caracteres dentro de uma [ ] você informa o que você quer buscar na string
mas nesse caso, nós colocamos um "^" no começo, o que significa negação (apenas quando dentro de uma [ ] ), ou seja, o que não queremos na string
nesse caso, informamos que antes do @ não queremos nenhum caractere que seja igual "\s@%$()#!"
depois do @ \w+ uma sequência de letras seguidas por ".", toda vez que queremos informar um "." no regex usamos uma barra \ para informar que o "." é texto e não comando
\b(?:com|co|edu|net|org)\b - primeiro, tudo que estiver dentro de \b \b significa "somente" ou seja não pode haver mais nada antes ou depois

vamos a um exemplo primeiro, vamos supor que você esteja procurando tudo que comece com a palavra "test" ou que contenha "test"
reg.Pattern = "(test)"
se a string houver "testando" ou "atestado"
o reg.test retorna true, pois "test" está em ambas as strings.
Mas agora, se você quiser procurar apenas a palavra "test" você precisa especificar \b(test)\b que retornaria false no reg.Test para "testando" ou "atestado"
e retornaria true, para "test"
Nesse caso, combina com qualquer provedor que seja: .com, .co, .edu, .net, .org


Depois disso, colocamos um ?: o que significa que esse match, não pode ser considerado uma referência e não retornaria nada em reg.execute(texto).item(0)
após isso especificamos que o email pode conter um .br ou não, é o que significa o "?" após a informação

vamos a outro exemplo

Código: Selecionar todos
Sub teste()
Dim reg As Object
Dim texto As String

Set reg = CreateObject("vbscript.regexp")
texto = "senhor"
reg.Pattern = "senhora?" ' informa que o último caractere foi ignorado, ele pode estar ou não na string
reg.IgnoreCase = True
Debug.Print reg.Test(texto) ' nesse caso retorna true para senhor ou senhora
End Sub


Lembrando que o "?" usado após o último caractere informa que ele pode estar ou não na string... mas dai você pergunta:
Então porque você usou o (?:\.br)?

Simples, o "?" ignora apenas o último caractere, e nesse caso, especificamos uma sequência de caracteres ".br"

o "$" significa, "fim da string" ou seja, nada mais pode estar depois da string

nesse caso, retornaria false, caso houvesse mais alguma coisa depois de .com sem que seja .br
por exemplo
exemplo@provedor.com... = falso
exemplo@provedor.com.us = falso


Vamos ver então. como validar um website

Código: Selecionar todos
Sub teste()
Dim reg As Object
Dim texto As String

Set reg = CreateObject("vbscript.regexp")
texto = "https://www.google.com.br" ' informa um website
reg.Pattern = "(?:https?:\/\/)?w{3}\.\w+\.(?:com|edu|net|org)(?:\.br)?$" ' informa as condições de busca
reg.IgnoreCase = True
If reg.Test(texto) = True Then
MsgBox "Este é um website válido"
Else
MsgBox "Este NÃO é um website válido"
End If
End Sub


Vamos entender as condições de busca do reg.Pattern
reg.Pattern = "(?:https?:\/\/)?w{3}\.\w+\.(?:com|edu|net|org)(?:\.br)?$" ' informa as condições de busca

Nesse caso, combina para websites que comecem ou não com http ou https
colocamos o (?:https?)? como sabemos o "?:" informa que ele não pode ser referência para uma chamada de propriedade .item
o "?" depois do "s" significa que ele pode conter http ou https, \/\/ signifca duas barras // , usamos o \ para ignorar a /, pois senão, pode ser usado como condição no regex
e o "?" depois que fechamos significa que pode conter ou não http:// ou https://

w{3} significa que deve conter a letra "w" 3 vezes, e após isso somente .com, .edu, .net, ou .org mesmo exemplo que usamos no email

Agora vamos aprender como validar um telefone celular com DDD

Código: Selecionar todos
Sub teste()
Dim reg As Object
Dim texto As String

Set reg = CreateObject("vbscript.regexp")
telefone = "(11) 99888-8888" ' informa um número de telefone
reg.Pattern = "^(((\(\d{2}\)|\d{2})\s?)?[9]\d{4}-?\d{4})\b" ' informa o que deve ser buscado
reg.Global = True ' informa que o regex não pode parar na primeira referência
If reg.Test(telefone) = True Then
MsgBox "Este é um número de telefone válido"
Else
MsgBox "Este NÃO é um número de telefone válido"
End If
End Sub



Vamos entender a Pattern
reg.Pattern = "^(((\(\d{2}\)|\d{2})\s?)?[9]\d{4}-?\d{4})\b"

Primeiro:
(((\(\d{2}\)|\d{2})\s?)?
para fazer com que a pattern identifique números com DDD, do modo (11) ou 11
vamos fazer com que o regex entenda que ( ) são caracteres e não referências, vamos usar a barra \ para isso
\(\d{2}\) significa que pode conter dois digitos dentro do ( ) e não mais ou menos que isso
usamos o "|" para indicar que pode haver outra forma de entrar com o DDD que pode ser sem o ( ) ou seja apenas o "11"
\d{2}
Depois usamos o ((\(\d{2}\)|\d{2})\s?) \s? significa que pode haver ou não, espaço depois do DDD
((\(\d{2}\)|\d{2})\s?)? essa condição informa que pode haver ou não o DDD

Usamos o [9] para indicar que obrigatoriamente a sequência de números deve começar com 9
depois disso, informamos que deve haver uma sequencia de 4 números após o 9 "\d{4}"
usando "-?" informamos que pode haver ou não um hífen que separa os números
E por último informamos que depois deve haver outra sequência de 4 números

Reparem que foi usado o "^" no começo da pattern... fora do [ ] o "^" significa "começo" ou seja, a string deve começar daquele jeito para ser válida
e no final foi usado um "\b" o que significa que não deve haver mais nada após a sequência de 4 números


Exemplo:
(11) 99888-8888 = verdadeiro
(11)99888-8888 = verdadeiro
(11) 998888888 = verdadeiro
(11)998888888 = verdadeiro
11 998888888 = verdadeiro
11998888888 = verdadeiro
1199888-8888 = verdadeiro
11 99888-8888 = verdadeiro
99888-8888 = verdadeiro
998888888 = verdadeiro
798888888 = falso


Fiz uma função para ser usada no Excel
Para isso você precisa inserir um módulo e colar o código abaixo dentro dele

Código: Selecionar todos
Function REGEX(txt As String, regx As String, ref As Integer)
Dim reg1
Set reg1 = CreateObject("vbscript.regexp")
Dim y
reg1.Pattern = regx

If ref = 1 Then
REGEX = reg1.Execute(txt).Item(0)


ElseIf ref > 1 Then
reg1.Global = True
REGEX = reg1.Execute(txt).Item(ref - 1)

ElseIf ref = 0 Then
reg1.Global = True
For Each match In reg1.Execute(txt)
y = y + " " + match
Next
REGEX = y
End If
End Function


Para usar é simples, vamos supor que o texto "teste 123 456" está em A1
Para usar, voce informa primeiro a referência ou o texto, a pattern, e depois a referência do regex

Código: Selecionar todos
=REGEX(A1;"(\d+)";1) ' retorna a primeira referência "123"
=REGEX(A1;"(\d+)";2) ' retorna a segunda referência "456"
=REGEX(A1;"(\d+)";0) ' retorna todas as referências juntas "123 456"



Bom, qualquer dúvida postem abaixo, se você gostou do tutorial ou se ajudou você, clique no "joinha" ao lado de citar
vou deixar uns links abaixo para que vocês possam procurar mais, o que eu ensinei aqui, foi apenas o básico do Regex para que vocês possam aprender mais
Pois o Regex é enorme, e complexo

Em breve mais tutoriais

regex, regex vba, regular expression vba, vbscript.regexp, as new regexp, CreateObject("vbscript.regexp")
https://pt.wikipedia.org/wiki/Express%C3%A3o_regular
http://www.regular-expressions.info/vbscript.html
https://regex101.com/
Se essa resposta te ajudou e resolveu o seu problema, clique no "joinha" ao lado de citar, e não esqueça de colocar o tópico como resolvido, clicando no

For this post the author Syrax thanked: 2
Alan.xls (Qua Ago 29, 2018 3:28 pm) • brunoxro (Seg Abr 03, 2017 10:57 pm)
Avatar do usuário
Syrax
Membro
Membro
 
Mensagens: 166
Registrado em: Sáb Fev 04, 2017 6:34 pm
Has thanked: 4 times
Have thanks: 135 times

{ SO_SELECT }

Re: TUTORIAL: COMO USAR O REGEX NO EXCEL

Mensagempor fernando.fernandes » Dom Abr 02, 2017 3:17 pm

ótimo tutorial, um tema pouquíssimo explorado e muito, mas muito importante para quem faz sistemas robustos nos quais qualquer ganho em desempenho, é muito bem vindo.
Usei RegEx muito, no passado, e vou confessar que não é fácil, mas é muito útil. Mas útil demais!

Obrigado Syrax, pelo tutorial !

fernando.fernandes
 

Re: TUTORIAL: COMO USAR O REGEX NO EXCEL

Mensagempor gigliozzi » Dom Mar 17, 2019 1:10 am

Uma das mais completas explicações que já vi publicada. Estou começando com Regex agora e percebo que há muito aprender. Muito obrigado! :D
William Gigliozzi
gigliozzi
Membro
Membro
 
Mensagens: 1
Registrado em: Dom Mar 17, 2019 1:05 am
Has thanked: 0 time
Have thanks: 0 time

Re: TUTORIAL: COMO USAR O REGEX NO EXCEL

Mensagempor wagner » Dom Mar 17, 2019 2:45 pm

William,

Boa tarde!

Seja muito bem vindo ao fórum.

Para aproveitar ao máximo o fórum e sempre manter o mesmo de forma organizada, sugiro ler os tópico da regras abaixo:
viewtopic.php?f=7&t=16757
viewtopic.php?f=7&t=203
viewtopic.php?f=7&t=7903
viewtopic.php?f=7&t=3841
viewtopic.php?f=7&t=12600
viewtopic.php?f=7&t=3371

Algumas solicitações especiais que pedimos, por gentileza, ficar atento:
1 - Não inserir no titulo de suas postagens expressões como Help, Ajuda, etc. O título deve ser um resumo da sua necessidade para que outras pessoas que tenham a mesma dúvida possam efetuar a pesquisa e achar como foi resolvido.
2 - Não insira em suas mensagens frases todas escritas em letras maiúsculas. Isso, na Internet, é compreendido como gritos e muitos usuários sequer respondem somente por esse fato!
3 - Insira sempre um arquivo exemplo compactado com .ZIP aqui mesmo no fórum. Existe, logo abaixo da caixa de mensagens, uma aba chamada "Adicionar um anexo" para essa finalidade. O arquivo exemplo deve ser pequeno, com apenas 5 linhas no máximo, compactado com .ZIP e ter o mesmo layout (nome do arquivo, nome das abas/guias/folhas, mesma linha/coluna onde os dados se iniciam) do arquivo original. Links de arquivos enviados para sites de compartilhamento de arquivos, muitas vezes são bloqueados pelas empresas, por conterem muitos vírus. Alguns usuários que acessam o fórum a partir de empresas não conseguem baixar tais arquivos.
4 - Não utilize a ferramenta CITAR para inserir o inteiro teor das mensagens que lhe são encaminhadas como resposta. Citações, se estritamente necessárias ao entendimento da mensagem que você quer enviar, devem ser apenas de pequenos trechos das mensagens.
5 - Se for postar códigos VBA aqui no fórum, utilize a ferramenta CODE localizada logo no início da caixa de mensagens (quinto botão da esquerda para a direita). As linhas de código devem ficar entre as palavras "CODE e /CODE".
6 - Agradeça sempre às pessoas que lhe responderam e às mensagens que atenderam a necessidade de sua demanda. Esse agradecimento deve ser clicando na mãozinha que fica localizada ao lado da ferramenta CITAR. Lembre-se: o fórum é gratuito e esse é o único incentivo para as pessoas que prestam ajuda. Você pode agradecer a quantos usuários quiser.
7 - O título ou o texto das mensagens postadas não devem ser escritos todo em letras maiúsculas. Na internet, tudo escrito em letras maiúsculas é interpretado como gritos e muitos usuários sequer olham para esse tipo de mensagem.
Se a resposta foi útil para você, por gentileza, Amigo, clique na mãozinha ao lado direito da ferramenta CITAR, no canto superior direito.

Atenciosamente
Wagner Morel
Avatar do usuário
wagner
Moderador
Moderador
 
Mensagens: 5331
Registrado em: Sáb Out 20, 2012 12:49 pm
Localização: Fortaleza - CE
Has thanked: 94 times
Have thanks: 2275 times


Voltar para Dicas & Macetes

Quem está online

Usuários navegando neste fórum: Nenhum usuário registrado e 1 visitante