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

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

引言:在《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(excelperfect),作者:fanjy

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

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

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

我来说两句

0 条评论
登录 后参与评论

相关文章

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

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

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

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

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

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

    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
  • 【Excel】用公式提取Excel单元格中的汉字

    数据科学社区
  • 一文学会用Python操作Excel+Word+CSV

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

    Python小二
  • 技巧:Excel用得好,天天没烦恼

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

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

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

    小F
  • Python数据分析之Pandas读写外部数据文件

    数据分析、数据挖掘、可视化是Python的众多强项之一,但无论是这几项中的哪一项都必须以数据作为基础,数据通常都存储在外部文件中,例如txt、csv、excel...

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

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

    Excel催化剂
  • Excel基础

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

    张果
  • EXCEL VBA语句集300

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

    Tony老师
  • 数据分析基础——EXCEL快速上手秘籍

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

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

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

    木东居士
  • Excel公式练习78: 判断并标识不连续的编码(续)

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

    fanjy
  • 个人永久性免费-Excel催化剂功能第43波-文本处理类函数增强

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

    Excel催化剂

扫码关注云+社区

领取腾讯云代金券