Jak używać wyrażeń regularnych (Regex) w programie Microsoft Excel zarówno w komórkach, jak i w pętlach


592

Jak korzystać z wyrażeń regularnych w programie Excel i korzystać z rozbudowanej konfiguracji Excela do manipulacji danymi?

  • Funkcja w komórce, aby zwrócić dopasowany wzór lub zastąpioną wartość w ciągu.
  • Sub, aby przejść przez kolumnę danych i wyodrębnić dopasowania do sąsiednich komórek.
  • Jaka konfiguracja jest konieczna?
  • Jakie są znaki specjalne Excela dla wyrażeń regularnych?

Rozumiem Regex nie jest idealnym rozwiązaniem dla wielu sytuacjach ( do wykorzystania lub nie używać wyrażeń regularnych? ), Ponieważ Excel można użyć Left, Mid, Right, Instrpolecenia typu podobnych manipulacji.


11
Gorąco polecam ten artykuł VB / VBA Regexp autorstwa Patrick Matthews
brettdj

1
Wypróbuj bezpłatny dodatek: seotoolsforexcel.com/regexpfind
Niels Bosma

1
Nie zapominajmy o Like operatorze , który zapewnia rodzaj lekkiej wersji funkcjonalności w stylu wyrażenia regularnego. Zazwyczaj jest znacznie szybszy niż wyrażenie regularne, nawet jeśli jest zawarty w procedurze podrzędnej lub funkcji.
Egalth

Odpowiedzi:


955

Wyrażenia regularne są używane do dopasowania wzorca.

Aby użyć w programie Excel, wykonaj następujące kroki:

Krok 1 : Dodaj odniesienie VBA do „Wyrażeń regularnych Microsoft VBScript 5.5”

  • Wybierz kartę „Deweloper” ( nie mam tej karty, co mam zrobić? )
  • Wybierz ikonę „Visual Basic” z sekcji wstążki „Kod”
  • W oknie „Microsoft Visual Basic for Applications” wybierz „Narzędzia” z górnego menu.
  • Wybierz „Referencje”
  • Zaznacz pole obok „Microsoft VBScript Regular Expressions 5.5”, aby dołączyć do skoroszytu.
  • Kliknij OK"

Krok 2 : Zdefiniuj swój wzór

Podstawowe definicje:

- Zasięg.

  • Np. a-zDopasowuje małe litery od a do z
  • Np. 0-5Pasuje do dowolnej liczby od 0 do 5

[] Dopasuj dokładnie jeden z obiektów w tych nawiasach.

  • Np. [a]Pasuje do litery a
  • Np. [abc]Pasuje do jednej litery, która może być a, b lub c
  • Np. [a-z]Pasuje do dowolnej małej litery alfabetu.

()Grupuje różne dopasowania do celów zwrotu. Zobacz przykłady poniżej.

{} Mnożnik dla powtarzających się kopii zdefiniowanego wcześniej wzoru.

  • Np. [a]{2}Pasuje do dwóch kolejnych małych liter a:aa
  • Np [a]{1,3}pasuje co najmniej jeden, a maksymalnie trzy niższe literę a, aa,aaa

+ Dopasuj co najmniej jeden lub więcej wcześniej zdefiniowanych wzorów.

  • Np a+dopasuje rzędu A w a, aa, aaa, i tak dalej

? Dopasuj zero lub jeden ze zdefiniowanych wcześniej wzorców.

  • Np. Wzorzec może, ale nie musi być obecny, ale można go dopasować tylko raz.
  • Np. [a-z]?Dopasowuje pusty ciąg lub dowolną małą literę.

* Dopasuj zero lub więcej wzorca zdefiniowanego przed nim. - Np. Symbol wieloznaczny dla wzoru, który może, ale nie musi być obecny. - Np. [a-z]*Dopasowuje pusty ciąg lub ciąg małych liter.

. Pasuje do dowolnego znaku z wyjątkiem znaku nowej linii \n

  • Np. a.Dopasowuje ciąg dwóch znaków rozpoczynający się od a, a kończący na czymkolwiek oprócz\n

| LUB operator

  • Np. a|bOznacza albo aalbo bmożna je dopasować.
  • Np. red|white|orangePasuje dokładnie do jednego z kolorów.

^ NIE operator

  • Np. [^0-9]Znak nie może zawierać liczby
  • Np. [^aA]Znak nie może być małymi alub dużymi literamiA

\ Ucieka specjalnemu znakowi, który następuje (przesłania powyższe zachowanie)

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

Wzory kotwiczenia:

^ Dopasowanie musi nastąpić na początku łańcucha

  • Np. ^aPierwszy znak musi być małą literąa
  • Np. ^[0-9]Pierwszy znak musi być liczbą.

$ Dopasowanie musi nastąpić na końcu ciągu

  • Np. a$Ostatni znak musi być małą literąa

Tabela pierwszeństwa:

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

Predefiniowane skróty postaci:

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

Przykład 1 : Uruchom jako makro

Poniższe przykładowe makro sprawdza wartość w komórce, A1aby sprawdzić, czy pierwsze 1 lub 2 znaki są cyframi. Jeśli tak, są one usuwane, a reszta łańcucha jest wyświetlana. Jeśli nie, pojawi się okno z informacją, że nie znaleziono dopasowania. A1Wartości komórek 12abczwrócą abc, wartość 1abczwrócą abc, wartość abc123zwrócą „Niepasujące”, ponieważ cyfry nie były na początku łańcucha.

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

Przykład 2 : Uruchom jako funkcję w komórce

Ten przykład jest taki sam jak w przykładzie 1, ale jest skonfigurowany do działania jako funkcja w komórce. Aby użyć, zmień kod na ten:

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

Umieść swoje ciągi („12abc”) w komórce A1. Wprowadź tę formułę =simpleCellRegex(A1)w komórce, B1a wynikiem będzie „abc”.

obraz wyników


Przykład 3 : Pętla przez zakres

Ten przykład jest taki sam jak w przykładzie 1, ale przechodzi przez szereg komórek.

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

Przykład 4 : Rozdzielanie różnych wzorów

W tym przykładzie pętla przechodzi przez zakres ( A1, A2& A3) i wyszukuje ciąg rozpoczynający się od trzech cyfr, po których następuje pojedynczy znak alfa, a następnie 4 cyfry. Dane wyjściowe dzielą dopasowania wzorca na sąsiednie komórki za pomocą (). $1reprezentuje pierwszy wzór dopasowany w pierwszym zestawie ().

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

Wyniki:

obraz wyników


Dodatkowe przykłady wzorów

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 preceding 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

22
Nie powinieneś o tym zapomnieć Set regEx = Nothing. Dostaniesz wyjątki Out Of Memory, gdy ten Sub jest wykonywany wystarczająco często.
Kiril

1
Zaadaptowałem przykład 4 z SubMatches do leczenia bardziej złożonego wyrażenia regularnego, w zasadzie nie używam replace podczas dzielenia, jeśli ktoś jest zainteresowany: stackoverflow.com/questions/30218413/...
Armfoot

11
Późna linia wiążąca:Set regEx = CreateObject("VBScript.RegExp")
ZygD,

2
Okej, jestem całkiem pewien, że to dlatego, że kod jest włączony ThisWorkbook. Spróbuj przenieść kod do osobnego Module.
Portland Runner,

3
@PortlandRunner w „eksploratorze projektów” (?) W tym pliku programu Excel brakowało podfolderu „Moduły”, chociaż inny plik go pokazywał. Kliknij plik prawym przyciskiem myszy i wybierz „wstaw moduł”, a następnie kliknij dwukrotnie „Moduł 1” i wkleisz kod. Zapisano Powrót do skoroszytu i ponowne wprowadzenie funkcji - zadziałało. Czy odpowiedź może być godna uwagi ze względu na niedoświadczonych, takich jak ja? Dzięki za pomoc.
youcantryreachingme

205

Aby skorzystać z wyrażeń regularnych bezpośrednio w formułach programu Excel, pomocne może być następujące UDF (funkcja zdefiniowana przez użytkownika). Mniej więcej bezpośrednio eksponuje funkcjonalność wyrażeń regularnych jako funkcję programu Excel.

Jak to działa

Zajmuje 2-3 parametry.

  1. Tekst do użycia wyrażenia regularnego.
  2. Wyrażenie regularne.
  3. Ciąg formatu określający, jak powinien wyglądać wynik. Może ona zawierać $0, $1, $2, i tak dalej. $0jest całym dopasowaniem, $1a wartość w górę odpowiada odpowiednim grupom dopasowania w wyrażeniu regularnym. Domyślnie to $0.

Kilka przykładów

Wyodrębnianie adresu e-mail:

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

Prowadzi do: some@email.com

Wyodrębnianie kilku podciągów:

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

Prowadzi do: E-Mail: some@email.com, Name: Peter Gordon

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

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

Wyniki w: Peter Gordon some@email.com...

Jak używać

Aby użyć tego UDF, wykonaj następujące czynności (w przybliżeniu na podstawie tej strony Microsoft . Mają tam kilka dobrych dodatkowych informacji!):

  1. W programie Excel w pliku obsługującym makro („.xlsm”) naciśnij, ALT+F11aby otworzyć edytor Microsoft Visual Basic for Applications .
  2. Dodaj odniesienie VBA do biblioteki wyrażeń regularnych (bezwstydnie skopiowane z odpowiedzi Portland Runners ++ ):
    1. Kliknij Narzędzia -> Referencje (przepraszam za niemiecki zrzut ekranu) Narzędzia -> Referencje
    2. Znajdź na liście Wyrażenia regularne Microsoft VBScript 5.5 i zaznacz pole wyboru obok niej.
    3. Kliknij OK .
  3. Kliknij Wstaw moduł . Jeśli nadasz swojemu modułowi inną nazwę, upewnij się, że moduł nie ma takiej samej nazwy jak UDF poniżej (np. Nazwij moduł, Regexa funkcja regexspowoduje błędy # NAZWA! ).

    Druga ikona w rzędzie ikon -> Moduł

  4. W dużym oknie tekstowym na środku wstaw następujące informacje:

    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 .


6
Ta odpowiedź w połączeniu z opisanymi tutaj etapami tworzenia dodatku była bardzo pomocna. Dziękuję Ci. Upewnij się, że nie podajesz modułu i funkcji o tej samej nazwie!
Chris Hunt

2
Powtarzam tylko powyższy komentarz Chrisa Hunta. Nie nazywaj również swojego modułu „Regex”. Myślałem, że zwariowałem przez chwilę, ponieważ funkcja nie działała z powodu błędu # NAZWA
Chris


@Enissay: Spróbuj utworzyć minimalny Function foo() As Variant \n foo="Hello World" \n End FunctionUDF, aby zobaczyć, czy to działa. Jeśli tak, przejdź do pełnej powyższej kwestii, jeśli nic nie jest zepsute (makra wyłączone?).
Patrick Böker,


64

Rozszerzając patszim jest odpowiedzią dla tych w pośpiechu.

  1. Otwórz skoroszyt programu Excel.
  2. Alt+, F11aby otworzyć okno VBA / Macros.
  3. Dodaj odwołanie do wyrażenia regularnego w obszarze Narzędzia, a następnie Odwołania
    ! [Formularz Excel VBA dodaj odniesienia
  4. i wybierając Wyrażenie regularne Microsoft VBScript 5.5
    ! [Excel VBA dodaj odwołanie do wyrażenia regularnego
  5. Wstaw nowy moduł (kod musi znajdować się w module, w przeciwnym razie nie będzie działał).
    ! [Excel VBA moduł wstawiania kodu
  6. W nowo wstawionym module
    ! [Excel VBA wstaw kod do modułu
  7. dodaj 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. Wzorzec wyrażenia regularnego jest umieszczany w jednej z komórek i jest na nim stosowane odwołanie bezwzględne . ! [Wykorzystanie funkcji regularnej Excel w komórkach Funkcja zostanie powiązana ze skoroszytem, ​​w którym została utworzona.
    Jeśli zachodzi potrzeba użycia jej w różnych skoroszytach, zapisz funkcję w Personal.XLSB


1
Dziękujemy za wzmiankę, że musi on znajdować się w pliku Personal.xlsb, aby był dostępny we wszystkich dokumentach programu Excel, nad którymi pracujesz. Większość (?) Innych odpowiedzi nie wyjaśnia tego. Personal.XLSB przejdzie do folderu (może być konieczne utworzenie folderu) C: \ Users \ nazwa użytkownika \ AppData \ Local \ Microsoft \ Excel \ XLStart folder
Mark Stewart

26

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

9

Musiałem użyć tego jako funkcji komórki (jak SUMlub VLOOKUP) i stwierdziłem, że łatwo było:

  1. Upewnij się, że znajdujesz się w pliku Excel z włączoną obsługą makr (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 za pomocą =REGPLACE(B1, "(\w) (\d+)", "$1$2")(np. „A 243” do „A243”)


To nazewnictwo OutputPattern mnie wyrzuciło. To wartość zastępcza.
Thor

1
Tak. Przypuszczam, że zostawiłem nazwany wzorzec, więc było jasne, że nie jest to tylko podstawianie ciągów i można użyć grup dopasowania
wyrażeń

7

To nie jest bezpośrednia odpowiedź, ale może stanowić bardziej efektywną alternatywę dla twojego rozważania. Arkusze Google mają kilka wbudowanych funkcji Regex, które mogą być bardzo wygodne i mogą pomóc ominąć niektóre procedury techniczne w Excelu. Oczywiście korzystanie z programu Excel na komputerze ma pewne zalety, ale dla znacznej większości użytkowników Arkusze Google oferują identyczne funkcje i mogą oferować pewne korzyści w zakresie przenoszenia i udostępniania dokumentów.

Oni oferują

REGEXEXTRACT: Wyodrębnia pasujące podciągi zgodnie z wyrażeniem regularnym.

REGEXREPLACE: Zamienia część ciągu tekstowego na inny ciąg tekstowy przy użyciu wyrażeń regularnych.

SUBSTITUTE: Zamienia istniejący tekst na nowy tekst w ciągu.

REPLACE: Zamienia część ciągu tekstowego na inny ciąg tekstowy.

Możesz wpisać je bezpośrednio do takiej komórki i wyprodukować, co chcesz

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

Działają również całkiem dobrze w połączeniu z innymi funkcjami, takimi jak instrukcje IF , takie jak:

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

wprowadź opis zdjęcia tutaj

Mamy nadzieję, że zapewnia to proste obejście dla użytkowników, którzy czują się wyśmiewani przez składnik VBS programu Excel.


Dzięki za udostępnienie Alexa. Jest to przydatne dla osób poszukujących wersji Google. Możesz napisać i odpowiedzieć na inne pytanie specyficzne dla Google Sheffield Regex, ponieważ ma ono własną nomenklaturę i byłoby bardzo przydatne dla innych. Niezależnie od tego, masz moje poparcie!
Portland Runner

4

Oto regex_subst()funkcja. Przykłady:

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

Oto uproszczony kod (w każdym razie prostszy dla mnie). Nie mogłem wymyślić, jak zbudować odpowiedni wzorzec 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

1

Nie chcę włączać biblioteki referencyjnej, ponieważ potrzebuję przenośnych skryptów. Dim foo As New VBScript_RegExp_55.RegExpLinia spowodowane User Defined Type Not Definedbłędy, ale znalazłem rozwiązanie, które pracowały dla mnie.

To, co chcesz zrobić, to wstawić ciąg znaków do komórki A1, a następnie przetestować strPattern. Gdy to zadziała, dostosuj odpowiednio rngdo potrzeb.

Public Sub RegExSearch()
'/programming/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

Nie chcę włączać biblioteki referencyjnej, ponieważ potrzebuję przenośnych skryptów - VBScript_RegExp_55biblioteka jest dość wszechobecna, więc niesie ze sobą bardzo małe ryzyko, że nie będzie na konkretnej maszynie docelowej. Zresztą przejście z Early Bound na Late Bound nie rozwiązuje problemu przenośności (kod nadal będzie występował błąd, tylko w czasie wykonywania, a nie w czasie kompilacji)
Chris Neilsen

1
Nie, to wcale nie jest prawda, ten skrypt może być udostępniony innemu użytkownikowi bez włączania czegokolwiek, co jest przenośne. Dzięki tho
FreeSoftwareServers

1
Jasne, ale kod RegEx nie będzie użyteczny . Jeśli rzeczy RegEx są częścią większego skoroszytu bibliotecznego, myślę, że nie
zgłasza

1

Aby dodać do cennej treści, chciałbym przypomnieć, dlaczego czasami RegEx w VBA nie jest idealny. Nie wszystkie wyrażenia są obsługiwane, ale zamiast tego mogą rzucić Error 5017i mogą pozostawić zgadywanie autora (co jest moją ofiarą).

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

„Chociaż” Wyrażenie regularne VBScript ... wersja 5.5 implementuje sporo istotnych funkcji wyrażeń regularnych, których brakowało w poprzednich wersjach VBScript. ... JavaScript i VBScript implementują wyrażenia regularne w stylu Perla. Brakuje im jednak wielu zaawansowanych funkcji dostępnych w Perlu i innych współczesnych smakach wyrażeń regularnych: „


Tak więc nie są obsługiwane:

  • Początek łańcucha Ancor \A, alternatywnie użyj ^karetki, aby dopasować pozycję przed 1. char w łańcuchu
  • Koniec łańcucha Ancor \Z, alternatywnie użyj $znaku dolara, aby dopasować pozycję po ostatnim znaku w łańcuchu
  • Positive LookBehind, np .: (?<=a)b(gdy obsługiwany jest postive LookAhead )
  • Negatywne LookBehind, np .: (?<!a)b(podczas gdy negatywne LookAhead jest obsługiwane)
  • Grupowanie atomowe
  • Kwantyfikatory dzierżawcze
  • Unicode np .: \{uFFFF}
  • Nazwane grupy przechwytywania . Alternatywnie użyj numerowanych grup przechwytywania
  • Modyfikatory wbudowane, np .: /i(rozróżnianie wielkości liter) lub /g(globalny) itp. Ustaw je za pomocą RegExpwłaściwości obiektu> RegExp.Global = Truei RegExp.IgnoreCase = Truejeśli są dostępne.
  • Warunkowe
  • Komentarze do wyrażeń regularnych . Dodaj je z regularnymi 'komentarzami w skrypcie

Już kilkakrotnie uderzyłem w ścianę, używając wyrażeń regularnych w VBA. Zwykle z, LookBehindale czasem nawet zapominam o modyfikatorach. Nie doświadczyłem tych wszystkich wyżej wymienionych środowisk, ale pomyślałem, że staram się być obszerny, odnosząc się do bardziej szczegółowych informacji. Skomentuj / popraw / dodaj. Duże brawa dla regular-expressions.info za bogactwo informacji.

PS Wspomniałeś o zwykłych metodach i funkcjach VBA, i mogę potwierdzić, że (przynajmniej dla mnie) były pomocne na własne sposoby, w których RegEx zawiódłby.

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.