Excel公式练习88：返回字符串中第一块数字之后的所有内容（续1） / 四六文摘

excelperfect

引言：在《Excel公式练习87：返回字符串中第一块数字之后的所有内容》中，我们给出了解决这个问题的一个公式，本文中，尝试着使用另一个公式来解决这个问题。尝试多种方法解决问题，能够帮助我们快速提高。

本次的练习是：如下图1所示，使用公式拆分列A中的字符串，从中返回列B中的字符串。例如，如果字符串是Monaco7190Australia1484，那么返回第一块数字右侧的所有字符串Australia1484。

图1

你的公式应该处理任意长度的字符串和任意长度的数字——不仅仅是图1中所显示的长度。此外，不应该使用任何辅助单元格、中间公式或命名区域，或者VBA。

如何使用公式获得结果？

（注：本文来自于chandoo.org中的公式挑战栏目，供有兴趣的朋友尝试和学习。）

解决方案

如上图1所示，需要返回的字符是蓝色粗体部分，即第一个文本和数字块之后的文本和数字块，对于“Monaco7190Australia1484”返回的是“Australia1484”。

问题的难点在于有一个文本块，然后是一个数字块，接着是我们实际想要提取的文本/数字块。因此，由于前面有一个文本/数字块，很难确定第二个文本/数字块的位置。

在单元格B2中输入公式：

=MID(A2,MODE(MMULT((N(ISNUMBER(-MID(A2,ROW(INDIRECT('1:'&LEN(A2))),1)))={1,0})*(ROW(INDIRECT('1:'&LEN(A2)))-{0,1}),{1;1}))+1,LEN(A2))

公式解析

1.动态地将字符串分成单个字符：

MID(A2,ROW(INDIRECT('1:'&LEN(A2))),1)

{'M';'o';'n';'a';'c';'o';'7';'1';'9';'0';'A';'u';'s';'t';'r';'a';'l';'i';'a';'1';'4';'8';'4'}

2.使数组元素变成负值：

-{'M';'o';'n';'a';'c';'o';'7';'1';'9';'0';'A';'u';'s';'t';'r';'a';'l';'i';'a';'1';'4';'8';'4'})

强迫以文本存储的数字变成数字，而文本值则会出错，这样返回：

{#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;-7;-1;-9;0;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;-1;-4;-8;-4}

3.ISNUMBER函数判断数组中的数字

所有数字都在字符代码58之前：

ISNUMBER({#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;-7;-1;-9;0;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;-1;-4;-8;-4})

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE}

4.将该数组转换为1和0，并检查它是否等于1或0

N({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE})={1,0}

{0;0;0;0;0;0;1;1;1;1;0;0;0;0;0;0;0;0;0;1;1;1;1}={1,0}

这实际上所做的是创建一个二维数组，该数组的一列与另一列相反。因此，一列记录是否是数字，另一列记录是否不是数字。如果我们看到在Excel中的输入会是什么样子，也许会有助于理解，将我们的原始字符串放在一边，这样我们就可以尝试找出正在做什么，如下图2所示。

图2

5.接着创建第二个二维数组

(ROW(INDIRECT('1:'&LEN(A2)))-{0,1})

{1,0;2,1;3,2;4,3;5,4;6,5;7,6;8,7;9,8;10,9;11,10;12,11;13,12;14,13;15,14;16,15;17,16;18,17;19,18;20,19;21,20;22,21;23,22}

如果在Excel中表示如下图3。

图3

6.然后将两个数组相乘

(N(ISNUMBER(-MID(A2,ROW(INDIRECT('1:'&LEN(A2))),1)))={1,0})*(ROW(INDIRECT('1:'&LEN(A2)))-{0,1})

{FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;TRUE,FALSE;TRUE,FALSE;TRUE,FALSE;TRUE,FALSE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;TRUE,FALSE;TRUE,FALSE;TRUE,FALSE;TRUE,FALSE}*{1,0;2,1;3,2;4,3;5,4;6,5;7,6;8,7;9,8;10,9;11,10;12,11;13,12;14,13;15,14;16,15;17,16;18,17;19,18;20,19;21,20;22,21;23,22}

{0,0;0,1;0,2;0,3;0,4;0,5;7,0;8,0;9,0;10,0;0,10;0,11;0,12;0,13;0,14;0,15;0,16;0,17;0,18;20,0;21,0;22,0;23,0}

如果在Excel区域内输入，则看起来像下图4所示。

图4

终于搞清楚了，第一个数组与第二个数组中唯一一个相同数字交界的地方就是第一个数字块结束和第二个字母块开始的地方。

7.数组相乘

使用MMULT函数将二维数组转换成一维数组：

MMULT({0,0;0,1;0,2;0,3;0,4;0,5;7,0;8,0;9,0;10,0;0,10;0,11;0,12;0,13;0,14;0,15;0,16;0,17;0,18;20,0;21,0;22,0;23,0},{1;1})

{0;1;2;3;4;5;7;8;9;10;10;11;12;13;14;15;16;17;18;20;21;22;23}

8.找出该数组中出现次数最多的数字并将其加上1

MODE({0;1;2;3;4;5;7;8;9;10;10;11;12;13;14;15;16;17;18;20;21;22;23})+1

9.获取最终结果

MID(A2,11,LEN(A2))

得到：

Australia1484

扩展

是不是该再研究一下矩阵了！

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。

欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料。

Excel公式练习88：返回字符串中第一块数字之后的所有内容（续1）

相关推荐