Hoe kan ik reguliere uitdrukkingen gebruiken in Excel en voordeel halen uit Excel's krachtige rasterachtige opzet voor gegevensmanipulatie?
Ik begrijp dat Regex niet ideaal is voor veel situaties (To use or not to use regular expressions?) aangezien excel Left
, Mid
, Right
, Instr
type commando's kan gebruiken voor soortgelijke manipulaties.
Reguliere uitdrukkingen worden gebruikt voor Pattern Matching.
Volg deze stappen om ze in Excel te gebruiken:
Stap 1: Voeg VBA referentie toe aan "Microsoft VBScript Reguliere Expressies 5.5"
Stap 2: Definieer uw patroon
Basis definities:
-
Bereik.
a-z
komt overeen met kleine letters van a tot z0-5
komt overeen met een cijfer van 0 tot 5[]
Komt overeen met precies één van de objecten binnen deze haakjes.
[a]
komt overeen met de letter a[abc]
komt overeen met een enkele letter die a, b of c kan zijn[a-z]
komt overeen met elke kleine letter van het alfabet.()
Groepeert verschillende overeenkomsten voor teruggave doeleinden. Zie onderstaande voorbeelden.
{}
Vermenigvuldiger voor herhaalde kopieën van het patroon dat ervoor gedefinieerd is.
[a]{2}
komt overeen met twee opeenvolgende kleine letters a: aa
[a]{1,3}
komt overeen met ten minste één en maximaal drie kleine letters a
, aa
, aaa
+
Komt overeen met ten minste één, of meer, van het daarvoor gedefinieerde patroon.
a+
komt overeen met opeenvolgende a's a
, aa
, aaa
, enzovoort?
Kom overeen met nul of één van het patroon ervoor.
[a-z]?
komt overeen met een lege tekenreeks of een enkele kleine letter.*
Komt overeen met nul of meer van het patroon dat ervoor gedefinieerd is.
[a-z]*
komt overeen met een lege tekenreeks of een tekenreeks met kleine letters..
Komt overeen met elk teken behalve de nieuwe regel `.
a.
Zoekt een tekenreeks van twee tekens beginnend met a en eindigend met iets behalve n
.|
OR operator
a|b
betekent dat òf a
òf b
gematched kan worden.rood|wit|oranje
komt overeen met precies een van de kleuren.^
NIET operator
[^0-9]
teken kan geen getal bevatten[^aA]
teken kan geen kleine letter a
of hoofdletter A
zijn`` Ontsnapt speciaal teken dat volgt (overschrijft bovenstaand gedrag)
.`, ```, `(`),
?, ``$
, `^
Anchoring Patterns:
^
Overeenstemming moet aan het begin van een string voorkomen
^a
Eerste teken moet kleine letter a
zijn^[0-9]
Eerste teken moet een cijfer zijn.$
De overeenkomst moet aan het einde van de tekenreeks voorkomen
a$
Laatste teken moet een kleine letter a
zijnVoorrangstabel:
Order Name Representation
1 Parentheses ( )
2 Multipliers ? + * {m,n} {m, n}?
3 Sequence & Anchors abc ^ $
4 Alternation |
Predefined Character Abbreviations:
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
Exemplaar 1: Uitvoeren als macro
De volgende voorbeeld macro kijkt naar de waarde in cel A1
om te zien of de eerste 1 of 2 karakters cijfers zijn. Zo ja, dan worden ze verwijderd en wordt de rest van de string weergegeven. Zo niet, dan verschijnt er een kader met de mededeling dat er geen overeenkomst is gevonden. Cel A1
waarde van 12abc
zal abc
opleveren, waarde van 1abc
zal abc
opleveren, waarde van abc123
zal "Not Matched" opleveren omdat de cijfers niet aan het begin van de tekenreeks stonden.
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
Voorbeeld 2: Uitvoeren als een in-cell functie
Dit voorbeeld is hetzelfde als voorbeeld 1 maar is ingesteld om als een in-cell functie te werken. Om het te gebruiken, wijzigt u de code als volgt:
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
Plaats uw tekenreeks ("12abc") in cel A1
. Voer deze formule =simpleCellRegex(A1)
in cel B1
in en het resultaat zal zijn "abc".
Exemplaar 3: Loop Through Range
Dit voorbeeld is hetzelfde als voorbeeld 1 maar maakt een lus door een bereik van cellen.
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
Voorbeeld 4: Het uit elkaar halen van verschillende patronen
Dit voorbeeld loopt door een bereik (A1
, A2
& A3
) en zoekt naar een tekenreeks die begint met drie cijfers, gevolgd door een enkel alfa teken en dan 4 numerieke cijfers. De uitvoer splitst de patroon overeenkomsten op in aangrenzende cellen door gebruik te maken van de ()
. $1
vertegenwoordigt het eerste patroon dat overeenkomt binnen de eerste reeks van ()
.
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
Resultaten:
Aanvullende patroonvoorbeelden
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
Om reguliere expressies direct in Excel formules te gebruiken kan de volgende UDF (user defined function) uitkomst bieden. Deze stelt de reguliere expressie-functionaliteit min of meer direct beschikbaar als een Excel-functie.
Het neemt 2-3 parameters.
$0
, $1
, $2
, enzovoort bevatten. $0
is de volledige match, $1
en hoger komen overeen met de respectievelijke match groepen in de reguliere expressie. Standaard wordt $0
gebruikt.Een e-mail adres uitpakken:
=regex("Peter Gordon: [email protected], 47", "\w+@\w+\.\w+")
=regex("Peter Gordon: [email protected], 47", "\w+@\w+\.\w+", "$0")
Resulteert in: [email protected]
Het extraheren van verschillende substrings:
=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "E-Mail: $2, Name: $1")
Resulteert in: E-Mail: [email protected], Naam: Peter Gordon`
Om een gecombineerde string in een enkele cel uit elkaar te halen in zijn componenten in meerdere cellen:
=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "$" & 1)
=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "$" & 2)
Resulteert in: Peter Gordon
[email protected]
...
Om deze UDF te gebruiken doe je het volgende (ruwweg gebaseerd op deze Microsoft pagina. Ze hebben wat goede aanvullende info daar!):
ALT+F11
om de Microsoft Visual Basic for Applications Editor te openen.Regex
en de functie regex
veroorzaakt #NAME! fouten).4. Voeg in het grote tekstvenster in het midden het volgende in:
Functie regex(strInput As String, matchPattern As String, Optional ByVal outputPattern As String = "$0") Als Variant
Dim inputRegexObj As New VBScript_RegExp_55.RegExp, outputRegexObj As New VBScript_RegExp_55.RegExp, outReplaceRegexObj As New VBScript_RegExp_55.RegExp
Dim inputMatches als Object, replaceMatches als Object, replaceMatch als Object
Dim replaceNumber Als Integer
Met inputRegexObj
.Global = True
.MultiLine = True
.NegeerHoofdletter = Onwaar
.Patroon = matchPatroon
Eindig met
Met outputRegexObj
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = "\$(\d+)"
Eindig met
Met outReplaceRegexObj
.Global = True
.MultiLine = True
.IgnoreCase = False
Eindig met
Set inputMatches = inputRegexObj.Execute(strInput)
Als inputMatches.Count = 0 Dan
regex = False
Anders
Set replaceMatches = outputRegexObj.Execute(outputPattern)
Voor elke replaceMatch in replaceMatches
replaceNumber = replaceMatch.SubMatches(0)
outReplaceRegexObj.Pattern = "\$" & replaceNumber
Als replaceNumber = 0 Dan
outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).Value)
Anders
Als replaceNumber > inputMatches(0).SubMatches.Count Dan
'regex = "Een tot hoge $-tag gevonden. Grootst toegestane is $" & inputMatches(0).SubMatches.Count & "."
regex = CVErr(xlErrValue)
Functie afsluiten
Else
outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1))
End If
Einde Als
Volgende
regex = outputPattern
End If
Einde Functie
Hier is mijn poging:
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