从分隔符连接的字符串中提取子字符串

有时候,在工作表单元格中有一些以某分隔符连接的字符串,如图1中的单元格A1,其内容是以逗号连接城市名。

图1

如果我们想要提取其中的某个城市,例如第8个子字符串表示的城市名,则可以使用下面的公式:

=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",999)),8*999-998,999))

在公式中,先使用SUBSTITUTE函数以999个空格来替换字符串中的每个分隔符,然后使用MID函数提取所需要的子字符串(前后都含有空格),最后使用TRIM函数去掉字符串首尾的空格获得最终的结果。由于每个子字符串远小于999,因此不用担心位置参数不合适而得到错误结果。

如果想要提取字符串中的每个子字符串并将它们分别放置在不同的单元格中,则可以使用公式:

=TRIM(MID(SUBSTITUTE($A$1,",",REPT("",999)),COLUMN(A1)*999-998,999))

结果如下图2所示。

图2

在图2所示的工作表中,在单元格B3中输入上面的公式,向右拖至单元格K3。

在公式中,COLUMN(A1)的值为1,向右拖动1列,将变为COLUMN(B1),值为2,依此类推,从而顺序提取子字符串的值。

好好讲道理:反击谬误的逻辑学训练(Attacking Faulty Reasoning)(美国30余所大学通用的逻辑学教材)(如果你只打算买一本关于批判性思维的书,本书是不二选择!)

作者:[美]T.爱德华戴默 著,黄琳,刀尔登 译

当当

一般公式

从上面的演示中,我们可以得到实现此种情况的一般公式为:

=TRIM(MID(SUBSTITUTE(字符串所在单元格,分隔符,REPT("",999)),子字符串位置*999-998,999))

公式中的数字999可以随字符串的长度而定,只是要取得足够大即可。

更进一步

如果要从字符串的结尾开始提取指定位置的子字符串呢?如图3所示,要提取单元格A1中倒数第3个子字符串。

图3

我们已经给出的公式为:

=TRIM(MID(SUBSTITUTE(A1,",",REPT("",999)),(2-3+(LEN(A1)-LEN(SUBSTITUTE(A1,",","")))/LEN(","))*999-998,999))

一般的公式为:

=TRIM(MID(SUBSTITUTE(字符串所在单元格,分隔符,REPT("",999)),(2-子字符串位置+(LEN(字符串所在单元格)-LEN(SUBSTITUTE(字符串所在单元格,分隔符,"")))/LEN(分隔符))*999-998,999))

上例中,可以使用一个更好的公式得到同样的结果:

=TRIM(LEFT(RIGHT(SUBSTITUTE(","&A1,",",REPT("",999)),3*999),999))

这也可以避免子字符串位置超过了字符串实际位置时出现的错误。

一般的公式为:

=TRIM(LEFT(RIGHT(SUBSTITUTE(分隔符&字符串所在单元格,分隔符,REPT(" ",999)),子字符串位置*999),999))

公式中的999是取的一个较大的数字,这个数字至少应大于字符串所在单元格中的字符数。

(0)

相关推荐

  • 四大经典案例带你玩转Excel必会函数之Substitute函数

    各位早,我是小雅! 先通知一件事情:今晚8点滴答老师的Excel免费大型公开课,欢迎大家来听课交流. 关于今晚上课教室以及今天教程配套的Excel练习文件,请扫下面二维码加入微信群领取. 温馨提醒:已 ...

  • 如何提取出最后一个斜杠(\)之前的内容?

    你好,我是刘卓.欢迎来到我的公号,excel函数解析.今天要分享的内容是如何提取出最后一个斜杠之前的内容,这类型的题很多小伙伴也会经常遇到. 下图A列是一些包含路径的文件名,现在要提取出最后一个斜杠前 ...

  • 35个Excel常用技巧汇总,拿来即用,效率翻倍!(上篇)

    很多小伙伴说老板经常搞些骚操作,还要求几分钟内解决!所以精通Excel刻不容缓! 今天,小可给大家整理了35个Excel实用技巧,工作中每天都要使用到- 如果全部掌握,分分钟碾压90%同事,再也不怕老 ...

  • 最后边那位同学,来蹭课的吧?

    周庄 前言 通常编码或代码都是分段式结构,中间用符号隔开以区别不同的代码.如: YRO-2000-H188 H000.MP.E.B 当我们需要将最后一段代码取出时,往往因为段数不统一,字符数不统一等原 ...

  • 如何批量的去计算单元格当中的人员总数?

    如何批量的去计算单元格当中的人员总数?

  • 用rept函数拆分数据就是好用

    观察一下可以发现原数据的特点,都是以相同的分隔符(~)连接的.这让我想到了以前的拆分方式,以~为分隔符进行拆分,提取拆分后的每个值,就和分列的效果一样.效果图如下: 它的拆分公式为=TRIM(MID( ...

  • 从含有数字的文本字符串中提取出数字

    我的工作表中有许多含有数字的单元格,我想将数字单独提取出来.如下图1所示,将列A的单元格中的数字提取出来放置在列B中,应该如何编写公式呢? 图1 可以使用数组公式: =1*MID(A1,MATCH(T ...

  • Excel字符串中提取数字,不需要大神级公式

    小勤:大海,救命!有个二货给的表里面有一列中英文.数字.标点符号全混在一起的,我要把数字提出来! 大海:呵呵.网上不是有很多大神写过很牛B的公式吗? 小勤:我找过了,找到一个,完全看不懂!而且试了一下 ...

  • 从字符串中提取多个数字

    大家好,我经常看到有些同学会问一些从字符串中提取多个数字的问题,可能是ta数据录入不规范的原因,也可能是从系统导出的数据.不管什么原因,我认为这个问题也挺常见的,所以就学习和研究了好几天,今天就来和大 ...

  • python:解决从字符串中提取省市区的问题

    需求 从发货订单的地址中,如下的字符串: "浙江省温州市永嘉县岩头镇芙蓉新村15栋" "温州XX永嘉县岩头镇芙蓉新村15栋" "浙江X永嘉县岩头镇芙蓉 ...

  • 如何统计带分隔符的字符串中不重复的子字符串数?

    Q:某些情况下,我们可能要统计带有分隔符的字符串中不重复的子字符串数.如下图1所示,我想知道单元格A1中不重复的数字有几个,应该怎么编写公式? 图1 A:下面的数组公式可以完成单元格A1的字符串不重复 ...

  • 在Excel中提取单元格混合字符串中的数字与英文的函数介绍

    如何将某个单元格中的由英文与数字混合组成的字符串中的英文与中文分别提取到其它单元格? 首先说明,在Excel中没有满足这种功能的现成的函数,要想使用这种函数,就必须使用自定义的函数. 下面我们先看效果 ...

  • 提取字符串中的数字

    本次的练习是:单元格中的数据包含文本和数字(如图1),如何使用公式提取出该单元格中的数字? 图1 先不看答案,自已动手试一试. 公式思路 先找到字符串文本中第1个数字出现的位置,然后取出从该位置起的全 ...

  • 63个函数公式、提取字符串中任意位置的数值

    63个函数公式、提取字符串中任意位置的数值

  • 如何用公式提取字符串中的数字?!经典公式,收藏备用

    如何用公式提取字符串中的数字?!经典公式,收藏备用