Excel 函数学习09-使用Excel函数从混合文本中取数值

如何使用函数公式从指定字符串中获取数据，包含了以下几种情况：

1 取固定位置和长度的数值
2 取字符串前面的数值
3 取字符串后面的数值
4 取字符串最大的数值
5 混合文本取数值
6 取字符串中身份证号
7 获取字符串中指定大小的数值
8 获取字符串中所有的数值的和
9 获取字符串中所有的数值
说明1：使用任何工具处理数据，发现数据的规律是前提必要条件。这些规律包含了数据的类型、位置、长度、序列、构成特点等，如果数据没有规律……那就只能请秘书了。当然，通常数据都是有规律的。用一句俗话来讲，生活从来不缺乏美，而缺乏一双发现美的眼睛，数据规律亦如此~

说明2：Excel解决问题的方式从来不是一种，请记得还有个快速填充（快捷键Ctrl+E）；但快速填充并不是绝对可靠的，另外，和函数公式相比，它并没有构建结果和数据源之间的动态关联，也不支持横向填充使用。

1 取固定位置和长度的数值

如下图所示，需要在B列计算A列中的电话号码。

观察数据规律如下：

1）电话号码处在关键字“电话”后面。
2）电话号码都是7位。

公式如下：

=MID(A2,FIND("电话",A2)+2,7)

FIND("电话",A2)+2，查找关键字“电话”出现的位置，+2是扣掉“电话”本身，其结果也就是电话号码出现的位置。使用MID函数从该位置开始取7个字符，即为电话号码。

2 取字符串前面的数值

如下图所示，需要获取字符串开头的数值。

观察数据规律如下：

1）数值的位置在字符串的开头

2）数值长度不一致

公式如下：

=-LOOKUP(0,-LEFT(A2,ROW( $1:$ 11)))

-LEFT(A2,ROW( $1:$ 11))，从A2单元格源字符串的左侧，依次提取1到15个字符，得到如下所示的内存数组。

{"7";"72";"724";"7247";"72473";"724737";"7247371";"7247371李";"7247371李子"……}

使用一个减负运算，将文本数值转换为数值，文本转换为错误值。

然后使用LOOKUP的查询套路，获取最后出现的数值，也就是长度最大的数值。

最后再使用一个减负运算，将数据还原为正。

3 取字符串后面的数值

如下图所示，需要获取字符串右侧的数值。

观察目标数据规律如下：

1）出现在字符串的右侧
2）字符长度不一致

公式如下：

=-LOOKUP(0,-RIGHT(A2,ROW( $1:$ 15)))

类似上一题。

4 取字符串最大的数值

如下图所示，需要获取A列数据中的最大值

观察数据规律：

1）数值都是整数，最大不超过2位数。

最大值数组公式如下：

=MAX(IF(ISNUMBER(FIND(ROW( $1:$ 99),A2)),ROW( $1:$ 99)))

公式使用FIND函数判断1-99的序列号是否在A2单元格中存在，如果存在则返回1~99的序列号，最后使用MAX函数从中获取最大值。

但最小值并不能使用以下数组公式：

=MIN(IF(ISNUMBER(FIND(ROW( $1:$ 99),A2)),ROW( $1:$ 99)))
如果数值并非都是低长度的整数，又或者可能包含小数点呢？

最大值数组公式如下：

=MAX(IFERROR(--MID(A2,ROW( $1:$ 99),COLUMN(A:O)),""))

--MID(A2,ROW( $1:$ 99),COLUMN(A:O))部分，从A2单元格源字符串的第1、2、3……99字符的位置，分别提取1、2、3……直至15位长度的字符，使用减负运算，将文本数值转换为数值，纯文本转换为错误值。

然后使用IFERROR函数将错误值转换为假空，最后使用MAX函数从中获取最大值。

但最小值并不能使用以下数组公式：

=MIN(IFERROR(--MID(A2,ROW( $1:$ 99),COLUMN(A:O)),""))

5 混合文本取数值

如下图所示，需要按顺序从A列数据中获取首个电话号码和末个电话号码

观察数据规律如下：

1）位置不固定

2）长度均为7位

获取首个电话号码，数组公式：

=VLOOKUP(0,MID(A2,ROW( $1:$ 99),7){0,1},2,0)*

获取末尾电话号码，数组公式：

=VLOOKUP(0,MID(A2,ROW( $1:$ 99),7){0,1},2)*

和上一条VLOOKUP函数相比，VLOOKUP的匹配机制使用了模糊匹配。

当然也可以使用LOOKUP函数：

=-LOOKUP(0,-MID(A2,ROW( $1:$ 15),7))

关于LOOKUP和VLOOKUP返回结果的区别，也就是VLOOKUP返回首个匹配结果，VLOOKUP返回最后的匹配结果，
除了使用VLOOKUP函数和LOOKUP函数，也可以使用定位法：

=MID( $A2,SMALL(IF(ISNUMBER(--MID($ A2,ROW( $1:$ 50),7)),ROW( $1:$ 50)),COLUMN(A1)),7)

IF(ISNUMBER(--MID( $A2,ROW($ 1: $50),7)),ROW($ 1:$50)部分，MID函数从1、2、3……50的位置，分别提取7个字符，使用减负运算搭配ISNUMBER函数判断是否为数值，如果为数值，则返回序列号。

然后使用SMALL函数依次从小到大，也就是从前向后，获取位置序号，并使用MID函数提取结果。

6 取字符串中身份证号

如下图所示，需要获取A列中的身份证号。

观察数据规律如下：

1）身份证号是字符串中唯一的数值
2）身份证长度为18位

公式如下：

=MID(A2,MIN(FIND(ROW( $1:$ 10)-1,A2&1/17)),18)

MIN(FIND(ROW( $1:$ 10)-1,A2&1/17)部分，获取首次出现数值的位置。然后使用MID函数按位置获取18个字符长度的数据。

1/17意思就是拿1除17，其结果是一个包含了0-9数字的字符串。A2&1/17的作用，是防止FIND函数查找不到某个数值时，返回错误值。

注意：获取身份证并不能使用前面讲述的套路，比如MAX/VLOOKUP/LOOKUP等，这是因为函数公式能计算的数值最大精度是15位，超过15位的部分将被系统修改为0。这就是为什么我们之前的函数公式，截取数值长度时，均使用的15，而不是更大值。

当字符串中身份证不是首次出现的数值时，可以使用以下数组函数获取结果：

=MID(A2,MATCH(0,MID(A2,ROW( $1:$ 99),18)*0,0),18)

MID(A2,ROW( $1:$ 99),18)*0部分，从A2源字符串的1、2、3~~99个字符的位置，分别提取18个字符，然后乘以0，将数值转换为0，非数值转换为错误值。

使用MATCH函数获取获取首个0出现的位置，也就是首个连续18个数值字符出现的位置。

最后使用MID函数获取结果。

7 获取字符串中最小的数值

如下图所示，需要获取A列数据中最小的数值。

观察数据规律如下：

1）数值均处于单位个前面。

数组公式如下：

=MIN(IFERROR(--TEXT(MID(TEXT(MID(SUBSTITUTE("a"&A2,"个",REPT(" ",50)),ROW( $1:$ 199),15),),2,15),"0;-0;0"),""))

SUBSTITUTE(A2,"个",REPT(" ",99))，将单位个替换为99个空格。

MID(SUBSTITUTE(A2,"个",REPT(" ",50)),ROW( $1:$ 199),15)，从1~199的位置，分别提取15位长度的字符。

TEXT(MID(SUBSTITUTE("a"&A2,"个",REPT(" ",50)),ROW( $1:$ 199),15),)，如果MID函数计算结果为数值，则屏蔽为假空。

MID(TEXT(MID(SUBSTITUTE("a"&A2,"个",REPT(" ",50)),ROW( $1:$ 199),15),),2,15)，从第2个位置，再分别提取15位长度的字符。为什么从第2个位置？数值的左侧必然为文本，从第2位取结果为数值的方才为完整的数值部分。

TEXT(MID(TEXT(MID(SUBSTITUTE("a"&A2,"个",REPT(" ",50)),ROW( $1:$ 199),15),),2,15),"0;-0;")，再使用一个TEXT函数屏蔽掉零值和文本。

最后使用IFERROR函数屏蔽掉错误值，使用MIN函数获取最小值，即为结果。

两个TEXT函数的应用是这个函数套路的精髓……

提取第N大的数值，比如第2大的数值：

=LARGE(IFERROR(--TEXT(MID(TEXT(MID(SUBSTITUTE(A2,"个",REPT(" ",50)),ROW( $1:$ 199),15),),2,15),"0;-0;0"),""),2)

8 获取字符串中所有字符的和

如下图所示，需要获取A列数据内所有数值的和。

9 获取字符串中所有的数值

B2数组公式：

=TRIM(TEXTJOIN(,TRUE,IFERROR(--MID(A2,ROW( $1:$ 99),1)," ")))