专栏首页完美ExcelExcel公式练习88:返回字符串中第一块数字之后的所有内容(续1)

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

引言:在《Excel公式练习87:返回字符串中第一块数字之后的所有内容》中,我们给出了解决这个问题的一个公式,本文中,尝试着使用另一个公式来解决这个问题。尝试多种方法解决问题,能够帮助我们快速提高。

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

图1

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

如何使用公式获得结果?

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

解决方案

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

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

在单元格B2中输入公式:

=MID(A2,MODE(MMULT((N(ISNUMBER(-MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)))={1,0})*(ROW(INDIRECT("1:"&LEN(A2)))-{0,1}),{1;1}))+1,LEN(A2))

公式解析

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

MID(A2,ROW(INDIRECT("1:"&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.使数组元素变成负值:

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

强迫以文本存储的数字变成数字,而文本值则会出错,这样返回:

{#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;-7;-1;-9;0;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;-1;-4;-8;-4}

3.ISNUMBER函数判断数组中的数字

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

ISNUMBER({#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;-7;-1;-9;0;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;-1;-4;-8;-4})

返回:

{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和0,并检查它是否等于1或0

N({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE})={1,0}

返回:

{0;0;0;0;0;0;1;1;1;1;0;0;0;0;0;0;0;0;0;1;1;1;1}={1,0}

返回:

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

这实际上所做的是创建一个二维数组,该数组的一列与另一列相反。因此,一列记录是否是数字,另一列记录是否不是数字。如果我们看到在Excel中的输入会是什么样子,也许会有助于理解,将我们的原始字符串放在一边,这样我们就可以尝试找出正在做什么,如下图2所示。

图2

5.接着创建第二个二维数组

(ROW(INDIRECT("1:"&LEN(A2)))-{0,1})

返回:

{1,0;2,1;3,2;4,3;5,4;6,5;7,6;8,7;9,8;10,9;11,10;12,11;13,12;14,13;15,14;16,15;17,16;18,17;19,18;20,19;21,20;22,21;23,22}

如果在Excel中表示如下图3。

图3

6.然后将两个数组相乘

(N(ISNUMBER(-MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)))={1,0})*(ROW(INDIRECT("1:"&LEN(A2)))-{0,1})

返回:

{FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;TRUE,FALSE;TRUE,FALSE;TRUE,FALSE;TRUE,FALSE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;TRUE,FALSE;TRUE,FALSE;TRUE,FALSE;TRUE,FALSE}*{1,0;2,1;3,2;4,3;5,4;6,5;7,6;8,7;9,8;10,9;11,10;12,11;13,12;14,13;15,14;16,15;17,16;18,17;19,18;20,19;21,20;22,21;23,22}

返回:

{0,0;0,1;0,2;0,3;0,4;0,5;7,0;8,0;9,0;10,0;0,10;0,11;0,12;0,13;0,14;0,15;0,16;0,17;0,18;20,0;21,0;22,0;23,0}

如果在Excel区域内输入,则看起来像下图4所示。

图4

终于搞清楚了,第一个数组与第二个数组中唯一一个相同数字交界的地方就是第一个数字块结束和第二个字母块开始的地方。

7.数组相乘

使用MMULT函数将二维数组转换成一维数组:

MMULT({0,0;0,1;0,2;0,3;0,4;0,5;7,0;8,0;9,0;10,0;0,10;0,11;0,12;0,13;0,14;0,15;0,16;0,17;0,18;20,0;21,0;22,0;23,0},{1;1})

返回:

{0;1;2;3;4;5;7;8;9;10;10;11;12;13;14;15;16;17;18;20;21;22;23}

8.找出该数组中出现次数最多的数字并将其加上1

MODE({0;1;2;3;4;5;7;8;9;10;10;11;12;13;14;15;16;17;18;20;21;22;23})+1

返回:

11

9.获取最终结果

MID(A2,11,LEN(A2))

得到:

Australia1484

本文分享自微信公众号 - 完美Excel(excelperfect),作者:fanjy

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2021-08-17

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

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

    引言:在《Excel公式练习87:返回字符串中第一块数字之后的所有内容》和《Excel公式练习88:返回字符串中第一块数字之后的所有内容(续1)》中,我们分别给...

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

    引言:在《Excel公式练习87:返回字符串中第一块数字之后的所有内容》、《Excel公式练习88:返回字符串中第一块数字之后的所有内容(续1)》和《Excel...

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

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

    fanjy
  • 淘宝商品信息定向爬虫

    最近在学习数据分析的时候,提前学习了爬虫,其实在很早之前就学习过Python了,但是后来学习Android了,没有将Python很好的实践。但是学习了爬虫之后,...

    GitOPEN
  • 简单的Excel VBA编程问题解答——完美Excel第183周小结

    下面是2020年11月12日发布的一些简单的ExcelVBA问题的答案,是不是和你想的一样。

    fanjy
  • 用Python自动化管理邮件简直太方便了,三个实用小例子带你体会!

    在之前的文章中我们已经了解如何对自己的邮箱做一些代码操作前的基础配置,以及通过 Python 代码收发、读取邮件。本文将简单复习之前的部分重点内容,并通过 3 ...

    数据森麟
  • Python自动化办公之Excel报表自动化指南!全文3W字

    来源:https://blog.csdn.net/u014779536/article/details/108182833

    统计学家
  • Python办公自动化之Excel做表自动化:全网最全,看这一篇就够了!

    官方文档:https://xlrd.readthedocs.io/en/latest/

    Python小二
  • python excel交互模块比较

    官方文档:https://xlrd.readthedocs.io/en/latest/

    用户2090661
  • 【Excel】用公式提取Excel单元格中的汉字

    数据科学社区
  • 技巧:Excel用得好,天天没烦恼

    分析公司DarkHorse Analytics 从美国劳工统计处获得数据,并制作了这张二十四小时会唿吸的地图,显示曼哈顿的工作与在宅人口。

    华章科技
  • 一文学会用Python操作Excel+Word+CSV

    数据处理是 Python 的一大应用场景,而 Excel 又是当前最流行的数据处理软件。因此用 Python 进行数据处理时,很容易会和 Excel 打起交道。...

    Python小二
  • 教你如何用Python轻轻松松操作Excel、Word、CSV,一文就够了,赶紧码住!!!

    数据处理是 Python 的一大应用场景,而 Excel 又是当前最流行的数据处理软件。因此用 Python 进行数据处理时,很容易会和 Excel 打起交道。...

    小F
  • 个人永久性免费-Excel催化剂功能第39波-DotNet版的正则处理函数

    原文在简书上发表,再同步到Excel催化剂微信公众号或其他平台上,文章后续有修改和更新将在简书上操作, 其他平台不作同步修改更新,因此建议阅读其他出处的文章时,...

    Excel催化剂
  • Excel基础

    一、基础 一个Excel文档称为工作簿(workbook)、一个工作簿中可以包含多个工作表(sheet) ? ctrl+向右箭头  查看最后一列 ctrl+向...

    张果
  • 教你搭建微信公众号自动答复机器人

    昨天我们的文章中说到:使用图灵机器人作为应答机器人可以满足要求,但是每天的回复条数在不花钱的情况下只能有100条。对于我这样贫困线人口怎么可能每个月花费...

    菜鸟小白的学习分享
  • [python交互]Excel催化剂与python交互原理剖析,py开发者按此规范可自行扩展功能

    以下的文字版内容,可能在后续的代码变动下会略有更改,大体上不变,要获取最新的信息,可私信笔者,加入Excel催化剂组建的python开发者社群,一起深入交流。

    Excel催化剂
  • EXCEL VBA语句集300

            定制模块行为 (1) Option Explicit ‘强制对模块内所有变量进行声明 Option Private Module ‘标记...

    Tony老师
  • Excel公式练习78: 判断并标识不连续的编码(续)

    本次的练习是:与《Excel公式练习77:判断并标识不连续的编码》相似,编码都是4个字符,由1个字母接着3个数字组成,但增加了字母没有按顺序的情形,如下图1所示...

    fanjy

扫码关注云+社区

领取腾讯云代金券