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

excelperfect

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

图1

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

如何使用公式获得结果?

(注:本文来自于chandoo.org中的公式挑战栏目,供有兴趣的朋友尝试和学习。)

解决方案

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

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

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

=MID(A2,1+MATCH(1,(CODE(MID(A2,ROW(A$1:INDEX(A:A,LEN(A2))),1))<58)*(CODE(MID(A2&'a',ROW(A$2:INDEX(A:A,LEN(A2)+1)),1))>57),0),LEN(A2))

公式解析

这个方法是试图找到一个数字紧跟一个字母的位置点。例如,对于“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.重复步骤13,但这次检查字母的出现

稍作更改,通过将ROW(A$1...更改为ROW(A$2...,以便结果数组偏移一个位置:

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

完美Excel

Excel与VBA及相关技术的个人分享平台
1541篇原创内容
公众号

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

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

(0)

相关推荐