Bagaimana saya dapat menggunakan ekspresi reguler di Excel dan mengambil keuntungan dari Excel's kuat grid-seperti pengaturan untuk manipulasi data?
Dalam fungsi sel untuk kembali sesuai pola atau diganti nilai dalam sebuah string.
Saya mengerti Regex ini tidak ideal untuk banyak situasi (Untuk menggunakan atau tidak menggunakan ekspresi reguler?) sejak excel dapat menggunakan Kiri
, Pertengahan
, Kanan
, Instr
jenis perintah-perintah untuk manipulasi serupa.
Regular expressions yang digunakan untuk Pencocokan Pola.
Untuk digunakan dalam Excel ikuti langkah-langkah berikut :
Langkah 1: Tambahkan VBA referensi untuk "Microsoft VBScript Ekspresi Reguler 5.5"
Langkah 2: Menentukan pola
Definisi dasar:
-
Range.
a-z
sesuai dengan huruf kecil dari a sampai z0-5
pertandingan setiap angka dari 0 sampai 5[]
Sama persis satu dari benda-benda di dalam kurung ini.
[a]
sesuai dengan huruf a[abc]
pertandingan satu huruf yang dapat a, b atau c[a-z]
cocok dengan salah satu huruf dari alfabet.()
Kelompok-kelompok yang berbeda sesuai untuk tujuan pengembalian. Lihat contoh di bawah ini.
{}
Multiplier untuk berulang-ulang salinan dari pola yang ditetapkan sebelumnya.
[a]{2}
dua pertandingan berturut-turut huruf a: aa
[a]{1,3}
pertandingan setidaknya satu sampai tiga huruf a
, aa
, aaa
+
Pertandingan setidaknya satu, atau lebih, dari pola yang ditetapkan sebelumnya.
a+
akan pertandingan berturut-turut a's a
, aa
, aaa
, dan sebagainya?
Pertandingan nol atau satu pola yang ditetapkan sebelumnya.
[a-z]?
pertandingan string kosong atau satu huruf.*
Cocok dengan nol atau lebih pola yang ditetapkan sebelumnya.
[a-z]*
pertandingan string kosong atau string dari huruf..
Cocok dengan semua karakter kecuali newline \n
a.
Pertandingan dua karakter string yang dimulai dengan a dan berakhir dengan apa-apa kecuali \n
|
ATAU operator
|b
berarti a
atau b
dapat dicocokkan.merah|putih|oranye
pertandingan persis salah satu warna.^
TIDAK operator
[^0-9]
karakter tidak dapat berisi sejumlah[^aA]
karakter tidak dapat lebih rendah case a
atau huruf A
\
Lolos karakter khusus yang mengikuti (menimpa atas perilaku)
\.
, \\
, \(
, \?
, \$
, \^
Penahan Pola:
^
Pertandingan harus terjadi di awal string
^a
karakter Pertama harus huruf a
^[0-9]
karakter Pertama harus berupa angka.$
Pertandingan harus terjadi di akhir string
a$
karakter Terakhir harus huruf a
Diutamakan tabel:
Order Name Representation
1 Parentheses ( )
2 Multipliers ? + * {m,n} {m, n}?
3 Sequence & Anchors abc ^ $
4 Alternation |
Standar Karakter Singkatan:
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
Contoh 1: Run macro
Berikut contoh makro terlihat pada nilai di sel A1
untuk melihat jika dulu 1 atau 2 karakter digit. Jika demikian, mereka akan dihapus dan sisanya dari string yang akan ditampilkan. Jika tidak, maka kotak akan muncul memberitahu anda bahwa tidak ada yang cocok. Sel A1
nilai-nilai 12abc
akan kembali abc
, nilai 1abc
akan kembali abc
, nilai abc123
akan kembali "Tidak Cocok" karena angka tidak di awal string.
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
Contoh 2: Run sebagai fungsi sel
Contoh ini sama dengan contoh 1 tapi setup untuk dijalankan sebagai sebuah fungsi sel. Untuk menggunakan, mengubah kode ini:
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
Menempatkan string ("12abc") di sel A1
. Masukkan rumus ini =simpleCellRegex(A1) di cell
B1` dan hasilnya akan menjadi "abc".
Contoh 3: Loop Melalui Berbagai
Contoh ini sama dengan contoh 1 tetapi loop melalui berbagai sel.
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
Contoh 4: Membelah dengan pola yang berbeda
Ini contoh loop melalui berbagai (A1
, A2
& A3
) dan mencari string yang dimulai dengan tiga digit diikuti dengan satu karakter alpha dan kemudian 4 digit numerik. Output perpecahan dengan pola pertandingan ke sel-sel yang berdekatan dengan menggunakan ()
. $1
merupakan pertama pola yang cocok dalam set pertama ()
.
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
Hasil:
Tambahan Contoh-Contoh Pola
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
Untuk membuat penggunaan ekspresi reguler langsung di Excel rumus berikut UDF (user defined function) yang dapat membantu. Itu lebih atau kurang langsung memperlihatkan ekspresi reguler fungsi sebagai fungsi excel.
Dibutuhkan 2-3 parameter.
$0
, $1
, $2
, dan sebagainya. $0
adalah seluruh pertandingan, $1
dan sesuai untuk masing-masing kelompok pertandingan di regular expression. Defaultnya $0
.Mengekstrak alamat email:
=regex("Peter Gordon: [email protected], 47", "\w+@\w+\.\w+")
=regex("Peter Gordon: [email protected], 47", "\w+@\w+\.\w+", "$0")
Hasil: [email protected]
Penggalian beberapa substring:
=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "E-Mail: $2, Name: $1")
Hasil: E-Mail: [email protected] Nama: Peter Gordon
Untuk mengambil terpisah gabungan string dalam satu sel menjadi komponen-komponennya dalam beberapa sel:
=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "$" & 1)
=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "$" & 2)
Hasil: Peter Gordon
[email protected]
...
Untuk menggunakan UDF lakukan hal berikut (kira-kira berdasarkan ini halaman Microsoft. Mereka memiliki beberapa info tambahan ada!):
ALT+F11
untuk membuka Microsoft Visual Basic untuk Aplikasi Editor.Regex
dan fungsi regex
penyebab #NAMA! kesalahan).4. Dalam teks besar jendela di tengah menyisipkan berikut:
Fungsi regex(strInput Sebagai String, matchPattern Sebagai String, Optional ByVal outputPattern Sebagai String = "$0") Sebagai Varian Dim inputRegexObj Seperti Baru VBScript_RegExp_55.RegExp, outputRegexObj Seperti Baru VBScript_RegExp_55.RegExp, outReplaceRegexObj Seperti Baru VBScript_RegExp_55.RegExp Dim inputMatches Sebagai Objek, replaceMatches Sebagai Objek, replaceMatch Sebagai Objek Dim replaceNumber Sebagai Integer
Dengan inputRegexObj .Global = True .MultiLine = True .IgnoreCase = False .Pola = matchPattern Akhirnya Dengan Dengan outputRegexObj .Global = True .MultiLine = True .IgnoreCase = False .Pola = "\$(\d+)" Akhirnya Dengan Dengan outReplaceRegexObj .Global = True .MultiLine = True .IgnoreCase = False Akhirnya Dengan
Set inputMatches = inputRegexObj.Execute(strInput) Jika inputMatches.Count = 0 Maka regex = False Lain Set replaceMatches = outputRegexObj.Execute(outputPattern) Untuk Setiap replaceMatch Di replaceMatches replaceNumber = replaceMatch.SubMatches(0) outReplaceRegexObj.Pola = "\$" & replaceNumber
Jika replaceNumber = 0 Maka outputPattern = outReplaceRegexObj.Mengganti(outputPattern, inputMatches(0).Nilai) Lain Jika replaceNumber > inputMatches(0).SubMatches.Kemudian Menghitung 'regex = "Yang tinggi $ tag ditemukan. Terbesar yang diperbolehkan adalah $" & inputMatches(0).SubMatches.Menghitung & "." regex = CVErr(xlErrValue) Keluar Fungsi Lain outputPattern = outReplaceRegexObj.Mengganti(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1)) End If End If Berikutnya regex = outputPattern End If End Function
Memperluas pada patszim's jawaban bagi mereka yang terburu-buru.
Fungsi RegxFunc(strInput Sebagai String, regexPattern As String) As String Dim regEx Sebagai New RegExp Dengan regEx .Global = True .MultiLine = True .IgnoreCase = False .pola = regexPattern Akhirnya Dengan
Jika regEx.Tes(strInput) Kemudian Set pertandingan = regEx.Execute(strInput) RegxFunc = sesuai(0).Nilai Lain RegxFunc = "tidak cocok" End If End Function
Berikut ini adalah upaya saya:
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
Aku harus menggunakan ini sebagai fungsi sel (seperti UANG
atau VLOOKUP
) dan menemukan bahwa itu mudah untuk:
Fungsi REGPLACE(myRange Sebagai Kisaran, matchPattern Sebagai String, outputPattern As String) As Variant Dim regex Seperti Baru VBScript_RegExp_55.RegExp Dim strInput Sebagai String
strInput = myRange.Nilai
Dengan regex .Global = True .MultiLine = True .IgnoreCase = False .Pola = matchPattern Akhirnya Dengan
REGPLACE = regex.Mengganti(strInput, outputPattern)
End Function
=REGPLACE(B1, "(\w) (\d+)", "$1$2")
(ex: "243" untuk "A243")Berikut adalah regex_subst()
fungsi. Contoh:
=regex_subst("watermellon", "[aeiou]", "")
---> wtrmlln
=regex_subst("watermellon", "[^aeiou]", "")
---> aeeo
Berikut ini adalah modern kode (sederhana bagi saya, sih). Saya tidak't tahu bagaimana untuk membangun output yang cocok menggunakan pola di atas untuk bekerja seperti contoh saya:
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
Saya don't ingin memiliki untuk mengaktifkan perpustakaan referensi yang saya butuhkan script saya untuk menjadi portabel. The Dim foo Seperti Baru VBScript_RegExp_55.RegExp
line disebabkan User Defined Type Not Defined
kesalahan, tapi saya menemukan solusi yang bekerja untuk saya.
Apa yang anda'll ingin lakukan adalah menempatkan contoh string di sel A1
, kemudian menguji strPattern
. Setelah itu's bekerja menyesuaikan kemudian ping
seperti yang diinginkan.
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