Como posso usar expressões regulares no Excel e tirar proveito da poderosa configuração de grade do Excel's para manipulação de dados?
Eu entendo que Regex não é ideal para muitas situações (Para utilizar ou não utilizar expressões regulares?) uma vez que o excel pode utilizar comandos do tipo Esquerda',
Médio', Direito',
Instr' para manipulações similares.
(http://en.wikipedia.org/wiki/Regular_expressions) são usadas para a correspondência de padrões.
Para usar no Excel, siga estes passos :
**Passo 1***: Adicionar referência VBA a "Microsoft VBScript Regular Expressions 5.5"
**Passo 2***: Defina o seu padrão
Definições básicas:
"Gama".
0-5
corresponde a qualquer número de 0 a 5[]
Faça corresponder exatamente um dos objetos dentro destes parênteses.
[a]
coincide com a letra a[abc]
corresponde a uma única letra que pode ser a, b ou c[a-z]
corresponde a qualquer letra minúscula do alfabeto.()
Agrupa diferentes partidas para fins de retorno. Veja os exemplos abaixo.
Multiplicador para cópias repetidas do padrão definido antes dele.
[a]{2}' corresponde a duas letras minúsculas a consecutivas:
aa``[a]{1,3}
corresponde a pelo menos uma e até três letras minúsculas a
, aa
, aaa
.+
Combine pelo menos um, ou mais, do padrão definido antes dele.
a+
irá corresponder a's a
, aa
, aaa
, e assim sucessivamente"Match zero ou um dos padrões definidos antes dele.
[a-z]?
corresponde a uma string vazia ou a qualquer letra minúscula.*
Combine zero ou mais do padrão definido antes dele.
[a-z]*
corresponde a string vazia ou string de letras minúsculas."Combina com qualquer personagem, exceto a nova linha".
a.
Combina uma cadeia de dois caracteres começando com um e terminando com qualquer coisa exceto \n
.OU operador
a|b
significa que a
ou b
podem ser combinadas.vermelho|branco|laranja
combina exatamente com uma das cores.^
NÃO operador
[^0-9]
caracteres não podem conter um número[^aA]`` caracteres não podem ser minúsculas
aou maiúsculas
A`."Escapa ao carácter especial que se segue (sobrepõe-se ao comportamento acima)
Padrões de ancoragem:
^
A correspondência deve ocorrer no início da cadeia
^a
O primeiro caractere deve ser a letra minúscula `a'.^[0-9]
O primeiro caractere deve ser um número.$
A partida deve ocorrer no final do fio
a$
O último caractere deve ser letra minúscula `a'.Tabela de precedências:
Order Name Representation
1 Parentheses ( )
2 Multipliers ? + * {m,n} {m, n}?
3 Sequence & Anchors abc ^ $
4 Alternation |
Abreviaturas de caracteres pré-definidos:
abr same as meaning
\d [0-9] Any single digit
\D [^0-9] Any single character that's not a digit
\w [a-zA-Z0-9_] Any word character
\W [^a-zA-Z0-9_] Any non-word character
\s [ \r\t\n\f] Any space character
\S [^ \r\t\n\f] Any non-space character
\n [\n] New line
Exemplo 1: Run as macro
O seguinte exemplo de macro olha para o valor na célula A1
para ver se os primeiros 1 ou 2 caracteres são dígitos. Se assim for, eles são removidos e o resto da string é exibido. Se não, então uma caixa aparece dizendo que nenhuma correspondência é encontrada. A célula A1
valores de 12abc
retornará abc
, o valor de 1abc
retornará abc
, o valor de abc123
retornará "Not Matched" porque os dígitos não estavam no início da string.
Private Sub simpleRegex()
Dim strPattern As String: strPattern = "^[0-9]{1,2}"
Dim strReplace As String: strReplace = ""
Dim regEx As New RegExp
Dim strInput As String
Dim Myrange As Range
Set Myrange = ActiveSheet.Range("A1")
If strPattern <> "" Then
strInput = Myrange.Value
With regEx
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = strPattern
End With
If regEx.Test(strInput) Then
MsgBox (regEx.Replace(strInput, strReplace))
Else
MsgBox ("Not matched")
End If
End If
End Sub
Exemplo 2: Executar como uma função in-cell
Este exemplo é o mesmo do exemplo 1, mas é configurado para ser executado como uma função in-cell. Para usar, altere o código para este:
Function simpleCellRegex(Myrange As Range) As String
Dim regEx As New RegExp
Dim strPattern As String
Dim strInput As String
Dim strReplace As String
Dim strOutput As String
strPattern = "^[0-9]{1,3}"
If strPattern <> "" Then
strInput = Myrange.Value
strReplace = ""
With regEx
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = strPattern
End With
If regEx.test(strInput) Then
simpleCellRegex = regEx.Replace(strInput, strReplace)
Else
simpleCellRegex = "Not matched"
End If
End If
End Function
Coloque as suas cordas ("12abc") na célula A1
. Digite esta fórmula =simpleCellRegex(A1)
na célula B1
e o resultado será "abc".
Exemplo 3: Loop Through Range
Este exemplo é o mesmo que o exemplo 1, mas percorre um intervalo de células.
Private Sub simpleRegex()
Dim strPattern As String: strPattern = "^[0-9]{1,2}"
Dim strReplace As String: strReplace = ""
Dim regEx As New RegExp
Dim strInput As String
Dim Myrange As Range
Set Myrange = ActiveSheet.Range("A1:A5")
For Each cell In Myrange
If strPattern <> "" Then
strInput = cell.Value
With regEx
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = strPattern
End With
If regEx.Test(strInput) Then
MsgBox (regEx.Replace(strInput, strReplace))
Else
MsgBox ("Not matched")
End If
End If
Next
End Sub
**Exemplo 4***: Dividindo padrões diferentes
Este exemplo faz loops através de um intervalo (A1
, A2
& A3
) e procura por uma string começando com três dígitos seguidos por um único caracter alfa e depois 4 dígitos numéricos. A saída divide as combinações de padrões em células adjacentes utilizando a função ()
. $1
representa o primeiro padrão que combina dentro do primeiro conjunto de ()
.
Private Sub splitUpRegexPattern()
Dim regEx As New RegExp
Dim strPattern As String
Dim strInput As String
Dim Myrange As Range
Set Myrange = ActiveSheet.Range("A1:A3")
For Each C In Myrange
strPattern = "(^[0-9]{3})([a-zA-Z])([0-9]{4})"
If strPattern <> "" Then
strInput = C.Value
With regEx
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = strPattern
End With
If regEx.test(strInput) Then
C.Offset(0, 1) = regEx.Replace(strInput, "$1")
C.Offset(0, 2) = regEx.Replace(strInput, "$2")
C.Offset(0, 3) = regEx.Replace(strInput, "$3")
Else
C.Offset(0, 1) = "(Not matched)"
End If
End If
Next
End Sub
Resultados:
**Exemplos de Padrões Adicionais***
String Regex Pattern Explanation
a1aaa [a-zA-Z][0-9][a-zA-Z]{3} Single alpha, single digit, three alpha characters
a1aaa [a-zA-Z]?[0-9][a-zA-Z]{3} May or may not have preceeding alpha character
a1aaa [a-zA-Z][0-9][a-zA-Z]{0,3} Single alpha, single digit, 0 to 3 alpha characters
a1aaa [a-zA-Z][0-9][a-zA-Z]* Single alpha, single digit, followed by any number of alpha characters
</i8> \<\/[a-zA-Z][0-9]\> Exact non-word character except any single alpha followed by any single digit
Para fazer uso de expressões regulares diretamente nas fórmulas do Excel, o seguinte UDF (função definida pelo usuário) pode ser de ajuda. Ele expõe mais ou menos diretamente a funcionalidade das expressões regulares como uma função do Excel.
São necessários 2-3 parâmetros.
$0
, $1
, $2
, e assim por diante. $0
é a partida inteira, $1
e acima corresponde aos respectivos grupos de partida na expressão regular. O valor padrão é $0
.Extraindo um endereço de e-mail:
=regex("Peter Gordon: [email protected], 47", "\w+@\w+\.\w+")
=regex("Peter Gordon: [email protected], 47", "\w+@\w+\.\w+", "$0")
Resultados em: [email protected]
Extracção de vários substratos:
=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "E-Mail: $2, Name: $1")
Resultados em: E-Mail: [email protected], Nome: Peter Gordon``
Desmontar uma cadeia combinada em uma única célula em seus componentes em várias células:
=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "$" & 1)
=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "$" & 2)
Resultados em: "Peter Gordon" "[email protected]" ...
Para utilizar este UDF faça o seguinte (mais ou menos baseado em esta página da Microsoft. Eles têm aí algumas boas informações adicionais!):
ALT+F11
para abrir o editor Microsoft Visual Basic for Applications.Regex
e a função regex
causa #NAME! erros).4. Na janela de texto grande do meio, insira o seguinte:
Função regex(strInput As String, matchPattern As String, Optional ByVal outputPattern As String = "$0") Como Variante
Dim inputRegexObj As New VBScript_RegExp_55.RegExp, outputRegexObj As New VBScript_RegExp_55.RegExp, outReplaceRegexObj As New VBScript_RegExp_55.RegExp
Dim inputMatches As Object, replaceMatches As Object, replaceMatch As Object
Dim substituirNumber As Integer
Com inputRegexObj
Global = Verdadeiro
MultiLinha = Verdadeiro
IgnoreCase = Falso
Padrão = matchPattern (padrão)
Fim Com
Com outputRegexObj
Global = Verdadeiro
MultiLinha = Verdadeiro
IgnoreCase = Falso
Padrão = "$(\d+)"
Fim Com
Com outReplaceRegexObj
Global = Verdadeiro
MultiLinha = Verdadeiro
IgnoreCase = Falso
Fim Com
Set inputMatches = inputRegexObj.Execute(strInput)
Se inputMatches.Count = 0 Então
regex = Falso
Senão
Set replaceMatches = outputRegexObj.Execute(outputPattern)
Para Cada ReposiçãoMatch In replaceMatches
replaceNumber = replaceMatch.SubMatches(0)
outReplaceRegexObj.Pattern = "\$" & replaceNumber
Se substituirNúmero = 0 Então
outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).Value)
Senão
Se substituirNumber > inputMatches(0).SubMatches.Count Então
'regex = "A a tag high $ encontrada. A maior permitida é $" & inputMatches(0).SubMatches.Count & " "."
regex = CVErr(xlErrValue)
Função de Saída
Senão
outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1))
Fim Se
Fim Se
Próximo
regex = outputPattern
Fim Se
Função Final
Aqui está a minha tentativa:
Function RegParse(ByVal pattern As String, ByVal html As String)
Dim regex As RegExp
Set regex = New RegExp
With regex
.IgnoreCase = True 'ignoring cases while regex engine performs the search.
.pattern = pattern 'declaring regex pattern.
.Global = False 'restricting regex to find only first match.
If .Test(html) Then 'Testing if the pattern matches or not
mStr = .Execute(html)(0) '.Execute(html)(0) will provide the String which matches with Regex
RegParse = .Replace(mStr, "$1") '.Replace function will replace the String with whatever is in the first set of braces - $1.
Else
RegParse = "#N/A"
End If
End With
End Function