Categories: Excel

Excel VBA 正規表示法 RegExp 使用教學與範例

本篇介紹如何在 Excel 的 VBA 巨集程式中，使用正規表示法（regular expression）匹配與取代文字。

正規表示法

正規表示法（regular expression，也稱為常規表示法、正規表示式等）是專門用來匹配、取代文字資料的語法規則，在處理各種複雜的文字處理問題時，正規表示法是不可或缺的工具之一。

多數的程式語言與軟體都有支援正規表示法，語法大同小異，在 Excel 與 VBA 巨集程式之中亦可使用。

匹配文字

若要在 VBA 巨集程式中，使用正規表示法匹配文字，可以先建立一個正規表示法的 RegExp 變數，並透過它的 Pattern 屬性設定匹配規則，然後即可呼叫其 Text 方法函數即可進行文字的匹配，以下是一個簡單的 Hello World 範例：

' 建立正規表示法變數
Dim regEx As New RegExp

' 要檢查的文字資料
Dim MyText As String
MyText = "An apple a day keeps the doctor away."

' 設定匹配規則
regEx.Pattern = "aw+e"

' 使用正規表示法匹配文字
If regEx.Test(MyText) Then
  MsgBox ("匹配成功")
Else
  MsgBox ("匹配失敗")
End If

在這個範例的匹配規則中，w+ 代表的是一個以上的任何英文字母、數字或下底線，所以整個 aw+e 的意思就是以 a 開頭、e 結尾的文字，執行之後該匹配規則就會匹配到文字中 apple，所以結果就會是「匹配成功」。

如果在執行含有正規表示法的 VBA 巨集程式時，出現了「使用者自訂型態尚未定義」的編譯錯誤，請啟用 VBA 的「Microsoft VBScript Regular Expressions」引用項目即可解決。

取代文字

若要將匹配的文字以新的文字取代，可以使用 RegExp 的 Replace 方法函數，通常在取代文字時，我們會先以 Test 檢查一下原始文字中是否有可以成功匹配的部份，若找到符合的部分再進行取代，以下是一個簡單的範例：

' 建立正規表示法變數
Dim regEx As New RegExp

' 要匹配與取代的文字資料
Dim MyText As String
MyText = "An apple a day keeps the doctor away."

' 設定匹配與取代規則
regEx.Pattern = "aw+e"

' 替代的文字
Dim MyReplace As String
MyReplace = "ORANGE"

' 使用正規表示法匹配並取代文字
If regEx.Test(MyText) Then
  Result = regEx.Replace(MyText, MyReplace)
  MsgBox ("取代後的文字：" & Result)
Else
  MsgBox ("匹配失敗")
End If

取代所有符合文字

RegExp 的 Replace 在取代匹配的文字時，預設只會取代第一個找到的部分，例如：

Dim regEx As New RegExp

Dim MyText As String
MyText = "吃葡萄不吐葡萄皮，不吃葡萄倒吐葡萄皮。"

regEx.Pattern = "葡萄"

Dim MyReplace As String
MyReplace = "香蕉"

' 使用正規表示法匹配並取代第一個符合的部分
If regEx.Test(MyText) Then
  Result = regEx.Replace(MyText, MyReplace)
  MsgBox ("取代後的文字：" & Result)
Else
  MsgBox ("匹配失敗")
End If

執行後的結果為：

取代第一個符合的部分文字

如果想讓程式一次把原始文字中，所有符合的文字一次全部取代掉，可以將 RegExp 物件的 Global 全域選項設定為 True，這樣就可以取代所有符合的部分：

Dim regEx As New RegExp

Dim MyText As String
MyText = "吃葡萄不吐葡萄皮，不吃葡萄倒吐葡萄皮。"

regEx.Pattern = "葡萄"

Dim MyReplace As String
MyReplace = "香蕉"

' 設定全域選項
regEx.Global = True

' 使用正規表示法匹配並取代所有符合的部分
If regEx.Test(MyText) Then
  Result = regEx.Replace(MyText, MyReplace)
  MsgBox ("取代後的文字：" & Result)
Else
  MsgBox ("匹配失敗")
End If

將所有符合的部分取代之後，結果就會像這樣：

取代所有符合的部分文字

不分大小寫

RegExp 預設在匹配英文字母時，會區分大寫與小寫，也就是說會將大寫與小寫視為不同的文字，若想要讓 RegExp 在匹配文字時，不區分大小寫的話，可以將 IgnoreCase 設定為 True：

Dim regEx As New RegExp

Dim MyText As String
MyText = "Apple APPLE aPPLe APpLe"

regEx.Pattern = "apple"

Dim MyReplace As String
MyReplace = "orange"

regEx.Global = True

' 不分大小寫
regEx.IgnoreCase = True

If regEx.Test(MyText) Then
  Result = regEx.Replace(MyText, MyReplace)
  MsgBox ("取代後的文字：" & Result)
Else
  MsgBox ("匹配失敗")
End If

執行結果為：

不分大小寫取代文字

多行文字

正規表示法的 ^ 與 $ 可以匹配整個字串的開頭與結尾，如果我們的原始文字資料包含很多行的時候，這兩個字符只會匹配整段的文字的開頭與結尾（也就是不管中間的換行字元的意思）。

以下這個範例我們想要把每一行開頭的 This 替換成 THAT，但如果按照預設的方式，只會替換掉第一行的 This，第二行以後的 This 會被忽略：

Dim regEx As New RegExp

' 多行文字
Dim MyText As String
MyText = "This is the first line." & vbNewLine & _
 "This is the second line." & vbNewLine & _
 "This is the third line."

regEx.Pattern = "^This"

Dim MyReplace As String
MyReplace = "THAT"

regEx.Global = True

If regEx.Test(MyText) Then
  Result = regEx.Replace(MyText, MyReplace)
  MsgBox ("取代後的文字：" & Result)
Else
  MsgBox ("匹配失敗")
End If

只取代第一行的開頭

遇到這樣的狀況時，可以將 MultiLine 屬性設定為 True，以多行模式進行匹配，這樣 ^ 與 $ 就會匹配每一行文字的開頭與結尾：

Dim regEx As New RegExp

' 多行文字
Dim MyText As String
MyText = "This is the first line." & vbNewLine & _
 "This is the second line." & vbNewLine & _
 "This is the third line."

regEx.Pattern = "^This"

Dim MyReplace As String
MyReplace = "THAT"

regEx.Global = True

' 多行模式
regEx.MultiLine = True

If regEx.Test(MyText) Then
  Result = regEx.Replace(MyText, MyReplace)
  MsgBox ("取代後的文字：" & Result)
Else
  MsgBox ("匹配失敗")
End If