Jak używać wyrażeń regularnych (Regex) w programie Microsoft Excel zarówno wewnątrz komórki, jak i pętli

Aby używać wyrażeń regularnych bezpośrednio w formułach programu Excel, pomocna może być następująca funkcja UDF (user defined function). Mniej lub bardziej bezpośrednio eksponuje funkcje wyrażeń regularnych jako funkcję Excela.

Jak to działa

Zajmuje 2-3 parametry.

1. tekst do użycia wyrażenia regularnego.
2. Wyrażenie regularne.
3. łańcuch formatu określający, jak powinien wyglądać wynik. Może zawierać $0, $1, $2, i tak dalej. $0 jest Cały Mecz, $1 i do góry odpowiadają odpowiednim grupom meczów w wyrażeniu regularnym. Domyślnie $0.

Niektóre przykłady

Wyodrębnianie adresu e-mail:

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

Wyniki w: [email protected]

Wydobywanie kilku podciągów:

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

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

Aby rozdzielić połączony ciąg w pojedynczej komórce na jego składniki w wielu komórkach:

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

Wyniki w: Peter Gordon [email protected] ...

Jak użycie

Aby użyć tego UDF, wykonaj następujące czynności (w przybliżeniu na podstawie tej strony Microsoftu . Mają tam dobre dodatkowe informacje!):

1. w Excelu w pliku z włączonym makrem (".xlsm') naciśnij ALT+F11, aby otworzyć Edytor Microsoft Visual Basic for Applications.
2. dodanie referencji VBA do biblioteki wyrażeń regularnych (bezwstydnie skopiowanych z Portland Runners++ answer ):
   1. Kliknij na narzędzia -> Referencje (proszę przepraszam za Niemiecki zrzut ekranu)
   2. Znajdź wyrażenia regularne Microsoft VBScript 5.5 na liście i zaznacz pole wyboru obok.
   3. kliknij OK .

3. Kliknij na Wstaw moduł. Jeśli nadasz modułowi inną nazwę, upewnij się, że moduł nie ma taką samą nazwę jak poniższy UDF (np. nazwanie modułu Regex i funkcja regex powoduje #NAME! błędy).

   

4. W dużym oknie tekstowym na środku wpisz:

   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+)"
       End With
       With 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 Each replaceMatch In replaceMatches
               replaceNumber = replaceMatch.SubMatches(0)
               outReplaceRegexObj.Pattern = "\$" & replaceNumber
   
               If replaceNumber = 0 Then
                   outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).Value)
               Else
                   If replaceNumber > inputMatches(0).SubMatches.Count Then
                       'regex = "A to high $ tag found. Largest allowed is $" & inputMatches(0).SubMatches.Count & "."
                       regex = CVErr(xlErrValue)
                       Exit Function
                   Else
                       outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1))
                   End If
               End If
           Next
           regex = outputPattern
       End If
   End Function
   

5. Zapisz i zamknij okno edytora Microsoft Visual Basic for Applications .

Rozszerzenie na patszim 'S odpowiedź dla tych, którzy się spieszą.

1. otwórz skoroszyt programu Excel.
2. Alt+F11 aby otworzyć okno VBA / makra.
3. Dodaj odwołanie do wyrażenia regularnego w sekcji narzędzia wtedy linki
   
4. i wybranie Microsoft VBScript Wyrażenie regularne 5.5
   
5. wstaw nowy moduł (kod musi znajdować się w module w przeciwnym razie nie działa).
   
6. w nowo wstawionym module,
   

7. Dodać następujący kod:

   Function RegxFunc(strInput As String, regexPattern As String) As String
       Dim regEx As New RegExp
       With regEx
           .Global = True
           .MultiLine = True
           .IgnoreCase = False
           .pattern = regexPattern
       End With
   
       If regEx.Test(strInput) Then
           Set matches = regEx.Execute(strInput)
           RegxFunc = matches(0).Value
       Else
           RegxFunc = "not matched"
       End If
   End Function
   

8. Wzór regex jest umieszczony w jednej z komórek i absolute reference jest na nim używany. Funkcja zostanie powiązana ze skoroszytem, w którym została utworzona.
   Jeśli istnieje potrzeba, aby była używana w różnych skoroszytach, Zapisz tę funkcję w Personal.XLSB

Oto moja próba:

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

To nie jest bezpośrednia odpowiedź, ale może stanowić bardziej skuteczną alternatywę dla twojej uwagi. Co oznacza, że Arkusze Google mają kilka wbudowanych funkcji Regex , które mogą być bardzo wygodne i pomagają obejść niektóre procedury techniczne w programie Excel. Oczywiście istnieją pewne zalety korzystania z programu Excel na komputerze, ale dla znacznej większości użytkowników Arkusze Google oferują identyczne wrażenia i mogą oferować pewne korzyści w zakresie przenośności i udostępniania dokumentów.

Oferują

REGEXEXTRACT: wyciąga pasujące podłańcuchy zgodnie z wyrażeniem regularnym.

REGEXREPLACE: zastępuje część ciągu tekstowego innym ciągiem tekstowym za pomocą wyrażeń regularnych.

SUBSTITUTE: zastępuje istniejący tekst nowym tekstem w ciągu znaków.

REPLACE: zastępuje część ciągu tekstowego innym ciągiem tekstowym.

Możesz wpisać je bezpośrednio do komórki jak tak i będzie produkować co chcesz

=REGEXMATCH(A2, "[0-9]+")

Oni działa również całkiem dobrze w kombinacjach z innymi funkcjami, takimi jak IF , Jak Tak:

=IF(REGEXMATCH(E8,"MiB"),REGEXEXTRACT(E8,"\d*\.\d*|\d*")/1000,IF(REGEXMATCH(E8,"GiB"),REGEXEXTRACT(E8,"\d*\.\d*|\d*"),"")

Miejmy nadzieję, że zapewnia to proste obejście dla użytkowników, którzy czują się wyśmiewani przez komponent VBS Excela.

Musiałem użyć tego jako funkcji komórki (jak SUM lub VLOOKUP) i stwierdziłem, że łatwo jest:

1. upewnij się, że jesteś w pliku Excel z włączonym makrem (Zapisz jako xlsm).
2. Otwórz Narzędzia programistyczne Alt + F11
3. Dodaj wyrażenia regularne Microsoft VBScript 5.5 jak w innych odpowiedziach

4. Utwórz następującą funkcję w skoroszycie lub we własnym module:

   Function REGPLACE(myRange As Range, matchPattern As String, outputPattern As String) As Variant
       Dim regex As New VBScript_RegExp_55.RegExp
       Dim strInput As String
   
       strInput = myRange.Value
   
       With regex
           .Global = True
           .MultiLine = True
           .IgnoreCase = False
           .Pattern = matchPattern
       End With
   
       REGPLACE = regex.Replace(strInput, outputPattern)
   
   End Function
   

5. Następnie możesz użyć w komórce z =REGPLACE(B1, "(\w) (\d+)", "$1$2") (np: "A 243 "do " A243")

Oto regex_subst() funkcja. Przykłady:

=regex_subst("watermellon", "[aeiou]", "")
---> wtrmlln
=regex_subst("watermellon", "[^aeiou]", "")
---> aeeo

Oto kod uproszczony(dla mnie prostszy). Nie mogłem dowiedzieć się, jak zbudować odpowiedni wzór wyjściowy, używając powyższego, aby działał jak moje przykłady:

Function regex_subst( _
     strInput As String _
   , matchPattern As String _
   , Optional ByVal replacePattern As String = "" _
) As Variant
    Dim inputRegexObj As New VBScript_RegExp_55.RegExp

    With inputRegexObj
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = matchPattern
    End With

    regex_subst = inputRegexObj.Replace(strInput, replacePattern)
End Function

Aby dodać do wartościowej treści, Chciałbym stworzyć przypomnienie, dlaczego czasami RegEx w VBA nie jest idealny. Nie wszystkie wyrażenia są obsługiwane, ale zamiast tego może rzucić Error 5017 i może pozostawić autora zgadywania(co jestem ofiarą siebie).

Chociaż możemy znaleźć źródła na temat tego, co jest wspierane, pomocne byłoby wiedzieć, które metacharaktery itp. są Nie obsługiwane. Bardziej szczegółowe wyjaśnienie można znaleźć tutaj . Wymienione w tym źródle:

"chociaż" Wyrażenie regularne VBScript ... Wersja 5.5 implementuje sporo podstawowych funkcji regex, których brakowało w poprzednich wersjach VBScript. ... JavaScript i VBScript implementują wyrażenia regularne W Stylu Perla. Brakuje im jednak sporej liczby zaawansowanych funkcji dostępnych w Perlu i innych nowoczesnych smakach wyrażeń regularnych:"

Więc Nie obsługiwane są:

• początek Łańcuch ancor \A, alternatywnie użyj karetki ^, aby dopasować pozycję przed pierwszym znakiem w łańcuchu
• koniec łańcucha ancor \Z, alternatywnie użyj znaku dolara $, aby dopasować pozycję po ostatnim znaku w łańcuchu
W przypadku, gdy nie jest to możliwe, nie jest to możliwe.]} Nie jest to jednak możliwe w przypadku, gdy nie jest to możliwe.]}
• Grupowanie Atomowe
• zaborczy Kwantyfikatory
• Unicode np.: \{uFFFF}
• Nazwane Grupy Przechwytywania . Alternatywnie użyj numerowanych grup przechwytywania
• modyfikatory liniowe, np.: /i (wielkość liter) lub /g (globalny) itp. Ustaw je za pomocą RegExp Właściwości obiektu > RegExp.Global = True i RegExp.IgnoreCase = True, jeśli są dostępne.
• Conditionals
• Komentarze Wyrażeń Regularnych . Dodaj je za pomocą zwykłych komentarzy ' w skrypcie

Już hit ściany więcej niż raz za pomocą wyrażeń regularnych w VBA. Zazwyczaj z LookBehind, ale czasami nawet zapominam o modyfikatorach. Sam nie doświadczyłem tych wszystkich wyżej wymienionych tła, ale pomyślałem, że postaram się być obszerny, odnosząc się do bardziej dogłębnych informacji. Zapraszam do komentowania / poprawiania / dodawania. Big shout out to regular-expressions.info dla bogactwa informacji.

P. S. wspomniałeś o zwykłych metodach i funkcjach VBA i mogę potwierdzić one (przynajmniej dla mnie) były pomocne na swój własny sposób, gdzie RegEx zawiedzie.

nie chcę włączać biblioteki referencyjnej, ponieważ moje Skrypty muszą być przenośne. Linia Dim foo As New VBScript_RegExp_55.RegExp spowodowała User Defined Type Not Defined błędy, ale znalazłem rozwiązanie, które zadziałało dla mnie.

Update re comments w / @ chrisneilsen:

Miałem wrażenie, że włączenie biblioteki referencyjnej jest związane z ustawieniami lokalnych komputerów, ale w rzeczywistości jest bezpośrednio związane ze skoroszytem. możesz więc włączyć bibliotekę referencyjną, udostępnić skoroszyt z makrami i użytkownik końcowy nie musiałby również włączać biblioteki. Zastrzeżenie: zaletą późnego wiązania jest to, że programista nie musi się martwić o niewłaściwą wersję biblioteki obiektowej zainstalowanej na komputerze użytkownika. prawdopodobnie nie byłoby to problemem w/ VBScript_RegExp_55.RegExp bibliotece, ale nie jestem sprzedawany, że" wydajność " benifit jest tego warta dla mnie w tym momencie, ponieważ mówimy o niezauważalnych milisekundach w moim kodzie. Uznałem, że zasługuje to na aktualizację, aby pomóc innym zrozumieć. Jeśli włącz bibliotekę referencyjną, możesz użyć "early bind", ale jeśli tego nie zrobisz, o ile mogę powiedzieć, kod będzie działał dobrze, ale musisz "late bind"{[13] } i stracić na niektórych funkcjach wydajności / debugowania.

Źródło: https://peltiertech.com/Excel/EarlyLateBinding.html

To, co chcesz zrobić, to umieścić przykładowy łańcuch w komórce A1, a następnie przetestować swój strPattern. Gdy to działa, dostosuj rng zgodnie z życzeniem.

Public Sub RegExSearch()
'https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops
'https://wellsr.com/vba/2018/excel/vba-regex-regular-expressions-guide/
'https://www.vitoshacademy.com/vba-regex-in-excel/
    Dim regexp As Object
    'Dim regex As New VBScript_RegExp_55.regexp 'Caused "User Defined Type Not Defined" Error
    Dim rng As Range, rcell As Range
    Dim strInput As String, strPattern As String
    
    Set regexp = CreateObject("vbscript.regexp")
    Set rng = ActiveSheet.Range("A1:A1")
        
    strPattern = "([a-z]{2})([0-9]{8})"
    'Search for 2 Letters then 8 Digits Eg: XY12345678 = Matched

    With regexp
        .Global = False
        .MultiLine = False
        .ignoreCase = True
        .Pattern = strPattern
    End With

    For Each rcell In rng.Cells

        If strPattern <> "" Then
            strInput = rcell.Value

            If regexp.test(strInput) Then
                MsgBox rcell & " Matched in Cell " & rcell.Address
            Else
                MsgBox "No Matches!"
            End If
        End If
    Next
End Sub