Excel中使用正则表达式

Excel另类条件化格式

1 前言

我们知道，Excel的条件化格式可以根据单元个的内容来设置整个单元格的格式。如图1中的C列，当单元格的内容为“结束”时，字体颜色为绿色，为“进行中”时，颜色为黄色。但如果我们想把B列的部分特定文字(如日期2012-3-4)设置成不同的字体和颜色， Excel的条件化格式工具是不能实现的，需要另辟蹊径。

图1

2 实现过程

2.1 目标

实现B列单元格中日期字符串的字体设置成“Arial Black”字体，颜色为红色。

2.2 设计思路

Excel的VBA编程功能很强大，可以轻松实现上述目标。程序的设计思路是：找到日期字符串，然后设置字体格式。本例中利用了正则表达式来寻找日期字符串，通过Characters对象的Font属性来设置字符串的格式。

2.3 正则表达式

我们在处理文本时，经常会遇到一些满足某种规则的字符串。比如，一个无符号的整数由一连串的数字构成，本例中的日期是满足“整数-整数-整数”规则的字符串。正则表达式就是用来指定这种规则的。如果某个字符串满足正则表达式指定的规则，则称该字符串为正则表达式的一个“匹配串”。正则表达式中用\d 来匹配单个数字，用\d+来匹配连续的多个数字，这样本例中用"\d+-\d+-\d+"就可以匹配日期字符串。Excel的VBA可以通过引用vbscript中的正则标定式对象"vbscript.regexp"来使用正则表达式。

2.4 Characters函数

语法：Characters(start, length)

参数：Start是指从第几个字符处开始选择，length是要返回的字符数。

返回：Characters对象。用来控件文本中某一范围的字符串。例如，通过Characters对象的Font属性可以设置字符串的字体格式。

2.5程序代码

把FormatClick程序和“更新”按钮的Click事件关联起来，只要点击“更新”按钮就可以更新日期的字体的名称和颜色了。具体程序如下:

Sub FormatClick()

'声明正则表达式对象

Dim RegEx As Object

'引用正则标定式对象

Set RegEx = CreateObject("vbscript.regexp")

'设置正则标定式对象属性

With RegEx

'Global=True意味着在整个搜索字符串时模式是全部匹配，False则只匹配第一个

.Global = True

'设置正则标定式

.Pattern = "\d+-\d+-\d+"

End With

'计算最后的行号

RowNo = [B1024576].End(xlUp).Row

For i = 2 To RowNo

'寻找匹配串

Set Matches = RegEx.Execute(ActiveSheet.Cells(i, 2).Text)

'遍历匹配集合

For Each Match In Matches

'Match.FirstIndex是指匹配的起始位置

'Match.Length是指匹配的长度

With ActiveSheet.Cells(i, 2).Characters(Start:=Match.FirstIndex + 1, Length:=Match.Length).Font

'设置字符串字体的格式

.Name = "Arial Black"

.Color = RGB(255, 0, 0)

End With

Next

Next

'释放正则标定式对象

Set RegEx = Nothing

End Sub

2.6 实现效果

通过简短的几行代码就可以实现预定的目标，如图2。本例只是抛砖引玉，通过VBA编程可以实现更复杂的另类条件化格式。

图2