value在excel中怎么解决（分析如何在Excel中实现精准提取）
时间: 08-13
栏目:学习方法
数据提取是Excel中常见的一类问题。
对于相对规律的数据，我们可以用函数（如用Left、Mid、Right）提取，或者用分列、快速填充等方式实现快速提取。
但有些数据或文本，根本没有规律可言，想从中提取目标文本不是一件容易的事。
例如图中B列单元格中的长文本，中间可能既有手机号码，又有座机号码，且这些电话号码的位置不固定。可以说，这是一份完全杂乱无规律的数据。
问题来了——像这样的无规律文本，怎么在Excel中实现精准提取呢？今天我们来介绍一个重要的知识点：正则表达式。
01什么是正则表达式？
正则表达式（Regular Expression），在代码中常简写为regex、regexp或RE，是计算机科学的一个概念，通常被用来检索、替换那些符合某个模式（或规则）的文本。
很多程序设计语言都支持利用正则表达式进行字符串操作，包括VBA。正则表达式表现为一串由提前定义好的特定字符、或由这些特定字符的组合所构成的“规则字符串”，如“\d+”就是一个正则表达式，表示任意多位的数字。
正则表达式中，一般包括两类字符，一种是正常的文本字符，另一种是元字符（就是指那些在正则表达式中具有特殊意义的专用字符）。比如，正则表达式“\babc”表示以“abc”开头的字符串，其中“abc”即为正常的文本字符，而“\b”则是一个元字符，代表“以特定字符开头的”。
以下为大家整理了常用的一些正则字符。
02利用正则表达式提取目标数据
回到本节开头的问题——从长文本中提取电话号码。
为了准确提取出电话号码，我们需要写出电话号码对应的正则表达式。由于号码中全是数字，因此可以使用元字符\d（代表数字）。而手机号与座机号又有不同，手机号是11位连续数字，而座机则是“4位区号-8位号码”的格式，因此提取手机号和座机号的正则表达式是不同的。
提取手机号：\d{11}，表示连续的11位数字，也可以用：1\d{10}，即1开头且后面还有10个数字。
提取座机号：\d{4}-\d+，表示“-”前有4位数字，“-”后面至少有1位数字。
同时提取手机号和座机号：\d{11}|\d{4}-\d+，“|”代表条件或，因此将前面两个正则表达式用“|”连起来，就可以同时提取手机号和座机号。
理解了正则表达式，接下来最关键的问题来了——正则表达式要写在哪里？如何利用正则表达式来提取目标文本呢？
在Excel中，我们需要借助VBA来实现，但是如果你安装了”Excel超能力“插件，则直接在正则输入框中输入正则表达式即可，往下看，会介绍到。
方式1——在VBA中应用正则表达式
第1步：ALT + F11，打开VBE，并新建一个模块；
第2步：在模块中编写如下代码：
Sub 提取电话()
Dim i, j As Long
Dim reg AsObject, PNums As Object
Set reg =CreateObject("VBScript.RegExp") ‘创建正则对象
reg.Global = True
reg.Pattern = "\d{11}|\d{4}-\d+" ‘正则模式，指定目标文本的正则表达式
For i = 3 ToCells(2, 2).End(4).Row
Set PNums =reg.Execute(Cells(i, 2).Value) ‘对单元格文本执行正则表达式
For j = 0 ToPNums.Count - 1
Cells(i,j + 3).Value = PNums(j).Value
Next j
Next i
End Sub
第3步：运行程序，一键即可完成自动提取。
代码中，Set reg =CreateObject("VBScript.RegExp")创建了正则对象，reg.Pattern = "\d{11}|\d{4}-\d+"指定了用于匹配的正则模式，即正则表达式。最后通过循环遍历，对各个单元格执行正则运算（reg.Execute(Cells(i, 2).Value)），并将结果依次填入目标单元格。
方式2——在Excel超能力插件中实现提取
在Excel内，想解决无规律文本的自动提取问题，你需要——第一，具备一定的VBA知识；第二，要熟练掌握正则表达式。但是，大部分朋友并没有学习过VBA，即使给到代码，一时半会也难以理解，更谈不上灵活应用了。
所以，仅仅掌握正则表达式是不够的。
因此，为了方便大家，我在”Excel超能力“插件中设计了一个专门用来完成正则提取的功能。不需要写VBA代码，只要在正则提取框内输入正则表达式，就可以自动完成提取，非常之方便！
下面以提取电话号码为例，来看操作步骤。
第1步：在正则提取框内输入正则表达式：\d{11}|\d{4}-\d+，然后按回车键执行；
第2步：在弹出的对话框中选择要处理的区域，即B3:B6，确定；
第3步：在弹出菜单中选择提取结果的存放位置，选第1个单元格即可，这里为C3单元格，确定后，Excel超能力就会帮我们完成自动提取。
提取后的结果如下：
熟练掌握正则表达式后，你会觉得这个功能简直好用爆了。再举两个例子。
从文本中提取身份证号——由于身份证号后一位有可能是X，因此正则表达式可以写为：\d+X?，即本例长文本中至少有1位数字且结果是数字或者字母X的子文本。
从微信接龙中提取金额——上图中B列文本来自一份微信群接龙，由于每个人的填写习惯不同，有人使用的金额单位为万，也有人用小写的w，还有人用大写的W，总之都表示的“万”。
而且，金额数字除整数外，有的数字还包含小数。因此，用正则表达式“\d+(\.)?\d*[万wW]”表示这一类金额数字。
此外，由于有些人有两个账户暴雷，因此填写的金额为“数字1+数字2”的模式，为了将这类数字也提取出来，使用正则表达式：\d+\+\d{1,}。
如果想同时将这两类金额信息提取到同一列，则可以用条件或“|”将两个正则表达式连起来，形成一个新的正则表达式：\d+(\.)?\d*[万wW]|\d+\+\d{1,}。
将上述正则表达式输入到Excel超能力的”正则提取"框内，按步骤选择数据区域和目标单元格，就能快速实现目标金额的提取。
● 超能力课堂：
一键提取中文、英文、数字、手机号、网址等信息
在Excel超能力的【文本处理】中还有一个【智能提取】功能，即使你不知道正则表达式怎么写，也可以完成一键提取。
这些提取目标包括——提取中文、提取英文、提取数字、提取手机号、提取电话号码、提取身份证号、提取邮政编码、提取Email地址、提取HTML标记、提取IP地址、提取URL。
安装了超能力的朋友，可以自行尝试。
对于相对规律的数据，我们可以用函数（如用Left、Mid、Right）提取，或者用分列、快速填充等方式实现快速提取。
但有些数据或文本，根本没有规律可言，想从中提取目标文本不是一件容易的事。
例如图中B列单元格中的长文本，中间可能既有手机号码，又有座机号码，且这些电话号码的位置不固定。可以说，这是一份完全杂乱无规律的数据。
问题来了——像这样的无规律文本，怎么在Excel中实现精准提取呢？今天我们来介绍一个重要的知识点：正则表达式。
01什么是正则表达式？
正则表达式（Regular Expression），在代码中常简写为regex、regexp或RE，是计算机科学的一个概念，通常被用来检索、替换那些符合某个模式（或规则）的文本。
很多程序设计语言都支持利用正则表达式进行字符串操作，包括VBA。正则表达式表现为一串由提前定义好的特定字符、或由这些特定字符的组合所构成的“规则字符串”，如“\d+”就是一个正则表达式，表示任意多位的数字。
正则表达式中，一般包括两类字符，一种是正常的文本字符，另一种是元字符（就是指那些在正则表达式中具有特殊意义的专用字符）。比如，正则表达式“\babc”表示以“abc”开头的字符串，其中“abc”即为正常的文本字符，而“\b”则是一个元字符，代表“以特定字符开头的”。
以下为大家整理了常用的一些正则字符。
02利用正则表达式提取目标数据
回到本节开头的问题——从长文本中提取电话号码。
为了准确提取出电话号码，我们需要写出电话号码对应的正则表达式。由于号码中全是数字，因此可以使用元字符\d（代表数字）。而手机号与座机号又有不同，手机号是11位连续数字，而座机则是“4位区号-8位号码”的格式，因此提取手机号和座机号的正则表达式是不同的。
提取手机号：\d{11}，表示连续的11位数字，也可以用：1\d{10}，即1开头且后面还有10个数字。
提取座机号：\d{4}-\d+，表示“-”前有4位数字，“-”后面至少有1位数字。
同时提取手机号和座机号：\d{11}|\d{4}-\d+，“|”代表条件或，因此将前面两个正则表达式用“|”连起来，就可以同时提取手机号和座机号。
理解了正则表达式，接下来最关键的问题来了——正则表达式要写在哪里？如何利用正则表达式来提取目标文本呢？
在Excel中，我们需要借助VBA来实现，但是如果你安装了”Excel超能力“插件，则直接在正则输入框中输入正则表达式即可，往下看，会介绍到。
方式1——在VBA中应用正则表达式
第1步：ALT + F11，打开VBE，并新建一个模块；
第2步：在模块中编写如下代码：
Sub 提取电话()
Dim i, j As Long
Dim reg AsObject, PNums As Object
Set reg =CreateObject("VBScript.RegExp") ‘创建正则对象
reg.Global = True
reg.Pattern = "\d{11}|\d{4}-\d+" ‘正则模式，指定目标文本的正则表达式
For i = 3 ToCells(2, 2).End(4).Row
Set PNums =reg.Execute(Cells(i, 2).Value) ‘对单元格文本执行正则表达式
For j = 0 ToPNums.Count - 1
Cells(i,j + 3).Value = PNums(j).Value
Next j
Next i
End Sub
第3步：运行程序，一键即可完成自动提取。
代码中，Set reg =CreateObject("VBScript.RegExp")创建了正则对象，reg.Pattern = "\d{11}|\d{4}-\d+"指定了用于匹配的正则模式，即正则表达式。最后通过循环遍历，对各个单元格执行正则运算（reg.Execute(Cells(i, 2).Value)），并将结果依次填入目标单元格。
方式2——在Excel超能力插件中实现提取
在Excel内，想解决无规律文本的自动提取问题，你需要——第一，具备一定的VBA知识；第二，要熟练掌握正则表达式。但是，大部分朋友并没有学习过VBA，即使给到代码，一时半会也难以理解，更谈不上灵活应用了。
所以，仅仅掌握正则表达式是不够的。
因此，为了方便大家，我在”Excel超能力“插件中设计了一个专门用来完成正则提取的功能。不需要写VBA代码，只要在正则提取框内输入正则表达式，就可以自动完成提取，非常之方便！
下面以提取电话号码为例，来看操作步骤。
第1步：在正则提取框内输入正则表达式：\d{11}|\d{4}-\d+，然后按回车键执行；
第2步：在弹出的对话框中选择要处理的区域，即B3:B6，确定；
第3步：在弹出菜单中选择提取结果的存放位置，选第1个单元格即可，这里为C3单元格，确定后，Excel超能力就会帮我们完成自动提取。
提取后的结果如下：
熟练掌握正则表达式后，你会觉得这个功能简直好用爆了。再举两个例子。
从文本中提取身份证号——由于身份证号后一位有可能是X，因此正则表达式可以写为：\d+X?，即本例长文本中至少有1位数字且结果是数字或者字母X的子文本。
从微信接龙中提取金额——上图中B列文本来自一份微信群接龙，由于每个人的填写习惯不同，有人使用的金额单位为万，也有人用小写的w，还有人用大写的W，总之都表示的“万”。
而且，金额数字除整数外，有的数字还包含小数。因此，用正则表达式“\d+(\.)?\d*[万wW]”表示这一类金额数字。
此外，由于有些人有两个账户暴雷，因此填写的金额为“数字1+数字2”的模式，为了将这类数字也提取出来，使用正则表达式：\d+\+\d{1,}。
如果想同时将这两类金额信息提取到同一列，则可以用条件或“|”将两个正则表达式连起来，形成一个新的正则表达式：\d+(\.)?\d*[万wW]|\d+\+\d{1,}。
将上述正则表达式输入到Excel超能力的”正则提取"框内，按步骤选择数据区域和目标单元格，就能快速实现目标金额的提取。
● 超能力课堂：
一键提取中文、英文、数字、手机号、网址等信息
在Excel超能力的【文本处理】中还有一个【智能提取】功能，即使你不知道正则表达式怎么写，也可以完成一键提取。
这些提取目标包括——提取中文、提取英文、提取数字、提取手机号、提取电话号码、提取身份证号、提取邮政编码、提取Email地址、提取HTML标记、提取IP地址、提取URL。
安装了超能力的朋友，可以自行尝试。
- 分页 >>
阅读全部
