Excel中批量提取摘要文本

在审计过程中我们查看序时账的时候,摘要里包含大量信息,有时候我们想要提取出一些自己想要的信息,比如:合同号、报关单号、发货数量等等。

比如在年报审计过程中,一家公司应收账款序时账摘要如上图所示。然而我想提取出最后的18位报关单号。

怎么办呢?

可能你最先想到的用left,right函数,没错这里报关单号在最后18位完全可以用right函数截取到最后18位的数字。

还有没有什么方法呢?

这是方方格子提取数字的工具。

它应该是用VBA里用正则表达式做出来的。

但是我要的是更加强大的工具,不仅可以提取数字、汉字这类的东西。我还想要能提取括号、引号等其他一切只要我想提取,我就能提取出来的工具。

而这就需要使用正则表达式做一个自定义函数。

我在excel home论坛淘了一个现成的自定义函数,作者:香川群子[kagawa]。我只是把默认的几个参数改成了自己能用上的。下面讲讲怎么使用。

公式

=REG(参数1,参数2,参数3,参数4)

参数1:原字符串

参数2:0,非0

参数3:匹配模式(pattern)

参数4:替换文本

(图一)

这个函数包含了四个参数:

参数1就是原字符串,也就是我们要从哪里提取文字。

比如上图中参数1就是下面这个字符串:

nigo81提示:原作者ExcelHome论坛 香川群子[kagawa],nigo修改。增值税率17%,小数就是0.17,今天“2018年8月12号”。

参数2如果是0或者不填写就是代表“替换”也就是说把匹配到的内容,替换成参数4的文本。

如果是1,2,3等非0数字,代表的是“提取”,提取出成功匹配第1,2,3个字符串。

(比如,上面那句话,如果我匹配数字,就会有81、17、0.17、2018、8、12这几个数字都满足条件,如果参数2填写1就提取出“81”,如果填写2就提取出“17”这个数字。以此类推。

参数3:就是正则表达式的匹配内容(pattern),相当于告诉电脑,你想要找出什么样的内容。我设置了默认的9个匹配模式。

这9种匹配的方式的效果见上面的“图一”。这9种模式其实很好记,规律如下:

1,2:都是表示数字。

3,4:都是表示包含字母类。

5:用键盘的话就知道5代表百分数。6就像骰子的6个面,整数小数大小通吃。

7:想象下“中文”的“中”字那个边角就和7很像,代表汉字。

8:很想上下都是一对()括号合起来的,所以代表括号里的内容(各种括号【】{}[]()都可以识别)。

9:和单引号很像,代表引号内的内容(中英文的引号“”,“”,''都可以识别)。

需要注意的是如果提取模式8,模式9,参数2需要填写“-1.2”。

例如:

比如这句话我想提取第一个括号的内容就写公式:

=REG("上面那句话“,-1.2,8)

提取结果是”未开票“。

如果我想提取第二个括号的内容就写公司:

=REG("上面那句话”,-2.2,8)

以此类推,第三个括号就是-3.2,第四个括号就是-4.2.

就只有第8,第9模式特殊点,参数2是填写“-1.2”类型,其他的都是填写“1”类型。

参数4:替换文本。

如果参数2填写的0或者空,代表替换模式,这里就是填写你想把匹配出的内容替换成的内容。就和excel里CTRL+F里的“替换”一样。

如果参数2非零,也就是提取模式。参数4就不用填写。

如果以上内容你没有看懂,就看下本文最后附件“正则表达式说明文档”看看里面的公式和说明就很容易理解。

举个栗子

这里举几个操作的例子,方便理解:

1、提取合同号。

2、提取括号里的内容。

3、替换文本。

我们在做往来分款项性质的时候,有些二级科目是“保证金”,”押金“,”装修押金“,这个时候我们可能披露的时候都是披露成一个”保证金及押金“,但是几百条客商的性质,如果我们用ctlr+f替换功能的时候我们先把”装修押金“全部替换为”保证金及押金“,然后如果再把”保证金“替换为”保证金及押金“的时候,我们会发现前一步我们刚替换完的“装修押金”的款项性质变成了“保证金及押金及押金”。

不知道你之前有没有遇到这种困惑。然后现在利用公式,我们不会出现这个问题。

我们只需要=reg(A3,,$A$2,$D$2)

这个公式就可以解决了。这里参数2没填就代表“替换”

参数三:(装修押金|保证金|押金),代表匹配“装修押金”或“保证金”或“押金”,中间用"|"隔开就可以了。很简单。

正则表达式

上面的参数三默认的1-9种匹配模式应该对于我们来说够用了。但是如果有一天你发现这满足不了你了。那么你可以学习正则表达式,它可以让你提取出任何你想要的东西。

当然正则表达式精通比较难,但是如果我们只是日常使用的话,其实也很简单。

下面我就把你可能会用到的并且两分钟能学会的正则表达式教给你:

元字符

\w 任意一个英文单词字符,包括A-Z,a-z,0-9或下划线_

\d 任意一个数字 0-9

. 英文句号表示任意一个除换行符外的字符

\s 任意不可见字符。包括空格、换行符、回车、换页

元字符就是最基础的字符,他们就像搭房子的砖头,我们要做的就是给他们抹点水泥,搭建起我们需要的房子。

比如我们回到开头的报关单号的例子:

应收12月XX外销原粉36*4220、18*4052报关单号790320160000000509

我们想提取出报关单号:

我们想到了"\d"表示数字,但是这个只是代表一个数字,那么怎么表示数量呢?

* 表示0个和多个字符

+ 表示至少一个字符

? 表示0个或1个字符

表示n个字符

表示n-m个字符

所以我们可以用\d+表示一串数字。但是这样的话,首先提取出来的是12。

这里我们知道报关单号是18位所以,我们可以直接写成

\d就可以了。

其他的相关知识如果有兴趣可以简单学习下。文末附件我留了一个VBA的正则表达式学习文档。

如何安装使用

附件中的说明文档打开,可以看看函数使用方法。

如果你想一直用这个函数,那么就“.xlam"的加载宏文件加载到你的excel。具体方法如下:

点击“浏览”,选中“xlam"文件后点击”确定“。

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20180813G0004400?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 yunjia_community@tencent.com 删除。

同媒体快讯

扫码关注云+社区

领取腾讯云代金券