在EXCEL工作表中使用Regular Expression
無可置疑的Regular Expression 是文字處理上很強大的工具,在一些常用的文字編輯器上都有這個功能,EXCEL內建的函式並沒有RegExp,
因此我們要透過自訂巨集函式(VBScript)來處理...
======================================================
步驟一: 開啟一個新的excel工作表,建立如下內容...
A1: aaa,bbb,ccc
B1: 10k+123~k50
然後存檔為"Book1.xls"
---------------------------------------------------------
步驟二: 按[Alt]+[F8],開啟巨集面板,在巨集名稱裡輸入"RegEXP",
並將巨集存放在:設定為"Book1.xls",然後按右邊的"建立"...
---------------------------------------------------------
步驟三:
在VB的程式編輯視窗中的最上方貼入下列code...
代碼: 選擇全部
Option Explicit
#Const LateBind = True
Function RegExpSubstitute(ReplaceIn, _
ReplaceWhat As String, ReplaceWith As String)
#If Not LateBind Then
Dim RE As RegExp
Set RE = New RegExp
#Else
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
#End If
RE.Pattern = ReplaceWhat
RE.Global = True
RegExpSubstitute = RE.Replace(ReplaceIn, ReplaceWith)
End Function
Function RegExpFind(FindIn, FindWhat As String, _
Optional IgnoreCase As Boolean = False)
Dim i As Long
#If Not LateBind Then
Dim RE As RegExp, allMatches As MatchCollection, aMatch As Match
Set RE = New RegExp
#Else
Dim RE As Object, allMatches As Object, aMatch As Object
Set RE = CreateObject("vbscript.regexp")
#End If
RE.Pattern = FindWhat
RE.IgnoreCase = IgnoreCase
RE.Global = True
Set allMatches = RE.Execute(FindIn)
ReDim rslt(0 To allMatches.Count - 1)
For i = 0 To allMatches.Count - 1
rslt(i) = allMatches(i).Value
Next i
RegExpFind = rslt
End Function
步驟四...存檔後回到Book1.xls,用滑鼠點一下A2,
然後在上方的函式輸入區輸入 =RegExpSubstitute(A1,"(.{1,3})(,)(.{1,3})(,)(.{1,3})","$5-$3-$1")
完成後按一下[v](輸入),這時你會發現A2的儲存格顯示為 ccc-bbb-aaa ,
表示目前已經可以在EXCEL中運行Regular Expression的強大功能了~!
(註:儲存格A1的內容為 aaa,bbb,ccc)
同理可證如果要將B1(內容為 B1: 10k+123~k50)透過RegExp把 k+ 置換為 . 的話,
只要這麼寫就可以了~ =RegExpSubstitute(C1,"(d{1,3})([kK]++)(d{1,3})","$1.$3")
完成後按一下[v](輸入),這時你會發現A2的儲存格顯示為 10.123~k50 了
記得要再去設定一下巨集的安全性,這樣就完成了。
-------------------------------------------------------------
若要去除 html code 的語法為 =RegExpSubstitute(A1, "<(.|n)*?>", "") 就可以了.