Hvordan kan jeg bruke regulære uttrykk i Excel og dra nytte av Excels kraftige rutenettlignende oppsett for datamanipulering?
Jeg forstår at Regex ikke er ideelt for mange situasjoner (Å bruke eller ikke bruke regulære uttrykk?) siden excel kan bruke Left
, Mid
, Right
, Instr
type kommandoer for lignende manipulasjoner.
Regulære uttrykk brukes til mønstermatching.
Følg disse trinnene for bruk i Excel:
Trinn 1: Legg til VBA-referanse til "Microsoft VBScript Regular Expressions 5.5";
Trinn 2: Definer mønsteret ditt
Grunnleggende definisjoner:
-
Rekkevidde.
a-z
samsvarer med små bokstaver fra a til z.0-5
samsvarer med et hvilket som helst tall fra 0 til 5.[]
Matcher nøyaktig ett av objektene innenfor disse parentesene.
[a]
samsvarer med bokstaven a.[abc]
samsvarer med en enkelt bokstav som kan være a, b eller c.[a-z]
samsvarer med en hvilken som helst liten bokstav i alfabetet.()
Grupperer ulike treff for returformål. Se eksempler nedenfor.
{}
Multiplikator for gjentatte kopier av mønsteret som er definert før den.
[a]{2}
matcher to påfølgende små bokstaver a: aa
[a]{1,3}
samsvarer med minst én og opptil tre små bokstaver a
, aa
, aaa
.+
Matcher minst ett, eller flere, av mønstrene som er definert før det.
a+
matche påfølgende a'er a
, aa
, aaa
og så videre.?
Matcher null eller ett av mønstrene som er definert før det.
[a-z]?
samsvarer med tom streng eller en hvilken som helst liten bokstav.*
Matcher null eller mer av mønsteret som er definert foran.
[a-z]*
samsvarer med tom streng eller en streng med små bokstaver..
Matcher et hvilket som helst tegn unntatt ny linje \n
.
a.
Matcher en streng på to tegn som begynner med a og slutter med hva som helst unntatt \n
.|
OR-operatoren
a|b
betyr at enten a
eller b
kan matches.rød|hvit|oransje
samsvarer med nøyaktig én av fargene.Operatoren ^
NOT
[^0-9]
ikke inneholde et tall.[^aA]
være en liten a
eller en stor A
.\
Unngår spesialtegn som følger (overstyrer oppførselen ovenfor)
\.
, \\
, \(
, \?
, \$
, \^
Forankringsmønstre:
^
Match må forekomme i begynnelsen av strengen
^a
Første tegn må være små bokstaver a
.^[0-9]
Første tegn må være et tall.$
Match må forekomme på slutten av strengen.
a$
Siste tegn må være små bokstaver a
.Presedens-tabell:
Order Name Representation
1 Parentheses ( )
2 Multipliers ? + * {m,n} {m, n}?
3 Sequence & Anchors abc ^ $
4 Alternation |
Forhåndsdefinerte karakterforkortelser:
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
Eksempel 1: Kjør som makro
Følgende eksempelmakro ser på verdien i celle A1
for å se om de første 1 eller 2 tegnene er sifre. I så fall fjernes de, og resten av strengen vises. Hvis ikke, vises en boks som forteller deg at det ikke er funnet noe treff. Celle A1
verdier av 12abc
vil returnere abc
, verdien av 1abc
vil returnere abc
, verdien av abc123
vil returnere "Not Matched" fordi sifrene ikke var i begynnelsen av strengen.
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
Eksempel 2: Kjøres som en funksjon i cellen
Dette eksemplet er det samme som eksempel 1, men er satt opp til å kjøre som en cellefunksjon. For å bruke, endre koden til dette:
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
Plasser strengene dine ("12abc") i celle A1
. Skriv inn denne formelen =simpleCellRegex(A1)
i celle B1
og resultatet blir "abc".
Skriv inn bildebeskrivelsen her]1.
Eksempel 3: Loop Through Range
Dette eksemplet er det samme som eksempel 1, men sløyfer gjennom en rekke celler.
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
Eksempel 4: Oppdeling av forskjellige mønstre
Dette eksempelet løkker gjennom et område (A1
, A2
og A3
) og ser etter en streng som starter med tre sifre etterfulgt av et enkelt alfategn og deretter 4 numeriske sifre. Utdataene deler opp mønstertreffene i tilstøtende celler ved hjelp av ()
. $1
representerer det første mønsteret som samsvarer innenfor det første settet med ()
.
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
Resultater:
Ytterligere mønstereksempler
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
For å bruke regulære uttrykk direkte i Excel-formler kan følgende UDF (brukerdefinert funksjon) være til hjelp. Den eksponerer mer eller mindre direkte funksjonalitet for regulære uttrykk som en Excel-funksjon.
Det tar 2-3 parametere.
$0
, $1
, $2
og så videre. $0er hele treffet,
$1og oppover tilsvarer de respektive treffgruppene i det regulære uttrykket. Standard er
$0`.Henter ut en e-postadresse:
=regex("Peter Gordon: [email protected], 47", "\w+@\w+\.\w+")
=regex("Peter Gordon: [email protected], 47", "\w+@\w+\.\w+", "$0")
Resultater i: [email protected]
Henter ut flere delstrenger:
=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "E-Mail: $2, Name: $1")
Resultater i: E-post: [email protected], Navn: Peter Gordon
Å ta fra hverandre en kombinert streng i en enkelt celle i komponentene i flere celler:
=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "$" & 1)
=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "$" & 2)
Resulterer i: Peter Gordon
[email protected]
...
For å bruke denne UDF-en gjør du følgende (omtrent basert på denne Microsoft-siden. De har god tilleggsinformasjon der!):
ALT+F11
for å åpne Microsoft Visual Basic for Applications Editor.Regex
og funksjonen regex
).Andre ikon i ikonraden -> Modul]4. 4. Sett inn følgende i det store tekstvinduet i midten:
Function regex(strInput As String, matchPattern As String, Optional ByVal outputPattern As String = "$0") As 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 As Object, replaceMatches As Object, replaceMatch As Object
Dim replaceNumber As Integer
With inputRegexObj
.global = True
.MultiLine = True
.IgnoreCase = False
.pattern = matchPattern
End With
With outputRegexObj
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = "\$(\d+)"
Avslutt med
With outReplaceRegexObj
.Global = True
.MultiLine = True
.IgnoreCase = False
End With
Set inputMatches = inputRegexObj.Execute(strInput)
If inputMatches.Count = 0 Then
regex = False
Ellers
Sett replaceMatches = outputRegexObj.Execute(outputPattern)
For Each replaceMatch In replaceMatches
replaceNumber = replaceMatch.SubMatches(0)
outReplaceRegexObj.Pattern = "\$" " replaceNumber
Hvis replaceNumber = 0 Then
outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).Value)
Ellers
If replaceNumber > inputMatches(0).SubMatches.Count Then
'regex = "En til høy $ tag funnet. Største tillatte er $ " " inputMatches(0).SubMatches.Count " "."
regex = CVErr(xlErrValue)
Avslutt funksjon
Ellers
outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1))
End If
End If
Neste
regex = outputPattern
End If
End Function
Her er mitt forsøk:
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