数据清洗和处理--正则表达式

我们日常工作中经常会处理一些不规范的数据，通过正则表达式可以很轻易的匹配出需要的信息，快速过滤数据。基于正则表达式可以制作海量数据清洗系统，网络爬虫，网络日志数据分析，表单验证等。

正则表达式简化了我们的代码量，比如提取公式中的单元格区域和函数名，若用代码写会非常繁琐，而正则表达式只有一行字符串。

要从字符串中取出“7开头5位数”的凭证号，可以用正则表达式获取，提取表达式为：（非数字）+7+（连续4位数）+（非数字）。

实例链接：https://pan.baidu.com/s/1W84-xaSe52COEXTiZxVjug

提取码：wx5l

–来自百度网盘超级会员V5的分享

VBA中不支持正则的反向肯定预查，可以嵌套正则表达式来处理。

(?<=pattern)

反向(look behind)肯定预查，与正向肯定预查类似，只是方向相反。例如，"(?<=95|98|NT|2000)Windows"能匹配"2000Windows"中的"Windows"，但不能匹配"3.1Windows"中的"Windows"。

做一个自定义函数reg，先用表达式取出需要的字符，这时最前面可能会有非数字字符。

再嵌套表达式^\D去掉前面的非数字。

自定义方法将凭证号全部展开

也可以选择单元格区域，加上转置公式生成一列排好序的清单，自定义函数实现方法可查看源码。

VBA中文本排序可以调用javascript对象快速实现。

Set js = CreateObject(“msscriptcontrol.scriptcontrol”)

js.Language = “javascript”

js.AddCode “function arrsort(a){return a.toArray().sort()}”

js.codeobject.arrsort(array)

在正则实例表中用replaceValue(A2,B2)自定义函数可以对一些表达式数据进行替代。

常用的元字符

代码	说明
.	匹配除换行符以外的任意字符
\w	匹配字母或数字或下划线或汉字
\s	匹配任意的空白符
\d	匹配数字
\b	匹配单词的开始或结束
^	匹配字符串的开始
$	匹配字符串的结束

常用的限定符

代码/语法	说明
*	重复零次或更多次
+	重复一次或更多次
?	重复零次或一次
{n}	重复n次
{n,}	重复n次或更多次
{n,m}	重复n到m次

常用的反义代码

代码/语法	说明
\W	匹配任意不是字母，数字，下划线，汉字的字符
\S	匹配任意不是空白符的字符
\D	匹配任意非数字的字符
\B	匹配不是单词开头或结束的位置
[^x]	匹配除了x以外的任意字符
[^aeiou]	匹配除了aeiou这几个字母以外的任意字符

分组语法

捕获
(exp)	匹配exp,并捕获文本到自动命名的组里
(?<name>exp)	匹配exp,并捕获文本到名称为name的组里，也可以写成(?'name'exp)
(?:exp)	匹配exp,不捕获匹配的文本，也不给此分组分配组号
位置指定
(?=exp)	匹配exp前面的位置
(?<=exp)	匹配exp后面的位置
(?!exp)	匹配后面跟的不是exp的位置
(?<!exp)	匹配前面不是exp的位置
注释
(?#comment)	这种类型的组不对正则表达式的处理产生任何影响，用于提供注释让人阅读

懒惰限定符

*?	重复任意次，但尽可能少重复
+?	重复1次或更多次，但尽可能少重复
??	重复0次或1次，但尽可能少重复
{n,m}?	重复n到m次，但尽可能少重复
{n,}?	重复n次以上，但尽可能少重复

常用的处理选项

名称	说明
IgnoreCase(忽略大小写)	匹配时不区分大小写。
Multiline(多行模式)	更改^和$的含义，使它们分别在任意一行的行首和行尾匹配，而不仅仅在整个字符串的开头和结尾匹配。
Singleline(单行模式)	更改.的含义，使它与每一个字符匹配（包括换行符\n）。
IgnorePatternWhitespace(忽略空白)	忽略表达式中的非转义空白并启用由#标记的注释。
RightToLeft(从右向左查找)	匹配从右向左而不是从左向右进行。
ExplicitCapture(显式捕获)	仅捕获已被显式命名的组。
ECMAScript(JavaScript兼容模式)	使表达式的行为与它在JavaScript里的行为一致。

其他语法

\a	报警字符(打印它的效果是电脑嘀一声)
\b	通常是单词分界位置，但如果在字符类里使用代表退格
\t	制表符，Tab
\r	回车
\v	竖向制表符
\f	换页符
\n	换行符
\e	Escape
\0nn	ASCII代码中八进制代码为nn的字符
\xnn	ASCII代码中十六进制代码为nn的字符
\unnnn	Unicode代码中十六进制代码为nnnn的字符
\cN	ASCII控制字符。比如\cC代表Ctrl+C
\A	字符串开头(类似^，但不受处理多行选项的影响)
\Z	字符串结尾或行尾(不受处理多行选项的影响)
\z	字符串结尾(类似$，但不受处理多行选项的影响)
\G	当前搜索的开头
\p{name}	Unicode中命名为name的字符类，例如\p{IsGreek}
(?>exp)	贪婪子表达式
(?<x>-<y>exp)	平衡组
(?im-nsx:exp)	在子表达式exp中改变处理选项
(?im-nsx)	为表达式后面的部分改变处理选项
(?(exp)yes|no)	把exp当作零宽正向先行断言，如果在这个位置能匹配，使用yes作为此组的表达式；否则使用no
(?(exp)yes)	同上，只是使用空表达式作为no
(?(name)yes|no)	如果命名为name的组捕获到了内容，使用yes作为表达式；否则使用no
(?(name)yes)	同上，只是使用空表达式作为no

常用正则表达式：

“^\d+$”　　//非负整数（正整数 + 0）

“^[0-9]*[1-9][0-9]*$”　　//正整数

“^((-\d+)|(0+))$”　　//非正整数（负整数 + 0）

“^-[0-9]*[1-9][0-9]*$”　　//负整数

“^-?\d+$”　　　　//整数

“^\d+(\.\d+)?$”　　//非负浮点数（正浮点数 + 0）

“^(([0-9]+\.[0-9]*[1-9][0-9]*)|([0-9]*[1-9][0-9]*\.[0-9]+)|([0-9]*[1-9][0-9]*))$”　　//正浮点数

“^((-\d+(\.\d+)?)|(0+(\.0+)?))$”　　//非正浮点数（负浮点数 + 0）

“^(-(([0-9]+\.[0-9]*[1-9][0-9]*)|([0-9]*[1-9][0-9]*\.[0-9]+)|([0-9]*[1-9][0-9]*)))$”　　//负浮点数

“^(-?\d+)(\.\d+)?$”　　//浮点数

“^[A-Za-z]+$”　　//由26个英文字母组成的字符串

“^[A-Z]+$”　　//由26个英文字母的大写组成的字符串

“[^a-z+$]”    '由26个英文字母的小写组成的字符串

" [^A-Za-z0-9]+$"　　‘由数字和26个英文字母组成的字符串

“^\w+$”　　//由数字、26个英文字母或者下划线组成的字符串

/^(d{2}|d{4})-((0([1-9]{1}))|(1[1|2]))-(([0-2]([1-9]{1}))|(3[0|1]))$/   // 年-月-日

/^((0([1-9]{1}))|(1[1|2]))/(([0-2]([1-9]{1}))|(3[0|1]))/(d{2}|d{4})$/   // 月/日/年

“^([w-.]+)@(([[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}.)|(([w-]+.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(]?)$”   //Emil

“(d±)?(d{4}-?d{7}|d{3}-?d{8}|^d{7,8})(-d+)?”   //电话号码

“^(d{1,2}|1dd|2[0-4]d|25[0-5]).(d{1,2}|1dd|2[0-4]d|25[0-5]).(d{1,2}|1dd|2[0-4]d|25[0-5]).(d{1,2}|1dd|2[0-4]d|25[0-5])$”   //IP地址

匹配中文字符的正则表达式：[^\u4e00-\u9fa5]

匹配双字节字符(包括汉字在内)：[^\x00-\xff]

匹配空行的正则表达式：\n[\s| ]*\r

匹配HTML标记的正则表达式：/<(.*)>.*<\/\1>|<(.*) \/>/

匹配首尾空格的正则表达式：(^\s*)|(\s*$)

匹配Email地址的正则表达式：\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*

匹配网址URL的正则表达式：^[a-zA-z]+://(\\w+(-\\w+)*)(\\.(\\w+(-\\w+)*))*(\\?\\S*)?$

匹配帐号是否合法(字母开头，允许5-16字节，允许字母数字下划线)：^[a-zA-Z][a-zA-Z0-9_]{4,15}$

匹配国内电话号码：(\d{3}-|\d{4}-)?(\d{8}|\d{7})?

匹配腾讯QQ号：^[1-9]*[1-9][0-9]*$