Excel公式练习90：返回字符串中第一块数字之后的所有内容（续3）
excelperfect
引言：在《Excel公式练习87：返回字符串中第一块数字之后的所有内容》、《Excel公式练习88：返回字符串中第一块数字之后的所有内容（续1）》和《Excel公式练习89：返回字符串中第一块数字之后的所有内容（续2）》中，我们分别给出了解决这个问题的三个公式，本文中，再次尝试着使用另一个公式来解决这个问题。正如之前已提到过的，尝试多种方法解决问题，能够帮助我们快速提高。
本次的练习是：如下图1所示，使用公式拆分列A中的字符串，从中返回列B中的字符串。例如，如果字符串是Monaco7190Australia1484，那么返回第一块数字右侧的所有字符串Australia1484。
图1
你的公式应该处理任意长度的字符串和任意长度的数字——不仅仅是图1中所显示的长度。此外，不应该使用任何辅助单元格、中间公式或命名区域，或者VBA。
如何使用公式获得结果？
（注：本文来自于chandoo.org中的公式挑战栏目，供有兴趣的朋友尝试和学习。）
解决方案
如上图1所示，需要返回的字符是蓝色粗体部分，即第一个文本和数字块之后的文本和数字块，对于“Monaco7190Australia1484”返回的是“Australia1484”。
问题的难点在于有一个文本块，然后是一个数字块，接着是我们实际想要提取的文本/数字块。因此，由于前面有一个文本/数字块，很难确定第二个文本/数字块的位置。
在单元格B2中输入数组公式：
=MID(A2,MATCH(1,MMULT(-ISERR(-MID(A2,ROW(OFFSET(A$1,,,LEN(A2))),{1,2})),{1;-1}),)+1,6^6)
公式解析
1.分解字符串成两列：
MID(A2,ROW(OFFSET(A$1,,,LEN(A2))),{1,2})
解析为：
{'M','Mo';'o','on';'n','na';'a','ac';'c','co';'o','o7';'7','71';'1','19';'9','90';'0','0A';'A','Au';'u','us';'s','st';'t','tr';'r','ra';'a','al';'l','li';'i','ia';'a','a1';'1','14';'4','48';'8','84';'4','4'}
将原始的一维字符串在{1,2}的帮助下将其拆分为一个二维（即两列）数组。新数组的两列几乎相同，只是其中一列中的所有元素都是一个字符长，而另一列中的所有元素都是两个字符长。
如果在工作表单元格区域中输入，则如下图2所示。
图2
2.转换数组中的数字文本
目前Excel认为这个数组只是文本，包括其中的数字。在数组前面放一个减号——或者实际上对它进行任何类型的数学运算——将迫使任何文本形式的数转换成数字，而文本则会显示#VALUE!错误。
-{'M','Mo';'o','on';'n','na';'a','ac';'c','co';'o','o7';'7','71';'1','19';'9','90';'0','0A';'A','Au';'u','us';'s','st';'t','tr';'r','ra';'a','al';'l','li';'i','ia';'a','a1';'1','14';'4','48';'8','84';'4','4'}
转换为：
{#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;-7,-71;-1,-19;-9,-90;0,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;-1,-14;-4,-48;-8,-84;-4,-4}
同样，输入到Excel中如下图3所示。
图3
3.将数组值转换为TRUE/FALSE
ISERR({#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;-7,-71;-1,-19;-9,-90;0,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;-1,-14;-4,-48;-8,-84;-4,-4})
返回：
{TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE}
这样，文本表示为TRUE，数字表示为FALSE，如下图4所示。
图4
4.将布尔值转换为1/0
在数组前面加上一个减号，将里面的布尔值转换为1/0。
-{TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE}
解析为：
{-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;0,0;0,0;0,0;0,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;0,0;0,0;0,0;0,0}
用工作表单元格表示如下图5所示。
图5
有趣的事情来了！上图5中突出显示的行正好位于我们想求的字符串之前，它是整个数组中唯一第一列为零，第二列为负的行。因此，如果计算出0 和-1的组合落在哪里，我们就确切地知道我们想要的子串从哪里开始。
5.使用MMULT
使用MMULT函数，给其第二个参数设置为{1;-1}，将第一个数组列乘以1，将第二个数组列乘以-1。
MMULT({-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;0,0;0,0;0,0;0,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;0,0;0,0;0,0;0,0},{1;-1})
返回：
{0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0}
在工作表中表示如下图6所示。
图6
由于我们在这个示例中的数组有两列，提供给MMULT的第二个参数是{1;-1}，在这种情况下，MMULT有效地将第一列中的每个数字乘以正数，将第二列中的每个数乘以负数，然后将结果相加。将第一个数组乘以正数对它没有任何作用，将第二个数组乘以负数会将任何现有的负值更改为正值，将结果加在一起使成为一个一维数组。
现在，根据上面突出显示的行，我们要查找的字符串之前的数字是唯一包含1的行。
6.查找
MATCH({1,{0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0},)+1
返回：
11
7.获取最终结果
MID(A2,11,6^6)
得到：
Australia1484
那个6^6只是使用一个长数字的一种简短方法。我们需要该参数中的数字足够大，以便涵盖所有可能的子字符串长度。使用LEN(A1)，公式中需要7个字符，而6^6只要三个字符。Excel2007或更高版本允许放入字符串的最大字符数为32767，因此考虑到6^6=46,656，这将绰绰有余。
这个公式相比于前面的3个公式，是不是更简单了。
欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料。