如何使用函数公式从指定字符串中获取数据，包含了以下几种情况：
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(11)))
-LEFT(A2,ROW(11))，从A2单元格源字符串的左侧，依次提取1到15个字符，得到如下所示的内存数组。
{"7";"72";"724";"7247";"72473";"724737";"7247371";"7247371李";"7247371李子"……}
使用一个减负运算，将文本数值转换为数值，文本转换为错误值。
然后使用LOOKUP的查询套路，获取最后出现的数值，也就是长度最大的数值。
最后再使用一个减负运算，将数据还原为正。
3 取字符串后面的数值
如下图所示，需要获取字符串右侧的数值。
观察目标数据规律如下：
1）出现在字符串的右侧
2）字符长度不一致
公式如下：
=-LOOKUP(0,-RIGHT(A2,ROW(15)))
类似上一题。
4 取字符串最大的数值
如下图所示，需要获取A列数据中的最大值
观察数据规律：
1）数值都是整数，最大不超过2位数。
最大值数组公式如下：
=MAX(IF(ISNUMBER(FIND(ROW(99),A2)),ROW(
99)))
公式使用FIND函数判断1-99的序列号是否在A2单元格中存在，如果存在则返回1~99的序列号，最后使用MAX函数从中获取最大值。
但最小值并不能使用以下数组公式：
=MIN(IF(ISNUMBER(FIND(ROW(99),A2)),ROW(
99)))
如果数值并非都是低长度的整数，又或者可能包含小数点呢？
最大值数组公式如下：
=MAX(IFERROR(--MID(A2,ROW(99),COLUMN(A:O)),""))
--MID(A2,ROW(99),COLUMN(A:O))部分，从A2单元格源字符串的第1、2、3……99字符的位置，分别提取1、2、3……直至15位长度的字符，使用减负运算，将文本数值转换为数值，纯文本转换为错误值。
然后使用IFERROR函数将错误值转换为假空，最后使用MAX函数从中获取最大值。
但最小值并不能使用以下数组公式：
=MIN(IFERROR(--MID(A2,ROW(99),COLUMN(A:O)),""))
5 混合文本取数值
如下图所示，需要按顺序从A列数据中获取首个电话号码和末个电话号码
观察数据规律如下：
1）位置不固定
2）长度均为7位
获取首个电话号码，数组公式：
=VLOOKUP(0,MID(A2,ROW(99),7){0,1},2,0)*
获取末尾电话号码，数组公式：
=VLOOKUP(0,MID(A2,ROW(99),7){0,1},2)*
和上一条VLOOKUP函数相比，VLOOKUP的匹配机制使用了模糊匹配。
当然也可以使用LOOKUP函数：
=-LOOKUP(0,-MID(A2,ROW(15),7))
关于LOOKUP和VLOOKUP返回结果的区别，也就是VLOOKUP返回首个匹配结果，VLOOKUP返回最后的匹配结果，
除了使用VLOOKUP函数和LOOKUP函数，也可以使用定位法：
=MID(A2,ROW(
50),7)),ROW(
50)),COLUMN(A1)),7)
IF(ISNUMBER(--MID(1:
1:$50)部分，MID函数从1、2、3……50的位置，分别提取7个字符，使用减负运算搭配ISNUMBER函数判断是否为数值，如果为数值，则返回序列号。
然后使用SMALL函数依次从小到大，也就是从前向后，获取位置序号，并使用MID函数提取结果。
6 取字符串中身份证号
如下图所示，需要获取A列中的身份证号。
观察数据规律如下：
1）身份证号是字符串中唯一的数值
2）身份证长度为18位
公式如下：
=MID(A2,MIN(FIND(ROW(10)-1,A2&1/17)),18)
MIN(FIND(ROW(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(99),18)*0,0),18)
MID(A2,ROW(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(199),15),),2,15),"0;-0;0"),""))
SUBSTITUTE(A2,"个",REPT(" ",99))，将单位个替换为99个空格。
MID(SUBSTITUTE(A2,"个",REPT(" ",50)),ROW(199),15)，从1~199的位置，分别提取15位长度的字符。
TEXT(MID(SUBSTITUTE("a"&A2,"个",REPT(" ",50)),ROW(199),15),)，如果MID函数计算结果为数值，则屏蔽为假空。
MID(TEXT(MID(SUBSTITUTE("a"&A2,"个",REPT(" ",50)),ROW(199),15),),2,15)，从第2个位置，再分别提取15位长度的字符。为什么从第2个位置？数值的左侧必然为文本，从第2位取结果为数值的方才为完整的数值部分。
TEXT(MID(TEXT(MID(SUBSTITUTE("a"&A2,"个",REPT(" ",50)),ROW(199),15),),2,15),"0;-0;")，再使用一个TEXT函数屏蔽掉零值和文本。
最后使用IFERROR函数屏蔽掉错误值，使用MIN函数获取最小值，即为结果。
两个TEXT函数的应用是这个函数套路的精髓……
提取第N大的数值，比如第2大的数值：
=LARGE(IFERROR(--TEXT(MID(TEXT(MID(SUBSTITUTE(A2,"个",REPT(" ",50)),ROW(199),15),),2,15),"0;-0;0"),""),2)
8 获取字符串中所有字符的和
如下图所示，需要获取A列数据内所有数值的和。
9 获取字符串中所有的数值
B2数组公式：
=TRIM(TEXTJOIN(,TRUE,IFERROR(--MID(A2,ROW(99),1)," ")))
网友评论