Excel'de düzenli ifadeleri nasıl kullanabilirim ve veri işleme için Excel'in güçlü ızgara benzeri kurulumundan nasıl yararlanabilirim?
Regex'in birçok durum için ideal olmadığını anlıyorum (Düzenli ifadeleri kullanmak ya da kullanmamak?) çünkü excel benzer manipülasyonlar için Left
, Mid
, Right
, Instr
tipi komutları kullanabilir.
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"'a ekleyin;
Adım 2: Deseninizi tanımlayın
Temel tanımlar:
-
Aralık.
a-z
a'dan z'ye kadar küçük harflerle eşleşir0-5
0'dan 5'e kadar herhangi bir sayıyla eşleşir[]` Bu parantezlerin içindeki nesnelerden tam olarak birini eşleştirin.
[a]
a harfiyle eşleşir[abc]
a, b veya c olabilen tek bir harfle eşleşir[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.
[a]{2}
ardışık iki küçük harf a ile eşleşir: aa`[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.
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.
[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.
[a-z]*
boş dizeyle veya küçük harf dizesiyle eşleşir..Satırbaşı hariç herhangi bir karakterle eşleşir
\n`
a.
a ile başlayan ve \n
dışında herhangi bir şeyle biten iki karakterli bir dizeyle eşleşir|
VEYA operatörü
a|b
, a
ya da b
ile eşleştirilebileceği anlamına gelir.red|white|orange
renklerden tam olarak biriyle eşleşir.^` DEĞİL işleci
[^0-9]
karakteri bir sayı içeremez[^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)
\.
, \\
, \(
, \?
, \$
, \^
Anchoring Kalıpları:
^` Eşleşme dizenin başında gerçekleşmelidir
^a
İlk karakter küçük harf a
olmalıdır^[0-9]
İlk karakter bir sayı olmalıdır.$` Eşleşme dizenin sonunda gerçekleşmelidir
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
</i8> \<\/[a-zA-Z][0-9]\> 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.
2-3 parametre alır.
$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.Bir e-posta adresini çıkarma:
=regex("Peter Gordon: [email protected], 47", "\w+@\w+\.\w+")
=regex("Peter Gordon: [email protected], 47", "\w+@\w+\.\w+", "$0")
Sonuçlar geldi: [email protected]
Birkaç alt dizeyi çıkarma:
=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "E-Mail: $2, Name: $1")
Sonuçlar geldi: E-Posta: [email protected], İsim: Peter Gordon
Tek bir hücredeki birleşik bir dizeyi birden fazla hücredeki bileşenlerine ayırmak için:
=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "$" & 1)
=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "$" & 2)
Sonuçlar geldi: Peter Gordon
[email protected]
...
Bu UDF'yi kullanmak için aşağıdakileri yapın (kabaca bu Microsoft sayfasına dayanmaktadır. Orada bazı iyi ek bilgiler var!):
ALT+F11
tuşlarına basın.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
İş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