- 帖子
- 231
- 主題
- 55
- 精華
- 0
- 積分
- 293
- 點名
- 0
- 作業系統
- winxp
- 軟體版本
- office2003
- 閱讀權限
- 20
- 性別
- 男
- 來自
- KEELUNG
- 註冊時間
- 2010-7-24
- 最後登錄
- 2018-8-28
|
本帖最後由 dechiuan999 於 2015-3-6 10:59 編輯
回復 2# diolin
大大您好：
謝謝大大的相助。
因無法利用EXCEL資料剖析
來分割字串；因而改用REGEXP
來分割字串。
依大大的方式，小弟無法將A欄
已取出的字串再利用REGEXP REPLACE
來清除。
語法如下：- Sub mRegRepStr()
-
- Dim mPtn As String
- Dim mStr As String
- Dim mRepStr As String
- Dim mSht As Worksheet
- Dim mRng As Range, mRng1 As Range
-
- Application.ScreenUpdating = False
- mPtn = "\s+\S+\s+\S+\s+(.*)"
- mRepStr = ""
-
- Set mSht = Worksheets("new")
- With mSht
- Set mRng1 = .Range("a1", .Range("a" & .Rows.Count).End(xlUp))
- For Each mRng In mRng1
- mStr = mRng.Value
- mRng.Value = mRegexpReplace(mPtn, mStr, mRepStr)
- Next
- End With
-
- Set mSht = Nothing
- Set mRng = Nothing
- Set mRng1 = Nothing
-
- End Sub
- Function mRegexpReplace(mPtn As String, mStr As String, mRepStr As String)
-
- 'Regexp Replace
- Dim mRegReplace As New VBScript_RegExp_55.RegExp
-
- Set mRegReplace = New VBScript_RegExp_55.RegExp
- With mRegReplace
- .Global = True
- .IgnoreCase = True
- .Pattern = mPtn
- mRegexpReplace = .Replace(mStr, mRepStr)
- End With
-
- Set mRegReplace = Nothing
- End Function
|
|
發表於 2015-3-5 16:49
| 
