前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Excel公式练习87:返回字符串中第一块数字之后的所有内容

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

作者头像
fanjy
发布2021-08-31 17:38:08
2.5K0
发布2021-08-31 17:38:08
举报
文章被收录于专栏:完美Excel

本次的练习是:如下图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

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2021-08-16,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 完美Excel 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档