Microsoft Excel'de hem hücre içi hem de döngülerde Düzenli İfadeler (Regex) nasıl kullanılır?

Düzenli ifadeler Örüntü Eşleştirme için kullanılır.

Excel'de kullanmak için aşağıdaki adımları izleyin:

Adım 1: VBA referansını "Microsoft VBScript Regular Expressions 5.5&quot'a ekleyin;

• Geliştirici" sekmesini seçin (Bu sekmeye sahip değilim ne yapmalıyım?)
• Şerit bölümündeki 'Kod'dan "Visual Basic" simgesini seçin
• Uygulamalar için Microsoft Visual Basic" penceresinde üst menüden "Araçlar" öğesini seçin.
• Referanslar"ı seçin;
• Çalışma kitabınıza dahil etmek için "Microsoft VBScript Regular Expressions 5.5" seçeneğinin yanındaki kutuyu işaretleyin.
• Tamam" seçeneğine tıklayın;

Adım 2: Deseninizi tanımlayın

Temel tanımlar:

- Aralık.

• Örneğin a-z a'dan z'ye kadar küçük harflerle eşleşir
• Örneğin 0-5 0'dan 5'e kadar herhangi bir sayıyla eşleşir

[]` Bu parantezlerin içindeki nesnelerden tam olarak birini eşleştirin.

• Örneğin [a] a harfiyle eşleşir
• Örneğin [abc] a, b veya c olabilen tek bir harfle eşleşir
• Örneğin [a-z] alfabenin herhangi bir küçük harfiyle eşleşir.

()` İade amaçları için farklı eşleşmeleri gruplar. Aşağıdaki örneklere bakınız.

{} Kendisinden önce tanımlanan desenin tekrarlanan kopyaları için çarpan.

• Örneğin, [a]{2} ardışık iki küçük harf a ile eşleşir: aa`
• Örneğin, [a]{1,3} en az bir ve en fazla üç küçük harfle eşleşir a, aa, aaa

+ Kendisinden önce tanımlanan kalıplardan en az birini veya daha fazlasını eşleştirir.

• Örneğin a+ ardışık a'larla eşleşecektir a, aa, aaa, vb.

? Kendisinden önce tanımlanan kalıplardan sıfır veya biriyle eşleşir.

• Örn. Desen mevcut olabilir veya olmayabilir ancak yalnızca bir kez eşleştirilebilir.
• Örneğin [a-z]? boş dizeyle veya tek bir küçük harfle eşleşir.

* Kendisinden önce tanımlanan kalıbın sıfır veya daha fazlasını eşleştirir.

• Örn. mevcut olabilecek veya olmayabilecek desen için joker karakter.
• Örn. [a-z]* boş dizeyle veya küçük harf dizesiyle eşleşir.

.Satırbaşı hariç herhangi bir karakterle eşleşir\n`

• Örneğin a. a ile başlayan ve \n dışında herhangi bir şeyle biten iki karakterli bir dizeyle eşleşir

| VEYA operatörü

• Örneğin a|b, a ya da b ile eşleştirilebileceği anlamına gelir.
• Örneğin red|white|orange renklerden tam olarak biriyle eşleşir.

^` DEĞİL işleci

• Örneğin [^0-9] karakteri bir sayı içeremez
• Örneğin, [^aA] karakteri küçük harf a veya büyük harf A olamaz

\ Takip eden özel karakterden kaçar (yukarıdaki davranışı geçersiz kılar)

• Örneğin \., \\, \(, \?, \$, \^

Anchoring Kalıpları:

^` Eşleşme dizenin başında gerçekleşmelidir

• Örneğin ^a İlk karakter küçük harf a olmalıdır
• Örneğin ^[0-9] İlk karakter bir sayı olmalıdır.

$` Eşleşme dizenin sonunda gerçekleşmelidir

• Örneğin a$ Son karakter küçük harf a olmalıdır

Öncelik tablosu:

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

Önceden Tanımlanmış Karakter Kısaltmaları:

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

Örnek 1: Makro olarak çalıştır

Aşağıdaki örnek makro, ilk 1 veya 2 karakterin rakam olup olmadığını görmek için A1 hücresindeki değere bakar. Eğer öyleyse, bunlar kaldırılır ve dizenin geri kalanı görüntülenir. Değilse, eşleşme bulunamadığını bildiren bir kutu görüntülenir. Hücre A1 12abc değerleri abc döndürür, 1abc değeri abc döndürür, abc123 değeri "Not Matched" döndürür çünkü rakamlar dizenin başında değildir.

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

Örnek 2: Hücre içi işlev olarak çalıştır

Bu örnek, örnek 1 ile aynıdır ancak hücre içi işlev olarak çalışacak şekilde ayarlanmıştır. Kullanmak için kodu şu şekilde değiştirin:

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

Dizelerinizi ("12abc") A1 hücresine yerleştirin. Bu formülü =simpleCellRegex(A1) B1 hücresine girin ve sonuç "abc" olacaktır.

Örnek 3: Aralık Boyunca Döngü

Bu örnek, örnek 1 ile aynıdır ancak bir hücre aralığında döngüler gerçekleştirir.

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

Örnek 4: Farklı desenleri ayırma

Bu örnek bir aralıkta (A1, A2 & A3) döngü yapar ve üç rakamla başlayan ve ardından tek bir alfa karakteri ve ardından 4 sayısal rakam içeren bir dize arar. Çıktı, () kullanarak desen eşleşmelerini bitişik hücrelere ayırır. $1, ilk()` kümesi içinde eşleşen ilk deseni temsil eder.

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

Sonuçlar:

Ek Desen Örnekleri

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

Düzenli ifadeleri doğrudan Excel formüllerinde kullanmak için aşağıdaki UDF (kullanıcı tanımlı işlev) yardımcı olabilir. Düzenli ifade işlevselliğini bir excel işlevi olarak aşağı yukarı doğrudan ortaya koyar.

Nasıl çalışır

2-3 parametre alır.

1. Düzenli ifadenin kullanılacağı bir metin.
2. Düzenli bir ifade.
3. Sonucun nasıl görünmesi gerektiğini belirten bir biçim dizesi. Bu, $0, $1, $2 ve benzerlerini içerebilir. $0tüm eşleşmedir,$1ve yukarısı düzenli ifadedeki ilgili eşleşme gruplarına karşılık gelir. Varsayılan değer$0`dır.

Bazı örnekler

Bir e-posta adresini çıkarma:

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

Sonuçlar geldi: some@email.com

Birkaç alt dizeyi çıkarma:

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

Sonuçlar geldi: E-Posta: some@email.com, İsim: Peter Gordon

Tek bir hücredeki birleşik bir dizeyi birden fazla hücredeki bileşenlerine ayırmak için:

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

Sonuçlar geldi: Peter Gordon some@email.com ...

Nasıl kullanılır

Bu UDF'yi kullanmak için aşağıdakileri yapın (kabaca bu Microsoft sayfasına dayanmaktadır. Orada bazı iyi ek bilgiler var!):

1. Excel'de Makro etkinleştirilmiş bir dosyada ('.xlsm') Microsoft Visual Basic for Applications Düzenleyicisini açmak için ALT+F11 tuşlarına basın.
2. Düzenli İfadeler kitaplığına VBA referansı ekleyin (utanmadan Portland Runners++ answer'den kopyalanmıştır):
   1. Araçlar -> Referanslar* üzerine tıklayın (lütfen Almanca ekran görüntüsünü mazur görün)
   2. Listede Microsoft VBScript Regular Expressions 5.5 öğesini bulun ve yanındaki onay kutusunu işaretleyin.
   3. Tamam'a* tıklayın.
3. Modül Ekle öğesine tıklayın. Modülünüze farklı bir ad verirseniz, Modülün aşağıdaki UDF ile aynı ada sahip olmadığından emin olun (örneğin, Modülü Regex ve fonksiyonu regex olarak adlandırmak #NAME! hatalarına neden olur).

4. Ortadaki büyük metin penceresine aşağıdakileri ekleyin:

    Function regex(strInput As String, matchPattern As String, Optional ByVal outputPattern As String = "$0") Varyant Olarak
        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

        inputRegexObj ile
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = matchPattern
        İle Bitir
        outputRegexObj ile
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = "\$(\d+)"
        İle Bitir
        outReplaceRegexObj ile
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
        İle Bitir

        Set inputMatches = inputRegexObj.Execute(strInput)
        Eğer inputMatches.Count = 0 ise O zaman
            regex = False
        Başka
            Set replaceMatches = outputRegexObj.Execute(outputPattern)
            replaceMatches içindeki Her replaceMatch için
                replaceNumber = replaceMatch.SubMatches(0)
                outReplaceRegexObj.Pattern = "\$" & replaceNumber

                Eğer replaceNumber = 0 ise O zaman
                    outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).Value)
                Başka
                    Eğer replaceNumber > inputMatches(0).SubMatches.Count Sonra
                        'regex = "A'dan yüksek $ etiketine kadar bulundu. İzin verilen en büyük $" & inputMatches(0).SubMatches.Count & "."
                        regex = CVErr(xlErrValue)
                        Çıkış Fonksiyonu
                    Başka
                        outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1))
                    End Eğer
                End Eğer
            Sonraki
            regex = outputPattern
        End Eğer
    Fonksiyonu Sonlandır

5. Uygulamalar için Microsoft Visual Basic* Düzenleyici penceresini kaydedin ve kapatın.

İşte benim denemem:

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