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.重复步骤1到3,但这次检查字母的出现
稍作更改,通过将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社群,进行技术交流和提问,获取更多电子资料。