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

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

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

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

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

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

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

我来说两句

0 条评论
登录 后参与评论

相关文章

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

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

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

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

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

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

    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 VBA编程问题解答——完美Excel第183周小结

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

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

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

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

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

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

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

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

    Excel催化剂
  • 一文带你快速入门Python | 初识Pandas

    这是Python数据分析实战基础的第一篇内容,主要是和Pandas来个简单的邂逅。已经熟练掌握Pandas的同学,可以加快手速滑动浏览或者直接略过本文。

    CDA数据分析师
  • 教你搭建微信公众号自动答复机器人

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

    菜鸟小白的学习分享
  • EXCEL VBA语句集300

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

    Tony老师
  • Python自动化办公都需要掌握什么知识?

    这可能是很多非IT职场人士面临的困惑,想把python用到工作中,却不知如何下手?python在自动化办公领域越来越受欢迎,批量处理简直是加班族的福音。

    张俊红
  • Python自动化办公知识点整理汇总

    这可能是很多非IT职场人士面临的困惑,想把python用到工作中,却不知如何下手?python在自动化办公领域越来越受欢迎,批量处理简直是加班族的福音。

    墨鬓
  • Excel基础

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

    张果
  • python 如何改变字符串中某一个值_python替换字符串中的某个字符

    使用python时会经常要对字符串做一些处理,比如:分割字符串、去掉空格、替换字符串

    用户7886150
  • 数据分析基础——EXCEL快速上手秘籍

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

    数据森麟

扫码关注云+社区

领取腾讯云代金券