Kako uporabljati regularne izraze (Regex) v programu Microsoft Excel v celicah in zankah

Regularni izrazi se uporabljajo za ujemanje vzorcev.

Za uporabo v Excelu sledite naslednjim korakom :

Korak 1: Microsoft VBScript Regular Expressions 5.5"Dodajte referenco VBA na "Microsoft VBScript Regular Expressions 5.5"

• Izberite zavihek "Developer" ([I don't have this tab what do I do do?] (http://msdn.microsoft.com/en-us/library/bb608625.aspx))
• Izberite ikono "Visual Basic" v razdelku 'Code' traku
• V oknu "Microsoft Visual Basic for Applications" izberite "Tools" v zgornjem meniju.
• Izberite "Reference"
• Označite kvadratek poleg "Microsoft VBScript Regular Expressions 5.5" za vključitev v delovni zvezek.
• Kliknite "OK"

Korak 2: Opredelite svoj vzorec

Osnovne opredelitve:

- Obseg.

• Npr. a-z ustreza malim črkam od a do z
• Npr. 0-5 ustreza vsem številkam od 0 do 5

[] Ujemanje natančno enega od predmetov znotraj teh oklepajev.

• Npr. [a] ustreza črki a
• Npr. [abc] ustreza eni sami črki, ki je lahko a, b ali c
• Npr. [a-z] se ujema s katero koli posamezno malo črko abecede.

() Združuje različna ujemanja za namene vračanja. Glej spodnje primere.

{} Multiplikator za ponavljajoče se kopije vzorca, opredeljenega pred njim.

• Npr. [a]{2} ustreza dvema zaporednima malima črkama a: aa
• Npr. [a]{1,3} se ujema z najmanj eno in največ tremi malimi črkami a, aa, aaa

+ ujema vsaj enega ali več vzorcev, ki so opredeljeni pred njim.

• Npr. a+ se ujema z zaporednimi a'i a, aa, aaa in tako naprej

? Ujemanje nič ali enega vzorca, opredeljenega pred njim.

• Npr. vzorec je lahko prisoten ali ne, vendar se lahko ujema samo enkrat.
• Npr. [a-z]? se ujema s praznim nizom ali katero koli posamezno malo črko.

* Ujemanje nič ali več vzorcev, opredeljenih pred njim.

• Npr. nadomestni znak za vzorec, ki je lahko prisoten ali ne.
• Npr. [a-z]* ustreza praznemu nizu ali nizu malih črk.

. Pripade kateremu koli znaku razen novi vrstici \n

• Npr. a. ustreza nizu dveh znakov, ki se začne z a in konča s čimer koli razen \n

| OR operator

• Npr. a|b pomeni, da se lahko ujema bodisi a bodisi b.
• Npr. rdeča|bela|oranžna ustreza natanko eni od barv.

Operator ^ NOT

• Npr. znak [^0-9] ne more vsebovati števila
• Npr. znak [^aA] ne more biti mala črka a ali velika črka A

``` Izbeži posebni znak, ki sledi (nadomešča zgornje obnašanje)

• Npr. \., \\, \(, \?, \$, \^

Vzorci sidranja:

^ Ujemanje mora biti na začetku niza

• Npr. ^a Prvi znak mora biti mala črka a
• Npr. ^[0-9] Prvi znak mora biti številka.

$ Ujemanje mora biti na koncu niza

• Npr. a$ Zadnji znak mora biti mala črka a

Prednostna tabela:

Order  Name                Representation
1      Parentheses         ( )
2      Multipliers         ? + * {m,n} {m, n}?
3      Sequence & Anchors  abc ^ $
4      Alternation         |

Predvidene okrajšave znakov:

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

Primer 1: Zaženi kot makro

Naslednji makro primer pregleda vrednost v celici A1 in ugotovi, ali sta prva 1 ali 2 znaka številki. Če so, jih odstrani in prikaže preostanek niza. Če ne, se prikaže okence, ki sporoča, da ni bilo najdeno nobeno ujemanje. Vrednosti celic A1 12abc bodo vrnile abc, vrednost 1abc bo vrnila abc, vrednost abc123 bo vrnila "Ni ujeto", ker števke niso bile na začetku niza.

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

Primer 2: Zaženi kot funkcija v celici

Ta primer je enak kot primer 1, vendar je nastavljen tako, da se izvaja kot funkcija v celici. Za uporabo spremenite kodo v naslednjo:

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

V celico A1 postavite svoj niz ("12abc"). V celico B1 vnesite to formulo =simpleCellRegex(A1) in rezultat bo "abc".

Primer 3: Loop Through Range

Ta primer je enak kot primer 1, le da je zanka postavljena skozi območje celic.

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

Primer 4: Razdelitev različnih vzorcev

Ta primer se vrti skozi območje (A1, A2 & A3) in išče niz, ki se začne s tremi številkami, ki jim sledi en alfa znak in nato 4 številčne številke. Rezultat razdeli ujemanje vzorca v sosednja polja z uporabo (). $1 predstavlja prvi vzorec, ki se ujema s prvim nizom ().

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

Rezultati:

Dodatni primeri vzorcev

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

    \            Exact non-word character except any single alpha followed by any single digit

Za uporabo regularnih izrazov neposredno v formulah programa Excel vam je lahko v pomoč naslednja UDF (uporabniško določena funkcija). Bolj ali manj neposredno izpostavlja funkcionalnost regularnih izrazov kot funkcijo Excela.

Kako deluje

Funkcija sprejme 2-3 parametre.

1. Besedilo, za katerega želite uporabiti regularni izraz.
2. Regularni izraz.
3. Vrstica formata, ki določa, kako naj bo rezultat videti. Vsebuje lahko $0, $1, $2 in tako naprej. $0 je celotno ujemanje, $1 in naprej ustrezajo ustreznim skupinam ujemanj v regularnem izrazu. Privzeta vrednost je $0.

Nekaj primerov

Izvleček e-poštnega naslova:

=regex("Peter Gordon: some@email.com, 47", "\w+@\w+\.\w+")
=regex("Peter Gordon: some@email.com, 47", "\w+@\w+\.\w+", "$0")

Rezultati: some@email.com.

Izvleček več podrejencev:

=regex("Peter Gordon: some@email.com, 47", "^(.+): (.+), (\d+)$", "E-Mail: $2, Name: $1")

Rezultat je: E-Mail: some@email.com, ime: Peter Gordon

Razčlenitev kombiniranega niza v eni celici na njegove sestavne dele v več celicah:

=regex("Peter Gordon: some@email.com, 47", "^(.+): (.+), (\d+)$", "$" & 1)
=regex("Peter Gordon: some@email.com, 47", "^(.+): (.+), (\d+)$", "$" & 2)

Rezultat je: Peter Gordon some@email.com ...

Kako uporabljati

Če želite uporabiti ta UDF, naredite naslednje (približno na podlagi te Microsoftove strani. Na njej je nekaj dobrih dodatnih informacij!):

1. V Excelu v datoteki z omogočenimi makri ('.xlsm') pritisnite ALT+F11, da odprete Microsoft Visual Basic for Applications Editor.
2. Dodajte referenco VBA na knjižnico Regular Expressions (brez sramu prekopirano iz Portland Runners++ odgovor):
   1. Kliknite na Orodja -> Odnosi (oprostite nemški posnetek zaslona).
   2. Na seznamu poiščite Microsoft VBScript Regular Expressions 5.5 in označite potrditveno polje poleg njega.
   3. Kliknite OK.
3. Kliknite Insert Module. Če boste modulu dali drugačno ime, poskrbite, da modul ne bo imel istega imena kot spodnji UDF (npr. poimenovanje modula Regex in funkcije regex povzroči #NAME!* napake).

4. V veliko okno z besedilom na sredini vstavite naslednje:

    Funkcija 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+)"
        End With
        Z outReplaceRegexObj
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
        End With

        Set inputMatches = inputRegexObj.Execute(strInput)
        If inputMatches.Count = 0 Then
            regex = False
        V nasprotnem primeru
            Set replaceMatches = outputRegexObj.Execute(outputPattern)
            For Each replaceMatch In replaceMatches
                replaceNumber = replaceMatch.SubMatches(0)
                outReplaceRegexObj.Pattern = "\$" & replaceNumber

                If replaceNumber = 0 Then
                    outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).Value)
                V nasprotnem primeru
                    If replaceNumber > inputMatches(0).SubMatches.Count Then
                        'regex = "Najdena je bila oznaka od A do visoke $. Največja dovoljena je $" & inputMatches(0).SubMatches.Count & "."
                        regex = CVErr(xlErrVrednost)
                        Izhodna funkcija
                    Drugače
                        outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1))
                    End If
                End If
            Naslednji
            regex = outputPattern
        End If
    Konec funkcije

5. Shranite in zaprite okno Microsoft Visual Basic for Applications Editor.

Tukaj je moj poskus:

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