VBA-SZ第3节|文本内容的处理技巧
最近更新:'2019-05-09'
1.Format函数(数字/日期/文本格式化)
2.正则表达式经验技巧
3.正则表达式-环视功能
1.Format函数(数字/日期/文本格式化)
1.1Format:数字格式化
1.1.1Format函数常用格式方法
Option Explicit
Sub a()
Dim i, s
i = 532.6542
s = Format(i, "currency")
MsgBox s
End Sub
1.1.2Format函数自定义格式
#:代表一位数字
".##":小数点后保留两位
Sub a()
Dim i, s
i = 234230.65487
s = Format(i, ".###")
MsgBox s
End Sub
以上案例,如果又想数字增加货币符号,又应该怎么操作呢?
Sub a()
Dim i, s
i = 234230.65487
s = Format(i, "￥.###")
MsgBox s
End Sub
".###"这里是格式控制符,代表小数点后三位,而￥代表字符本身的意义,并非是格式控制符.
".###"这里不足三位的小数,还是会保留之前的小数位数,不会补0.
Sub a()
Dim i, s
For i = 3 To 21
s = Format(Cells(i, 2), "￥.###")
Cells(i, 3) = s
Next i
End Sub
使用".000",则不足位数会自动补0
Sub a()
Dim i, s
For i = 3 To 21
s = Format(Cells(i, 2), "￥.000")
Cells(i, 3) = s
Next i
End Sub
1.1.3Format函数常用格式符
以下案例就是使用了分号,正数的格式是￥.000,负数的格式是(￥.000),0格式是零,空值的格式是-
Sub a()
Dim i, s
For i = 3 To 21
s = Format(Cells(i, 2), "￥.000;(￥.000);零;-")
Cells(i, 3) = s
Next i
End Sub
以下是经常犯错的内容,需要格外注意:
1.2Format:日期格式化
1.2.1VBA日期系统自带格式
VBA有很多自带的日期格式,具体如下截图:
Sub dateFormat()
Dim s As String, d As Date
d = Range("b3").Value
s = Format(d, "long date")
MsgBox s
End Sub
代码显示的最终结果为
需要注意事项如下:
在不同版本、语言的操作系统中,日期显示格式可能不同
1.2.2VBA日期自定义格式
1.2.2.1常用格式符:d
如果把2016年8月16日,自定义为8月第16天（公元2016年）， Tuesday,需要怎么操作呢?
Sub dateFormat()
Dim s As String, d As Date
d = Range("b3").Value
s = Format(d, "m月d天(公元yyyy年),dddd")
MsgBox s
End Sub
1.2.2.2常用格式符:d,m,yyyy,aaaa
如果把8月第16天（公元2016年）， Tuesday更改为8月第16天（公元2016年）， 星期一,需要怎么操作呢?
Sub dateFormat()
Dim s As String, d As Date
d = Range("b3").Value
s = Format(d, "m月d天(公元yyyy年),aaaa")
MsgBox s
End Sub
1.2.2.3常用格式符:w
常用格式符w又代表什么意思呢?
Sub dateFormat1()
Dim s As String, d As Date
d = Range("b3").Value
s = Format(d, "当周第w天,aaaa")
MsgBox s
End Sub
常用格式符w,使星期一为当周的第一天,又是怎么自定义呢?
Sub dateFormat1()
Dim s As String, d As Date
d = Range("b3").Value
s = Format(d, "当周第w天,aaaa", vbMonday)
MsgBox s
End Sub
常用格式符w,使星期五为当周的第一天,又是怎么自定义呢?
Sub dateFormat1()
Dim s As String, d As Date
d = Range("b3").Value
s = Format(d, "当周第w天,aaaa", vbFriday)
MsgBox s
End Sub
1.2.2.4常用格式符:ww
常用格式符ww代表的是原日期所在的星期,是一年中的第几周.
Sub dateFormat1()
Dim s As String, d As Date
d = Range("b3").Value
s = Format(d, "本年度第ww周")
MsgBox s
End Sub
常用格式符:ww在第几周存在有争议的地方.因此就有了firstWeekofYear,以此规定全年的第一周.
firstWeekofYear这个参数有四个取值,具体如下:
firstWeekofYear与firstDayofWeek也相互关联,具体如下:
a,m,d等这些字母在使用过程中会被认为是格式控制符,如下截图的m会被认为是月份数字.
如果想正常显示这些字母应该在字母前面加上\
1.3Format:文本格式化
1.3.1Format:@符合的使用
@符合的使用及其复杂,网上说清楚的也比较少,这一模块需要反复学习.
1.3.1.1@少于原文本字符数
Sub demoStr()
Dim s As String, r As String
s = "张宏义"
r = Format(s, "@, ")
MsgBox r
End Sub
1.3.1.2@超出原文本字符数
Sub demoStr()
Dim s As String, r As String
s = "张宏义"
r = Format(s, "@@, @@@@")
MsgBox r
End Sub
1.3.2Format:!符合的使用
1.3.2.1 !@配合使用,@少于原文本字符数
Sub demoStr()
Dim s As String, r As String
s = "张宏义"
r = Format(s, "!@, @")
MsgBox r
End Sub
image.png
1.3.2.2 !@配合使用,@超出原文本字符数
Sub demoStr()
Dim s As String, r As String
s = "张宏义"
r = Format(s, "!@, @@@@")
MsgBox r
End Sub
1.4 Format其他参考用法
2.正则表达式经验技巧
2.1正则表达式:捕获组
捕获组：使用圆括号,将匹配结果中的局部内容单独抽取出来。
2.1.1案例1:将客户的区号和本地号码提取出来.
1.首先我们写出一个正则表达式.以上截图的数字特征是:多位数字-多位数字,正则表达式可以写成\d+-\d
2.需要将区号和本地号区分开,就用().
3.在VBA上写相关的正则表达代码
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")
myReg.Global = True
myReg.Pattern = "(\d+)-(\d+)"
Set myMatches = myReg.Execute(s)
i = 8
For Each myMatch In myMatches
Cells(i, 2) = myMatch.submatches(0)
Cells(i, 3) = myMatch.submatches(1)
i = i + 1
Next myMatch
End Sub
2.1.2案例2:多级菜单按-用()分解开
按照案例1的方法,发现查找的结果不符合要求,使用多个嵌套的捕获组比较复杂,并非达到我们所要求的结果.
错误的原因是,量词对分组有效,对捕获组却是没有任何意义的.分组以及捕获组都是用()表示.在查找的过程,正则表达式先对分组有效,查找之后,再对捕获组的数据进行分割.如果捕获组多个有效时,则返回最后一个结果.
为了查找结果的准确性,编写两个正则对象.第一个正则表达式是查找所有完整的电话号码.第二个正则表达式则查找一个电话号码中的关键数字.
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
2.2正则表达式:非捕获组
3.正则表达式-环视功能
3.1肯定顺序环视:(?=abc)右边是abc
要求将A1单元格的内容,对北京的关键字眼的词进行分割,并将分割的词分别放到A2之后的单元格,具体如截图.
1.对A1单元格的内容进行属性特点的分析:都是有北京开头,直到下一个北京结束.正则表达式是"北京\S+北京"
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")
reg.Global = True
'reg.MultiLine是多行模式
reg.MultiLine = True
reg.Pattern = "北京\S+北京"
Set matches = reg.Execute(s)
For i = 0 To matches.Count - 1
Cells(i + 2, 1) = matches(i).Value
Next i
End Sub
最终显示的结果为:
从结果可以看出,正则表达式未达到切分的效果,只是少了最后一个东字.导致的原因是,正则表达式执行的是贪婪搜索.最长的结果更符合贪婪的搜索结果.
2.对A1单元格的内容进行属性特点的分析:都是有北京开头,直到下一个北京结束.避免贪婪搜索,实行懒惰搜索,正则表达式是"北京\S+?北京"
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")
reg.Global = True
'reg.MultiLine是多行模式
reg.MultiLine = True
reg.Pattern = "北京\S+?北京"
Set matches = reg.Execute(s)
For i = 0 To matches.Count - 1
Cells(i + 2, 1) = matches(i).Value
Next i
End Sub
最终显示的结果为:
从结果可以看出,正则表达式未达到切分的效果,得到的结果是北京西北京和北京北北京.导致的原因是,正则表达式执行的是贪婪搜索是不会回头的,对已经扫描过的文字不会再次扫描.
- 第一次搜索的结果是:北京西北京.下次扫描本应该是:站北京北北京南站北京东.因站是开头,非北京开头,下次扫描实际是:北京北北京南站北京东
- 第二次搜索的结果是:北京北北京,下次扫描是:南站北京东.因南站是开头,非北京开头,以下找不到正则的相关结果,故结束.
具体扫描结果可查看如下截图:
3.对A1单元格的内容进行属性特点的分析:都是有北京开头,直到不是北字的结束.正则表达式是"北京[^北]"
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")
reg.Global = True
'reg.MultiLine是多行模式
reg.MultiLine = True
reg.Pattern = "北京[^北]"
Set matches = reg.Execute(s)
For i = 0 To matches.Count - 1
Cells(i + 2, 1) = matches(i).Value
Next i
End Sub
最终显示的结果为:
从结果可以看出,正则表达式未达到切分的效果,得到的结果是少了北京北.主要是正则表达式是搜索北京开头以不出现北字为节点进行分割,因此对北京北排除在外.
是否可对正则表达式进行如下截图的更改呢?其实也是不可行的.
4.对A1单元格的内容进行属性特点的分析:都是有北京开头,直到不是北京的光标位置结束.正则表达式是"北京\S+?(?=北京)"
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")
reg.Global = True
'reg.MultiLine是多行模式
reg.MultiLine = True
reg.Pattern = "北京\S+?(?=北京)"
Set matches = reg.Execute(s)
For i = 0 To matches.Count - 1
Cells(i + 2, 1) = matches(i).Value
Next i
End Sub
最终显示的结果为:
出错导致的原因分析如下截图:
从结果可以看出,正则表达式未达到切分的效果,得到的结果是少了北京东.原因是搜索到后面北京东的光标右侧没有北京,无法匹配导致.
5.对A1单元格的内容进行属性特点的分析:都是有北京开头,直到不是北京的光标位置或最后一行的末尾结束.正则表达式是"北京\S+?(?=北京|$)"
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")
reg.Global = True
'reg.MultiLine是多行模式
reg.MultiLine = True
reg.Pattern = "北京\S+?(?=北京|$)"
Set matches = reg.Execute(s)
For i = 0 To matches.Count - 1
Cells(i + 2, 1) = matches(i).Value
Next i
End Sub
最终显示的结果如下:
6.其他方法
3.2否定顺序环视:(?!abc)右边不是是abc
案例:对区号进行去除,只显示本地号码,并将结果返回在A2以下的单元格
正则表达式:"\d+(?!\d|-)"
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")
reg.Global = True
'reg.MultiLine是多行模式
reg.MultiLine = True
reg.Pattern = "\d+(?!\d|-)"
Set matches = reg.Execute(s)
For i = 0 To matches.Count - 1
Cells(i + 2, 1) = matches(i).Value
Next i
End Sub
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)+元)这部分光标的内容用逗号代替.
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
代码显示的最终结果为:
4.2案例2:网页信息的转换
将以下截图标黄的新闻标题进行提取.
4.2.1找到唯一标识
4.2.2简化无关字符
使用正则表达式,对无关字符需要简化
使用正则表达式,避免贪婪搜索和警惕换行问题,以下截图是常用的方法.
4.2.3酌情设计多层,正则表达式
以下案例就需要酌情设计的
4.3:正则表达式的书籍推荐
image.png
