Hur man använder reguljära uttryck (Regex) i Microsoft Excel både i celler och slingor

[Reguljära uttryck] (http://en.wikipedia.org/wiki/Regular_expressions) används för mönstermatchning.

För att använda dem i Excel följer du dessa steg :

Steg 1: Lägg till VBA-referens till "Microsoft VBScript Regular Expressions 5.5"

• Välj fliken "Developer" ([Jag har inte den här fliken, vad gör jag?] (http://msdn.microsoft.com/en-us/library/bb608625.aspx))
• Välj ikonen "Visual Basic" från avsnittet 'Code' i bandet.
• I fönstret "Microsoft Visual Basic for Applications" välj "Tools" från den övre menyn.
• Välj "Referenser"
• Markera rutan bredvid "Microsoft VBScript Regular Expressions 5.5" för att inkludera i arbetsboken.
• Klicka på "OK"

Steg 2: Definiera ditt mönster

Grundläggande definitioner:

- Range.

• T.ex. a-z matchar små bokstäver från a till z.
• Exempel: 0-5 matchar alla siffror från 0 till 5.

[] Matchar exakt ett av objekten inom dessa parenteser.

• T.ex. [a] matchar bokstaven a.
• T.ex. [abc] matchar en enda bokstav som kan vara a, b eller c.
• T.ex. [a-z] matchar varje enskild liten bokstav i alfabetet.

() Grupperar olika träffar för returändamål. Se exempel nedan.

{} Multiplikator för upprepade kopior av det tidigare definierade mönstret.

• T.ex. [a]{2} matchar två på varandra följande små bokstäver a: aa.
• T.ex. [a]{1,3} matchar minst en och upp till tre små bokstäver a, aa, aaa.

+ Matchar minst en eller flera av de mönster som definierats före det.

• T.ex. a+ matchar på varandra följande a's a, aa, aaa, och så vidare.

? Matchar noll eller ett av de mönster som definierats före det.

• Exempel: Mönstret kan förekomma eller inte, men kan bara matchas en gång.
• Exempel: [a-z]? matchar en tom sträng eller en enda liten bokstav.

* Matchar noll eller flera av de mönster som definierats före det.

• T.ex. jokertecken för ett mönster som kan eller inte kan finnas.
• T.ex. [a-z]* matchar en tom sträng eller en sträng av små bokstäver.

. Matchar alla tecken utom en ny rad \n.

• T.ex. a. Matchar en sträng av två tecken som börjar med a och slutar med något annat än \n.

| OR-operatör

• T.ex. a|b betyder att antingen a eller b kan matchas.
• T.ex. red|white|orange matchar exakt en av färgerna.

^ NOT-operatorn

• T.ex. kan tecknet [^0-9] inte innehålla ett nummer.
• T.ex. kan tecknet [^aA] inte vara ett litet a eller ett stort A.

\ undviker specialtecken som följer (åsidosätter ovanstående beteende)

• T.ex. \., \\\, \(, \?, \$, \^

Förankringsmönster:

^ Matchning måste ske i början av strängen

• T.ex. ^a Första tecknet måste vara en liten bokstav a.
• T.ex. ^[0-9] Första tecknet måste vara ett nummer.

$ Matchningen måste ske i slutet av strängen.

• T.ex. a$ Sista tecknet måste vara en liten bokstav a.

Precedenstabell:

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

Predefinierade teckenförkortningar:

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

Exempel 1: Kör som makro

Följande exempelmakro tittar på värdet i cell A1 för att se om de första 1 eller 2 tecknen är siffror. Om så är fallet tas de bort och resten av strängen visas. Om inte visas en ruta som talar om att ingen matchning har hittats. Cell A1 värden på 12abc kommer att ge abc, värde på 1abc kommer att ge abc, värde på abc123 kommer att ge "Not Matched" eftersom siffrorna inte fanns i början av strängen.

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

Exempel 2: Körs som en funktion i en cell

Det här exemplet är samma som exempel 1 men är inställt för att köras som en funktion i en cell. För att använda den ändrar du koden till följande:

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

Placera dina strängar ("12abc") i cell A1. Skriv in formeln =simpleCellRegex(A1) i cell B1 och resultatet blir "abc".

Exempel 3: Loop Through Range

Det här exemplet är detsamma som exempel 1, men loopar genom ett intervall av 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

Exempel 4: Dela upp olika mönster

Det här exemplet loopar genom ett intervall (A1, A2 & A3) och letar efter en sträng som börjar med tre siffror följt av ett enda alfatecken och sedan fyra siffror. I resultatet delas mönstermatcherna upp i intilliggande celler med hjälp av (). $1 representerar det första mönstret som matchas i den första uppsättningen av ().

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

Resultat:

Exempel på ytterligare mönster

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 du vill använda reguljära uttryck direkt i Excel-formler kan följande UDF (user defined function) vara till hjälp. Den ger mer eller mindre direkt tillgång till reguljära uttrycksfunktioner som en Excel-funktion.

Hur den fungerar

Den tar 2-3 parametrar.

1. En text som det reguljära uttrycket ska användas på.
2. Ett reguljärt uttryck.
3. En formatsträng som anger hur resultatet ska se ut. Den kan innehålla $0, $1, $2 och så vidare. $0 är hela matchningen, $1 och uppåt motsvarar respektive matchningsgrupper i det reguljära uttrycket. Standardvärdet är $0.

Några exempel

Extraherar en e-postadress:

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

Resultat: [email protected]

Extraherar flera delsträngar:

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

Resultat: E-Mail: [email protected], Name: Peter Gordon

För att dela upp en kombinerad sträng i en enda cell i dess komponenter i flera celler:

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

Ger: Peter Gordon [email protected] ...

Hur man använder

För att använda den här UDF:n gör du följande (ungefärligt baserat på denna Microsoft-sida). De har en del bra tilläggsinformation där!):

1. I Excel i en fil med makronegenskaper ('.xlsm') tryck ALT+F11 för att öppna Microsoft Visual Basic for Applications Editor.
2. Lägg till en VBA-referens till biblioteket Regular Expressions (skamlöst kopierat från Portland Runners++ answer):
   1. Klicka på Tools -> References (ursäkta den tyska skärmdumpen).
   2. Hitta Microsoft VBScript Regular Expressions 5.5 i listan och markera kryssrutan bredvid.
   3. Klicka på OK.
3. Klicka på Insert Module. Om du ger modulen ett annat namn måste du se till att modulen inte har samma namn som UDF:n nedan (om du t.ex. namnger modulen Regex och funktionen regex orsakar det #NAME! fel).

4. I det stora textfönstret i mitten skriver du in följande:

    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

        Med inputRegexObj
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = matchPattern
        Slutar med
        Med outputRegexObj
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = "\$(\d+)"
        Slutar med
        Med outReplaceRegexObj
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
        Slut med

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

                Om replaceNumber = 0 Då
                    outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).Value)
                Annars
                    Om replaceNumber > inputMatches(0).SubMatches.Count Då
                        'regex = "En för hög $-tagg hittas. Största tillåtna är $" & inputMatches(0).SubMatches.Count & "."
                        regex = CVErr(xlErrValue)
                        Avsluta funktionen
                    Else
                        outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1))
                    Slut om
                Slutar om
            Nästa
            regex = outputPattern
        Slutar om
    Slut på funktionen

5. Spara och stäng fönstret Microsoft Visual Basic for Applications Editor.

Här är mitt försö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