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

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

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

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

图1

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

如何使用公式获得结果?

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

解决方案

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

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

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

=MID(A2,MATCH(1,MMULT(-ISERR(-MID(A2,ROW(OFFSET(A$1,,,LEN(A2))),{1,2})),{1;-1}),)+1,6^6)

公式解析

1.分解字符串成两列:

MID(A2,ROW(OFFSET(A$1,,,LEN(A2))),{1,2})

解析为:

{"M","Mo";"o","on";"n","na";"a","ac";"c","co";"o","o7";"7","71";"1","19";"9","90";"0","0A";"A","Au";"u","us";"s","st";"t","tr";"r","ra";"a","al";"l","li";"i","ia";"a","a1";"1","14";"4","48";"8","84";"4","4"}

将原始的一维字符串在{1,2}的帮助下将其拆分为一个二维(即两列)数组。新数组的两列几乎相同,只是其中一列中的所有元素都是一个字符长,而另一列中的所有元素都是两个字符长。

如果在工作表单元格区域中输入,则如下图2所示。

图2

2.转换数组中的数字文本

目前Excel认为这个数组只是文本,包括其中的数字。在数组前面放一个减号——或者实际上对它进行任何类型的数学运算——将迫使任何文本形式的数转换成数字,而文本则会显示#VALUE!错误。

-{"M","Mo";"o","on";"n","na";"a","ac";"c","co";"o","o7";"7","71";"1","19";"9","90";"0","0A";"A","Au";"u","us";"s","st";"t","tr";"r","ra";"a","al";"l","li";"i","ia";"a","a1";"1","14";"4","48";"8","84";"4","4"}

转换为:

{#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;-7,-71;-1,-19;-9,-90;0,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;-1,-14;-4,-48;-8,-84;-4,-4}

同样,输入到Excel中如下图3所示。

图3

3.将数组值转换为TRUE/FALSE

ISERR({#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;-7,-71;-1,-19;-9,-90;0,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;-1,-14;-4,-48;-8,-84;-4,-4})

返回:

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

这样,文本表示为TRUE,数字表示为FALSE,如下图4所示。

图4

4.将布尔值转换为1/0

在数组前面加上一个减号,将里面的布尔值转换为1/0。

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

解析为:

{-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;0,0;0,0;0,0;0,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;0,0;0,0;0,0;0,0}

用工作表单元格表示如下图5所示。

图5

有趣的事情来了!上图5中突出显示的行正好位于我们想求的字符串之前,它是整个数组中唯一第一列为零,第二列为负的行。因此,如果计算出0 和-1的组合落在哪里,我们就确切地知道我们想要的子串从哪里开始。

5.使用MMULT

使用MMULT函数,给其第二个参数设置为{1;-1},将第一个数组列乘以1,将第二个数组列乘以-1。

MMULT({-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;0,0;0,0;0,0;0,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;0,0;0,0;0,0;0,0},{1;-1})

返回:

{0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0}

在工作表中表示如下图6所示。

图6

由于我们在这个示例中的数组有两列,提供给MMULT的第二个参数是{1;-1},在这种情况下,MMULT有效地将第一列中的每个数字乘以正数,将第二列中的每个数乘以负数,然后将结果相加。将第一个数组乘以正数对它没有任何作用,将第二个数组乘以负数会将任何现有的负值更改为正值,将结果加在一起使成为一个一维数组。

现在,根据上面突出显示的行,我们要查找的字符串之前的数字是唯一包含1的行。

6.查找

MATCH({1,{0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0},)+1

返回:

11

7.获取最终结果

MID(A2,11,6^6)

得到:

Australia1484

那个6^6只是使用一个长数字的一种简短方法。我们需要该参数中的数字足够大,以便涵盖所有可能的子字符串长度。使用LEN(A1),公式中需要7个字符,而6^6只要三个字符。Excel2007或更高版本允许放入字符串的最大字符数为32767,因此考虑到6^6=46,656,这将绰绰有余。

这个公式相比于前面的3个公式,是不是更简单了。

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

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

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

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

我来说两句

0 条评论
登录 后参与评论

相关文章

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

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

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

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

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

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

    fanjy
  • 技巧:Excel用得好,天天没烦恼

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

    华章科技
  • 数据分析基础——EXCEL快速上手秘籍

    这篇文章是本系列的第一篇,选择性汇总了EXCEL的常用且重点的模块和公式,用作内部员工EXCEL基础操作培训,以帮助表格基础薄弱的同事快速熟悉常用操作,提升工作...

    数据森麟
  • 数据分析基础——EXCEL快速上手秘籍

    这篇文章是本系列的第一篇,选择性汇总了EXCEL的常用且重点的模块和公式,用作内部员工EXCEL基础操作培训,以帮助表格基础薄弱的同事快速熟悉常用操作,提升工作...

    木东居士
  • Excel公式练习92:统计两个字符串共有的唯一字符数

    引言:本文的练习整理自chandoo.org。多一些练习,想想自己怎么解决问题,看看别人又是怎解决的,能够快速提高Excel公式编写水平。

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

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

    fanjy
  • 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
  • 一文学会用Python操作Excel+Word+CSV

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

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

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

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

    数据科学社区
  • 个人永久性免费-Excel催化剂功能第39波-DotNet版的正则处理函数

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

    Excel催化剂
  • Excel基础

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

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

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

    fanjy
  • 拒绝加班!工作中必会的15个excel函数

    有人会说,现在网上excel技巧太多,一眼看过去感觉各个都好牛逼,恨不得全部收藏起来。可是,能真正能用到的时候并不多,因为学习的知识都太散了,也不能及时进行总结...

    企鹅号小编
  • 7道题,测测你的职场技能

    当我们鼠标单击“显示值”列的任一单元格,在编辑栏里,我们可以看到其“内核”其实是和输入值一致的。

    猴子聊数据分析

扫码关注云+社区

领取腾讯云代金券