Kā izmantot regulārās izteiksmes (Regex) programmā Microsoft Excel gan šūnās, gan cilpās

Regulārās izteiksmes tiek izmantotas rakstu saskaņošanai.

Lai izmantotu programmā Excel, izpildiet šādas darbības :

1. solis: Pievienojiet VBA atsauci uz "Microsoft VBScript Regular Expressions 5.5"

• Izvēlieties "Izstrādātājs" cilni (Man nav šīs cilnes, ko man darīt?).
• Izvēlēties "Visual Basic" ikonu no 'Code' lentes sadaļas
• "Microsoft Visual Basic for Applications" logā izvēlieties "Tools" no augšējās izvēlnes.
• Izvēlieties "Atsauces"
• Atzīmējiet izvēles rūtiņu pie "Microsoft VBScript Regular Expressions 5.5", lai iekļautu savā darbgrāmatā.
• Noklikšķiniet uz "OK";

2. solis: Definējiet savu modeli

Bāzes definīcijas:

- Diapazons.

• Piemēram, a-z atbilst mazajiem burtiem no a līdz z.
• piem., 0-5 atbilst jebkuram skaitlim no 0 līdz 5

[] Atbilst tieši vienam no šajos iekavās esošajiem objektiem.

• Piemēram, [a] atbilst burtam a
• piem., [abc] atbilst vienam burtam, kas var būt a, b vai c.
• piem., [a-z] atbilst jebkuram mazajam alfabēta burtam.

() Atgriešanas nolūkos grupē dažādas sakritības. Sk. turpmāk sniegtos piemērus.

{} Korekcijas reizinātājs pirms tā definētā parauga atkārtotām kopijām.

• Piemēram, [a]{2} atbilst diviem secīgiem mazajiem burtiem a: aa
• Piem., [a]{1,3}`` atbilst vismaz vienam un ne vairāk kā trim mazajiem burtiema,aa,aaa`

+ Atbilst vismaz vienam vai vairākiem pirms tā definētajiem paraugiem.

• Piemēram, a+ atbilst secīgiem a's a, aa, aaa un tā tālāk.

? Atbilst nullei vai vienam no pirms tā definētajiem paraugiem.

• Piem., Pattern var būt vai nebūt, bet to var saskaņot tikai vienu reizi.
• Piemēram, [a-z]? atbilst tukšai virknei vai jebkuram mazajam burtam.

* Atbilst nullei vai vairākiem pirms tā definētajiem paraugiem.

• Piem., aizstājējzīmīte, kas var būt vai var nebūt šablonā.
• Piemēram, [a-z]* atbilst tukšai virknei vai mazo burtu virknei.

. Atbilst jebkurai rakstzīmei, izņemot jauno rindu \n.

• Piem., a. atbilst divu rakstzīmju virknei, kas sākas ar a un beidzas ar jebko, izņemot \n.

| OR operators

• Piemēram, a|b nozīmē, ka var salīdzināt vai nu a, vai b.
• Piemēram, sarkana|balta|oranža atbilst tieši vienai no krāsām.

^ NOT operators

• Piem., [^0-9] rakstzīme nevar saturēt skaitli.
• Piem., [^aA] rakstzīme nevar būt mazā a vai lielā A.

``` Izvairās no sekojošās īpašās rakstzīmes (aizstāj iepriekš minēto)

• Piemēram, \., \\, \(, \?, \$, \^

Anchoring Patterns:

^ Saskaņošanai jānotiek virknes sākumā

• Piemēram, ^a Pirmajam rakstzīmim jābūt mazajam burtam a.
• Piemēram, ^[0-9] Pirmajam rakstzīmim jābūt ciparam.

$ Saskaņošanai jānotiek virknes beigās

• piem., a$`` Pēdējam rakstzīmei jābūt mazajam burtama``.

Priekšības tabula:

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

Paredzētie rakstzīmju saīsinājumi:

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

1. piemērs: Palaist kā makro

Šajā makro piemērā tiek apskatīta vērtība šūnā A1, lai noskaidrotu, vai pirmās 1 vai 2 zīmes ir cipari. Ja tas tā ir, tās tiek izņemtas un tiek parādīta pārējā virkne. Ja nē, tad tiek parādīts lodziņš, kurā tiek paziņots, ka nav atrasta atbilstība. Ailes A1 vērtība 12abc atgriezīs abc, vērtība 1abc atgriezīs abc, vērtība abc123 atgriezīs "Neatbilda", jo cipari nebija virknes sākumā.

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

2. piemērs: Palaist kā šūnu funkciju

Šis piemērs ir tāds pats kā 1. piemērs, bet ir iestatīts tā, lai darbotos kā šūnā esoša funkcija. Lai to izmantotu, mainiet kodu šādi:

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

Ievietojiet savu virkni ("12abc") šūnā A1. Ievadiet šo formulu =simpleCellRegex(A1) šūnā B1, un rezultāts būs "abc".

3. piemērs: Loop Through Range

Šis piemērs ir tāds pats kā 1. piemērs, bet tas veido cilpu caur šūnu diapazonu.

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

4. piemērs: Dažādu rakstu sadalīšana

Šajā piemērā tiek veikta cilpa caur diapazonu (A1, A2 & amp; A3) un meklē virkni, kas sākas ar trim cipariem, kam seko viens burtu simbols un pēc tam 4 ciparu zīmes. Izvadā parauga sakritības tiek sadalītas blakus esošajās šūnās, izmantojot (). $1 ir pirmais raksts, kas atbilst pirmajam () kopumam.

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

Rezultāti:

Papildu rakstu piemēri

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

Lai izmantotu regulārās izteiksmes tieši Excel formulās, var noderēt šāda UDF (lietotāja definēta funkcija). Tā vairāk vai mazāk tieši atklāj regulārās izteiksmes funkcionalitāti kā Excel funkciju.

Kā tā darbojas

Tā pieņem 2-3 parametrus.

1. Teksts, kuram jāizmanto regulārā izteiksme.
2. Regulārā izteiksme.
3. Formāta virkne, kas norāda, kā rezultātam jāizskatās. Tajā var būt $0, $1, $2 utt. $0 ir visa sakritība, $1 un turpmāk atbilst attiecīgajām sakritības grupām regulārajā izteiksmē. Standarta noklusējuma vērtība ir $0.

Daži piemēri

E-pasta adreses iegūšana:

=regex("Peter Gordon: [email protected], 47", "\w+@\w+\.\w+")
=regex("Peter Gordon: [email protected], 47", "\w+@\w+\.\w+", "$0")

Rezultāti: [email protected]

Vairāku apakšvirsrakstu iegūšana:

=regex("Peter Gordon: som[email protected], 47", "^(.+): (.+), (\d+)$", "E-Mail: $2, Name: $1")

Rezultāts: E-Mail: [email protected], Nosaukums: Pīters Gordons:: Peter Gordon

Kombinētas virknes sadalīšana vienā šūnā tās sastāvdaļās vairākās šūnās:

=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "$" & 1)
=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "$" & 2)

Rezultāts: Peter Gordon [email protected] ...

Kā lietot

Lai izmantotu šo UDF, rīkojieties šādi (aptuveni balstoties uz šo Microsoft lapu. Tur ir laba papildu informācija!):

1. Programmā Excel failā, kurā ir iespējota makro programma ('.xlsm'), nospiediet ALT+F11, lai atvērtu Microsoft Visual Basic for Applications redaktoru.
2. Pievienojiet VBA atsauci uz regulāro izteicienu bibliotēku (bezkaunīgi nokopēts no [Portland Runners++ atbilde][2]):
   1. Noklikšķiniet uz Tools -> References (lūdzu, atvainojiet par vācu ekrānšāviņu). .
   2. Sarakstā atrodiet Microsoft VBScript Regular Expressions 5.5 un atzīmējiet blakus esošo izvēles rūtiņu.
   3. Noklikšķiniet uz OK.
3. Noklikšķiniet uz Insert Module. Ja piešķirat modulim citu nosaukumu, pārliecinieties, ka modulim nav tāds pats nosaukums kā zemāk esošajai UDF (piemēram, moduļa nosaukuma Regex un funkcijas regex nosaukuma piešķiršana izraisa #NAME! kļūdas).

4. Lielajā teksta logā vidū ievietojiet šādu tekstu:

    . 4. 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
        Ar outReplaceRegexObj
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
        End With

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

                Ja replaceNumber = 0 Tad
                    outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).Value)
                Pretējā gadījumā
                    Ja replaceNumber > inputMatches(0).SubMatches.Count Tad
                        'regex = "Atrasts no A līdz augstam $ tagam. Lielākā atļautā ir $" & inputMatches(0).SubMatches.Count & "."
                        regex = CVErr(xlErrValue)
                        Iziet no funkcijas
                    Citādi
                        outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1))
                    End If
                End If
            Next
            regex = outputPattern
        End If
    Funkcijas beigas

5. Saglabājiet un aizveriet Microsoft Visual Basic for Applications redaktora logu.

[2]: .: /questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops#answer-22542835

Šeit ir mans mēģinājums:

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