文本內容的處理技巧
1.Format函數(數字/日期/文本格式化)2.正則表達式經驗技巧3.正則表達式-環視功能
1.Format函數(數字/日期/文本格式化)
1.1Format:數字格式化
1.1.1Format函數常用格式方法
<code>Option
Explicit
Sub
a()
Dim
i, s
i
=532.6542
s
=Format(i, "currency")
MsgBox
s
End
Sub
/<code>
1.1.2Format函數自定義格式
#:代表一位數字".##":小數點後保留兩位
<code>Sub
a()
Dim
i, s
i
=234230.65487
s
=Format(i, ".###")
MsgBox
s
End
Sub
/<code>
以上案例,如果又想數字增加貨幣符號,又應該怎麼操作呢?
<code>Sub
a()
Dim
i, s
i
=234230.65487
s
=Format(i, "￥.###")
MsgBox
s
End
Sub
/<code>
".###"這裡是格式控制符,代表小數點後三位,而￥代表字符本身的意義,並非是格式控制符.
".###"這裡不足三位的小數,還是會保留之前的小數位數,不會補0.
<code>Sub
a()
Dim
i, s
For
i = 3 To 21
s
=Format(Cells(i, 2), "￥.###")
3) = s
Next
i
End
Sub
/<code>
使用".000",則不足位數會自動補0
<code>Sub
a()
Dim
i, s
For
i = 3 To 21
s
=Format(Cells(i, 2), "￥.000")
3) = s
Next
i
End
Sub
/<code>
1.1.3Format函數常用格式符
以下案例就是使用了分號,正數的格式是￥.000,負數的格式是(￥.000),0格式是零,空值的格式是-
<code>Sub
a()
Dim
i, s
For
i = 3 To 21
s
=Format(Cells(i, 2), "￥.000;(￥.000);零;-")
3) = s
Next
i
End
Sub
/<code>
以下是經常犯錯的內容,需要格外注意:
1.2Format:日期格式化
1.2.1VBA日期系統自帶格式
VBA有很多自帶的日期格式,具體如下截圖:
<code>Sub
dateFormat()
Dim
s As String, d As Date
d
=Range("b3").Value
s
=Format(d, "long date")
MsgBox
s
End
Sub
/<code>
代碼顯示的最終結果為
需要注意事項如下:在不同版本、語言的操作系統中,日期顯示格式可能不同
1.2.2VBA日期自定義格式
1.2.2.1常用格式符:d
如果把2016年8月16日,自定義為8月第16天（公元2016年）， Tuesday,需要怎麼操作呢?
<code>Sub
dateFormat()
Dim
s As String, d As Date
d
=Range("b3").Value
s
=Format(d, "m月d天(公元yyyy年),dddd")
MsgBox
s
End
Sub
/<code>
1.2.2.2常用格式符:d,m,yyyy,aaaa
如果把8月第16天（公元2016年）， Tuesday更改為8月第16天（公元2016年）， 星期一,需要怎麼操作呢?
<code>Sub
dateFormat()
Dim
s As String, d As Date
d
=Range("b3").Value
s
=Format(d, "m月d天(公元yyyy年),aaaa")
MsgBox
s
End
Sub
/<code>
1.2.2.3常用格式符:w
常用格式符w又代表什麼意思呢?
<code>Sub
dateFormat1()
Dim
s As String, d As Date
d
=Range("b3").Value
s
=Format(d, "當週第w天,aaaa")
MsgBox
s
End
Sub
/<code>
常用格式符w,使星期一為當週的第一天,又是怎麼自定義呢?
<code>Sub
dateFormat1()
Dim
s As String, d As Date
d
=Range("b3").Value
s
=Format(d, "當週第w天,aaaa", vbMonday)
MsgBox
s
End
Sub
/<code>
常用格式符w,使星期五為當週的第一天,又是怎麼自定義呢?
<code>Sub
dateFormat1()
Dim
s As String, d As Date
d
=Range("b3").Value
s
=Format(d, "當週第w天,aaaa", vbFriday)
MsgBox
s
End
Sub
/<code>
1.2.2.4常用格式符:ww
常用格式符ww代表的是原日期所在的星期,是一年中的第幾周.
<code>Sub
dateFormat1()
Dim
s As String, d As Date
d
=Range("b3").Value
s
=Format(d, "本年度第ww周")
MsgBox
s
End
Sub
/<code>
常用格式符:ww在第幾周存在有爭議的地方.因此就有了firstWeekofYear,以此規定全年的第一週.
firstWeekofYear這個參數有四個取值,具體如下:
firstWeekofYear與firstDayofWeek也相互關聯,具體如下:
a,m,d等這些字母在使用過程中會被認為是格式控制符,如下截圖的m會被認為是月份數字.
如果想正常顯示這些字母應該在字母前面加上\
1.3Format:文本格式化
1.3.1Format:@符合的使用
@符合的使用及其複雜,網上說清楚的也比較少,這一模塊需要反覆學習.
1.3.1.1@少於原文本字符數
<code>Sub
demoStr()
Dim
s As String, r As String
s
="張宏義"
r
=Format(s, "@, ")
MsgBox
r
End
Sub
/<code>
1.3.1.2@超出原文本字符數
<code>Sub
demoStr()
Dim
s As String, r As String
s
="張宏義"
r
=Format(s, "@@, @@@@")
MsgBox
r
End
Sub
/<code>
1.3.2Format:!符合的使用
1.3.2.1 !@配合使用,@少於原文本字符數
<code>Sub
demoStr()
Dim
s As String, r As String
s
="張宏義"
r
=Format(s, "!@, @")
MsgBox
r
End
Sub
/<code>
image.png
1.3.2.2 !@配合使用,@超出原文本字符數
<code>Sub
demoStr()
Dim
s As String, r As String
s
="張宏義"
r
=Format(s, "!@, @@@@")
MsgBox
r
End
Sub
/<code>
1.4 Format其他參考用法
2.正則表達式經驗技巧
2.1正則表達式:捕獲組
捕獲組：使用圓括號,將匹配結果中的局部內容單獨抽取出來。
2.1.1案例1:將客戶的區號和本地號碼提取出來.
1.首先我們寫出一個正則表達式.以上截圖的數字特徵是:多位數字-多位數字,正則表達式可以寫成\d+-\d
2.需要將區號和本地號區分開,就用().
3.在VBA上寫相關的正則表達代碼
<code>Option
Explicit
Sub
matchDemo()
Dim
i As Long, s As String, myReg As Object
Dim
myMatches As Object, myMatch As Object
s
=Range("B2").Value
Set
myReg = CreateObject("vbscript.regexp")
=True
="(\d+)-(\d+)"
Set
myMatches = myReg.Execute(s)
i
=8
For
Each myMatch In myMatches
2) = myMatch.submatches(0)
3) = myMatch.submatches(1)
i
=i + 1
Next
myMatch
End
Sub
/<code>
2.1.2案例2:多級菜單按-用()分解開
按照案例1的方法,發現查找的結果不符合要求,使用多個嵌套的捕獲組比較複雜,並非達到我們所要求的結果.
錯誤的原因是,量詞對分組有效,對捕獲組卻是沒有任何意義的.分組以及捕獲組都是用()表示.在查找的過程,正則表達式先對分組有效,查找之後,再對捕獲組的數據進行分割.如果捕獲組多個有效時,則返回最後一個結果.
為了查找結果的準確性,編寫兩個正則對象.第一個正則表達式是查找所有完整的電話號碼.第二個正則表達式則查找一個電話號碼中的關鍵數字.
<code>Sub matchDemo()
Dim i As
Long, s As
String, j As
Long
Dim myReg1 As
Object, matches1 As
Object
Dim myReg2 As
Object, matches2 As
Object
s = Range("B2"
).Value
Set myReg1 = CreateObject("vbscript.regexp"
)
myReg1.Global
= True
myReg1.Pattern = "(\d+)(-\d+)+"
Set myReg2 = CreateObject("vbscript.regexp"
)
myReg2.Global
= True
myReg2.Pattern = "\d+"
Set matches1 = myReg1.Execute(s)
For
i = 0
To matches1.Count - 1
Set matches2 = myReg2.Execute(matches1(i).Value)
For
j = 0
To matches2.Count - 1
Cells(i + 7
, j + 2
) = matches2(j).Value
Next j
Next i
End Sub
/<code>
2.2正則表達式:非捕獲組
3.正則表達式-環視功能
3.1肯定順序環視:(?=abc)右邊是abc
要求將A1單元格的內容,對北京的關鍵字眼的詞進行分割,並將分割的詞分別放到A2之後的單元格,具體如截圖.
1.對A1單元格的內容進行屬性特點的分析:都是有北京開頭,直到下一個北京結束.正則表達式是"北京\S+北京"
<code>Sub
lookRoundDemo()
Dim
reg As Object, matches As Object
Dim
i As Long, s As String
s
=Cells(1, 1)
Set
reg = CreateObject("vbscript.regexp")
=True
'reg.MultiLine是多行模式
=True
="北京\S+北京"
Set
matches = reg.Execute(s)
For
i = 0 To matches.Count - 1
+ 2, 1) = matches(i).Value
Next
i
End
Sub
/<code>
最終顯示的結果為:
從結果可以看出,正則表達式未達到切分的效果,只是少了最後一個東字.導致的原因是,正則表達式執行的是貪婪搜索.最長的結果更符合貪婪的搜索結果.
2.對A1單元格的內容進行屬性特點的分析:都是有北京開頭,直到下一個北京結束.避免貪婪搜索,實行懶惰搜索,正則表達式是"北京\S+?北京"
<code>Sub
lookRoundDemo()
Dim
reg As Object, matches As Object
Dim
i As Long, s As String
s
=Cells(1, 1)
Set
reg = CreateObject("vbscript.regexp")
=True
'reg.MultiLine是多行模式
=True
="北京\S+?北京"
Set
matches = reg.Execute(s)
For
i = 0 To matches.Count - 1
+ 2, 1) = matches(i).Value
Next
i
End
Sub
/<code>
最終顯示的結果為:
從結果可以看出,正則表達式未達到切分的效果,得到的結果是北京西北京和北京北北京.導致的原因是,正則表達式執行的是貪婪搜索是不會回頭的,對已經掃描過的文字不會再次掃描.
- 第一次搜索的結果是:北京西北京.下次掃描本應該是:站北京北北京南站北京東.因站是開頭,非北京開頭,下次掃描實際是:北京北北京南站北京東
- 第二次搜索的結果是:北京北北京,下次掃描是:南站北京東.因南站是開頭,非北京開頭,以下找不到正則的相關結果,故結束.
具體掃描結果可查看如下截圖:
3.對A1單元格的內容進行屬性特點的分析:都是有北京開頭,直到不是北字的結束.正則表達式是"北京[^北]"
<code>Sub
lookRoundDemo()
Dim
reg As Object, matches As Object
Dim
i As Long, s As String
s
=Cells(1, 1)
Set
reg = CreateObject("vbscript.regexp")
=True
'reg.MultiLine是多行模式
=True
="北京[^北]"
Set
matches = reg.Execute(s)
For
i = 0 To matches.Count - 1
+ 2, 1) = matches(i).Value
Next
i
End
Sub
/<code>
最終顯示的結果為:
從結果可以看出,正則表達式未達到切分的效果,得到的結果是少了北京北.主要是正則表達式是搜索北京開頭以不出現北字為節點進行分割,因此對北京北排除在外.
是否可對正則表達式進行如下截圖的更改呢?其實也是不可行的.
4.對A1單元格的內容進行屬性特點的分析:都是有北京開頭,直到不是北京的光標位置結束.正則表達式是"北京\S+?(?=北京)"
<code>Sub
lookRoundDemo()
Dim
reg As Object, matches As Object
Dim
i As Long, s As String
s
=Cells(1, 1)
Set
reg = CreateObject("vbscript.regexp")
=True
'reg.MultiLine是多行模式
=True
="北京\S+?(?=北京)"
Set
matches = reg.Execute(s)
For
i = 0 To matches.Count - 1
+ 2, 1) = matches(i).Value
Next
i
End
Sub
/<code>
最終顯示的結果為:
出錯導致的原因分析如下截圖:
從結果可以看出,正則表達式未達到切分的效果,得到的結果是少了北京東.原因是搜索到後面北京東的光標右側沒有北京,無法匹配導致.
5.對A1單元格的內容進行屬性特點的分析:都是有北京開頭,直到不是北京的光標位置或最後一行的末尾結束.正則表達式是"北京\S+?(?=北京|$)"
<code>Sub
lookRoundDemo()
Dim
reg As Object, matches As Object
Dim
i As Long, s As String
s
=Cells(1, 1)
Set
reg = CreateObject("vbscript.regexp")
=True
'reg.MultiLine是多行模式
=True
="北京\S+?(?=北京|$)"
Set
matches = reg.Execute(s)
For
i = 0 To matches.Count - 1
+ 2, 1) = matches(i).Value
Next
i
End
Sub
/<code>
最終顯示的結果如下:
6.其他方法
3.2否定順序環視:(?!abc)右邊不是是abc
案例:對區號進行去除,只顯示本地號碼,並將結果返回在A2以下的單元格
正則表達式:"\d+(?!\d|-)"
<code>Sub
lookRoundDemo()
Dim
reg As Object, matches As Object
Dim
i As Long, s As String
s
=Cells(1, 1)
Set
reg = CreateObject("vbscript.regexp")
=True
'reg.MultiLine是多行模式
=True
="\d+(?!\d|-)"
Set
matches = reg.Execute(s)
For
i = 0 To matches.Count - 1
+ 2, 1) = matches(i).Value
Next
i
End
Sub
/<code>
3.3肯定逆序環視&肯否定逆序環視:
VBA暫時不接受這兩種環視
4.正則表達式:環視實現位置
4.1案例1:將B列的原始金額轉換為規範格式
很多方式都可以實現,比較簡單的是正則表達式.用逗號分開,其實就是用光標找到合適的位置進行分開.
4.1.1 正則表達式是"(?=(\d\d\d\d)+元)"
從測試結果可以看出4220元前面也有個逗號,出現的原因是光標在4220的時候,4220剛好有4個數字並且有個元,剛好符合要求.從而可以看出,這個正則表達式其實還不夠嚴謹.
4.1.2 正則表達式是"(?<=\d)(?=(\d\d\d\d)+元)"
符合要求的是光標的左邊有個數字並且光標的右邊有四個數字+元
從下面結果可以看出符合要求,但是VBA不支持逆序環視
4.1.3 正則表達式是"(\d)(?=(\d\d\d\d)+元)"&捕獲組
VBA不支持逆序環視,但是可以用捕獲組代替.(\d)的用$1進行捕獲,而(?=(\d\d\d\d)+元)這部分光標的內容用逗號代替.
<code>Sub demo()
Dim i As
Long, s As
String, reg As
Object
Set reg = CreateObject("vbscript.regexp"
)
reg.Global
= True
reg.Pattern = "(\d)(?=(\d\d\d\d)+元)"
For
i = 3
To 11
s = Cells(i, 2
)
Cells(i, 3
) = reg.Replace(s, "$1,"
)
Next i
End Sub
/<code>代碼顯示的最終結果為:
4.2案例2:網頁信息的轉換
將以下截圖標黃的新聞標題進行提取.
4.2.1找到唯一標識
4.2.2簡化無關字符
使用正則表達式,對無關字符需要簡化
使用正則表達式,避免貪婪搜索和警惕換行問題,以下截圖是常用的方法.
4.2.3酌情設計多層,正則表達式
以下案例就需要酌情設計的
4.3:正則表達式的書籍推薦
image.png
0人點贊
excel函數
