Excel公式练习87：返回字符串中第一块数字之后的所有内容

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

图1

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

如何使用公式获得结果？

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

解决方案

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

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

在单元格B2中输入数组公式：

=MID(A2,1+MATCH(1,(CODE(MID(A2,ROW(A1:INDEX(A:A,LEN(A2))),1))<58)*(CODE(MID(A2&"a",ROW(A

公式解析

这个方法是试图找到一个数字紧跟一个字母的位置点。例如，对于“Monaco7190Australia1484”，要找到数字0和字母A之间的位置。

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

MID(A2,ROW(A$1:INDEX(A:A,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.使用CODE函数将单个字符转换Excel用来表示每个数字/字母的特定数字：

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

返回：

{77;111;110;97;99;111;55;49;57;48;65;117;115;116;114;97;108;105;97;49;52;56;52}

3.找出哪些代码代表数字

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

{77;111;110;97;99;111;55;49;57;48;65;117;115;116;114;97;108;105;97;49;52;56;52}<58

返回：

{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到3，但这次检查字母的出现

稍作更改，通过将ROW(A1...更改为ROW(A2...，以便结果数组偏移一个位置：

CODE(MID(A2&"a",ROW(A$2:INDEX(A:A,LEN(A2)+1)),1))>57

返回：

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

5.将得到的两个数组相乘

这会将这些TRUE和FALSE值转换为数值1和0。

此外，因为第二个数组偏移一位，这给了我字母和数字重合的位置。（鉴于第二个数组已被偏移，这等效于识别数字后紧跟着出现字母）

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

返回：

{0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;1}

6.然后需要做的就是确定第一个1在这个数组中的位置

MATCH(1,{0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;1},0)

返回：

10

7.接着返回该位置右侧原始字符串中的所有字符

=MID(A2,1+10,LEN(A2))

返回：

Australia1484