Excel 使用正则表达式提取数据

大家都知道通配符的作用,例如进行字符串替换时,我们可使用*代替任意字符,使用?代替单个字符,这极大的提高了工作效率。不过这似乎不是最强的,专业编程人员还有更高级的秘密武器--正则表达式。然而,在Excel中使用正则表达式,一般只能在VBA中调用,要求比较高。所以,本页中的函数才应运而生,不用写VBA,不用学专业的编程语言,就可以使用正则表达式使自己拥有超强的数据处理能力。

版本:Excel 2003/2017/2010/2013/2016

公式RegexString(Text,Rule,[Advance],[Index]),可使用正则表达式提取特定字符串。参数说明,Text指待处理的字符串文本。Rule指正在表达式,支持常用的正则表达式,为了简化操作,可使用诸如“数字”、“字母”、“小写字母”、“大写字母”、“汉字”等名词代替正则表达式。Advance指正则表达式Rule的书写模式,默认是0,表示使用简单模式,如果设置成1表示高级模式,即Rule是C#正则表达式,比如匹配数字应书写成([\d]{0,})。Index指多个结果时返回全部还是指定的结果,可选参数,默认0,返回全部,1表示返回第一个结果。

常用的正则表达式

一、校验数字的表达式

1 数字:^[0-9]*$

2 n位的数字:^\d{n}$

3 至少n位的数字:^\d{n,}$

4 m-n位的数字:^\d{m,n}$

5 零和非零开头的数字:^(0|[1-9][0-9]*)$

6 非零开头的最多带两位小数的数字:^([1-9][0-9]*)+(.[0-9]{1,2})?$

7 带1-2位小数的正数或负数:^(\-)?\d+(\.\d{1,2})?$

8 正数、负数、和小数:^(\-|\+)?\d+(\.\d+)?$

9 有两位小数的正实数:^[0-9]+(.[0-9]{2})?$

10 有1~3位小数的正实数:^[0-9]+(.[0-9]{1,3})?$

11 非零的正整数:^[1-9]\d*$ 或 ^([1-9][0-9]*){1,3}$ 或 ^\+?[1-9][0-9]*$

12 非零的负整数:^\-[1-9][]0-9"*$ 或 ^-[1-9]\d*$

13 非负整数:^\d+$ 或 ^[1-9]\d*|0$

14 非正整数:^-[1-9]\d*|0$ 或 ^((-\d+)|(0+))$

15 非负浮点数:^\d+(\.\d+)?$ 或 ^[1-9]\d*\.\d*|0\.\d*[1-9]\d*|0?\.0+|0$

16 非正浮点数:^((-\d+(\.\d+)?)|(0+(\.0+)?))$ 或 ^(-([1-9]\d*\.\d*|0\.\d*[1-9]\d*))|0?\.0+|0$

17 正浮点数:^[1-9]\d*\.\d*|0\.\d*[1-9]\d*$ 或 ^(([0-9]+\.[0-9]*[1-9][0-9]*)|([0-9]*[1-9][0-9]*\.[0-9]+)|([0-9]*[1-9][0-9]*))$

18 负浮点数:^-([1-9]\d*\.\d*|0\.\d*[1-9]\d*)$ 或 ^(-(([0-9]+\.[0-9]*[1-9][0-9]*)|([0-9]*[1-9][0-9]*\.[0-9]+)|([0-9]*[1-9][0-9]*)))$

19 浮点数:^(-?\d+)(\.\d+)?$ 或 ^-?([1-9]\d*\.\d*|0\.\d*[1-9]\d*|0?\.0+|0)$

二、校验字符的表达式

1 汉字:^[\u4e00-\u9fa5]{0,}$

2 英文和数字:^[A-Za-z0-9]+$ 或 ^[A-Za-z0-9]{4,40}$

3 长度为3-20的所有字符:^.{3,20}$

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

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

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

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

8 由数字、26个英文字母或者下划线组成的字符串:^\w+$ 或 ^\w{3,20}$

9 中文、英文、数字包括下划线:^[\u4E00-\u9FA5A-Za-z0-9_]+$

10 中文、英文、数字但不包括下划线等符号:^[\u4E00-\u9FA5A-Za-z0-9]+$ 或 ^[\u4E00-\u9FA5A-Za-z0-9]{2,20}$

11 可以输入含有^%&',;=?$\"等字符:[^%&',;=?$\x22]+

12 禁止输入含有~的字符:[^~\x22]+

三、特殊需求表达式

1 Email地址:^\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*$

2 域名:[a-zA-Z0-9][-a-zA-Z0-9]{0,62}(/.[a-zA-Z0-9][-a-zA-Z0-9]{0,62})+/.?

3 InternetURL:[a-zA-z]+://[^\s]* 或 ^http://([\w-]+\.)+[\w-]+(/[\w-./?%&=]*)?$

4 手机号码:^(13[0-9]|14[5|7]|15[0|1|2|3|5|6|7|8|9]|18[0|1|2|3|5|6|7|8|9])\d{8}$

5 电话号码("XXX-XXXXXXX"、"XXXX-XXXXXXXX"、"XXX-XXXXXXX"、"XXX-XXXXXXXX"、"XXXXXXX"和"XXXXXXXX):^(\(\d{3,4}-)|\d{3.4}-)?\d{7,8}$

6 国内电话号码(0511-4405222、021-87888822):\d{3}-\d{8}|\d{4}-\d{7}

7 身份证号(15位、18位数字):^\d{15}|\d{18}$

8 短身份证号码(数字、字母x结尾):^([0-9]){7,18}(x|X)?$ 或 ^\d{8,18}|[0-9x]{8,18}|[0-9X]{8,18}?$

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

10 密码(以字母开头,长度在6~18之间,只能包含字母、数字和下划线):^[a-zA-Z]\w{5,17}$

11 强密码(必须包含大小写字母和数字的组合,不能使用特殊字符,长度在8-10之间):^(?=.*\d)(?=.*[a-z])(?=.*[A-Z]).{8,10}$

12 日期格式:^\d{4}-\d{1,2}-\d{1,2}

13 一年的12个月(01~09和1~12):^(0?[1-9]|1[0-2])$

14 一个月的31天(01~09和1~31):^((0?[1-9])|((1|2)[0-9])|30|31)$

15 钱的输入格式:

16 1.有四种钱的表示形式我们可以接受:"10000.00" 和 "10,000.00", 和没有 "分" 的 "10000" 和 "10,000":^[1-9][0-9]*$

17 2.这表示任意一个不以0开头的数字,但是,这也意味着一个字符"0"不通过,所以我们采用下面的形式:^(0|[1-9][0-9]*)$

18 3.一个0或者一个不以0开头的数字.我们还可以允许开头有一个负号:^(0|-?[1-9][0-9]*)$

19 4.这表示一个0或者一个可能为负的开头不为0的数字.让用户以0开头好了.把负号的也去掉,因为钱总不能是负的吧.下面我们要加的是说明可能的小数部分:^[0-9]+(.[0-9]+)?$

20 5.必须说明的是,小数点后面至少应该有1位数,所以"10."是不通过的,但是 "10" 和 "10.2" 是通过的:^[0-9]+(.[0-9]{2})?$

21 6.这样我们规定小数点后面必须有两位,如果你认为太苛刻了,可以这样:^[0-9]+(.[0-9]{1,2})?$

22 7.这样就允许用户只写一位小数.下面我们该考虑数字中的逗号了,我们可以这样:^[0-9]{1,3}(,[0-9]{3})*(.[0-9]{1,2})?$

23 8.1到3个数字,后面跟着任意个 逗号+3个数字,逗号成为可选,而不是必须:^([0-9]+|[0-9]{1,3}(,[0-9]{3})*)(.[0-9]{1,2})?$

24 备注:这就是最终结果了,别忘了"+"可以用"*"替代如果你觉得空字符串也可以接受的话(奇怪,为什么?)最后,别忘了在用函数时去掉去掉那个反斜杠,一般的错误都在这里

25 xml文件:^([a-zA-Z]+-?)+[a-zA-Z0-9]+\\.[x|X][m|M][l|L]$

26 中文字符的正则表达式:[\u4e00-\u9fa5]

27 双字节字符:[^\x00-\xff] (包括汉字在内,可以用来计算字符串的长度(一个双字节字符长度计2,ASCII字符计1))

28 空白行的正则表达式:\n\s*\r (可以用来删除空白行)

29 HTML标记的正则表达式:<(\S*?)[^>]*>.*?</\1>|<.*? /> (网上流传的版本太糟糕,上面这个也仅仅能部分,对于复杂的嵌套标记依旧无能为力)

30 首尾空白字符的正则表达式:^\s*|\s*$或(^\s*)|(\s*$) (可以用来删除行首行尾的空白字符(包括空格、制表符、换页符等等),非常有用的表达式)

31 腾讯QQ号:[1-9][0-9]{4,} (腾讯QQ号从10000开始)

32 中国邮政编码:[1-9]\d{5}(?!\d) (中国邮政编码为6位数字)

33 IP地址:\d+\.\d+\.\d+\.\d+ (提取IP地址时有用)

34 IP地址:((?:(?:25[0-5]|2[0-4]\\d|[01]?\\d?\\d)\\.){3}(?:25[0-5]|2[0-4]\\d|[01]?\\d?\\d))

网友留言

牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊牛逼啊!! 站长牛逼!!!!(声嘶力竭) 我爱你们!
评论时间: 2021-02-22 22:04:00
需要正则表达式
评论时间: 2020-10-03 21:17:00
genxietigong
评论时间: 2020-09-07 17:26:00
为什么使用高级模式,匹配出来的是空呢?
评论时间: 2020-07-18 18:31:00
@wum,存在多个匹配结果时可以使用index返回指定结果,比如RegexString(Text,Rule,1,2)表示返回第二个结果。具体问题,请联系客服QQ
评论时间: 2020-07-17 17:17:00
你好,我想问下,如果我想匹配第二次出现的字符串,该怎么做 例如: GM~0(GM) US~1(US) LD~2(LDD) LdDD~3(LdDD) NOT~4(NT) NR~5(NR) 我想要取US~1(US) 或者我想分别把他们取下来 GM~0(GM) US~1(US) LD~2(LDD) LdDD~3(LdDD) NOT~4(NT) NR~5(NR)
评论时间: 2020-07-17 16:58:00
好的好的,我试试
评论时间: 2020-07-12 08:01:00
@Zxy 目前不支持数组公式,我得研究研究。如果上午可用下午不可用,中间没有更新过函数库的话,重新打开Excel或勾选函数库试试。
评论时间: 2020-07-11 11:44:00
上午那个index还能用,现在不能了,什么回事。。。。
评论时间: 2020-07-09 16:58:00
谢谢管理,还有一个问题想咨询下,你家的这些自定义函数可以支持数组公式返回么?我试了下貌似不行, {=SUM(IF(RegexExists(A2:A48,B2:B48,1)=C2:C48,1,0))} A列是一堆的公司名,B列是通过正则写的一些匹配公式,C列全部是字母Y,我的目的就是统计A列中能匹配B列中关键字的数量有多少。 譬如A列是一堆公司名,B列我写 京东|叮咚 然后通过数组公式统计 A列中有多少项是能匹配到关键字的 如果我不用数组公式,直接用公式=IF(RegexExists(A2,B$2,1)="Y",1,0),然后sum是正常的,但是套到数组公式就不行了。是不是自定义函数不支持?
评论时间: 2020-07-09 15:01:00
@Zxy 已经加上了,Index指多个结果时返回全部还是指定的结果,可选参数,默认0,返回全部,1表示返回第一个结果。
评论时间: 2020-07-05 13:41:00
@Zxy 暂时还没这功能,我考虑加上
评论时间: 2020-07-02 20:37:00
如果正则匹配到多个结果,我只想取第一个,有什么办法?
评论时间: 2020-07-02 17:05:00
=RegexString(A2,"(B\w{9})",1) 记得加括号
评论时间: 2020-05-13 18:05:00
提取车牌号=RegexString(A1,"([\u4e00-\u9fa5]{1,1}[\dA-Za-z]{5,6})",1)
评论时间: 2020-05-11 21:28:00
这个函数库在哪里呢?
评论时间: 2019-04-28 22:46:00
尝试着使用这个函数,貌似很多都匹配不出来, 在其它的正则表达式检验工具上测试都没有问题的,放到这里就一直返回空白。 比如: 写的 “=RegexString(A2,"B\w{9}",1)” 想匹配下面地址中的“B008OD7Z5I” https://www...Adhesive/dp/B008OD7Z5I/ref=zg_bs_10676812011_1?_encoding=UTF8&psc=1 不知道是我写的有问题 还是什么原因。
评论时间: 2019-01-31 16:48:00

请您留言

获取验证码
提交
关注本站公众号“IT特工队”获得更多技术支持