Excel公式练习89:返回字符串中第一块数字之后的所有内容(续2)

excelperfect

引言:在《Excel公式练习87:返回字符串中第一块数字之后的所有内容》和《Excel公式练习88:返回字符串中第一块数字之后的所有内容(续1)》中,我们分别给出了解决这个问题的两个公式,本文中,再次尝试着使用另一个公式来解决这个问题。正如之前已提到过的,尝试多种方法解决问题,能够帮助我们快速提高。

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

图1

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

如何使用公式获得结果?

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

解决方案

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

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

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

=MID(A2,MIN(IFERROR(SEARCH(CHAR(ROW($65:$90)),A2,MIN(IFERROR(FIND(ROW($1:$10)-1,A2),''))+1),'')),LEN(A2))

公式解析

1.找出字符串中每个数字出现的位置:

FIND(ROW($1:$10)-1,A2)

解析为:

FIND({1;2;3;4;5;6;7;8;9;10}-1,A2)

解析为:

FIND({0;1;2;3;4;5;6;7;8;9},A2)

得到:

{10;8;#VALUE!;#VALUE!;21;#VALUE!;#VALUE!;7;22;9}

2.删除错误

IFERROR({10;8;#VALUE!;#VALUE!;21;#VALUE!;#VALUE!;7;22;9},””)

返回:

{10;8;'';'';21;'';'';7;22;9}

3.这样我们就可以计算出这些数字中的哪一个首先出现在字符串中:

MIN({10;8;'';'';21;'';'';7;22;9})

返回:

8

因此,已经计算出第一组数字的开始位置是第8个字符,这意味着可以完全忽略出现在这个位置之前的任何文本。

4.获取第二块的开始

现在,可以从位置8搜索下一个字母。那个字母将是我们所求文本块的开始。

SEARCH(CHAR(ROW($65:$90)),A2,8)

解析为:

SEARCH({“A”;”B”;”C”;”D”;”E”;”F”;”G”;”H”;”I”;”J”;”K”;”L”;”M”;”N”;”O”;”P”;”Q”;”R”;”S”;”T”;”U”;”V”;”W”;”X”;”Y”;”Z”},A2,8)

返回:

{11;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;18;#VALUE!;#VALUE!;17;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;15;13;14;12;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

5.使用IFERROR消除错误值

IFERROR({11;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;18;#VALUE!;#VALUE!;17;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;15;13;14;12;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!},””)

返回:

{11;'';'';'';'';'';'';'';18;'';'';17;'';'';'';'';'';15;13;14;12;'';'';'';'';''}

6.计算出这些字母哪个首先出现在字符串中

MIN({11;'';'';'';'';'';'';'';18;'';'';17;'';'';'';'';'';15;13;14;12;'';'';'';'';''})

返回:

11

7.获取最终结果

MID(A2,11,LEN(A2))

得到:

Australia1484

更简单了!

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

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

(0)

相关推荐