Substitute函数使用简介

在许多时候可能需要对某个文本字符串中的部分内容进行替换。我们除了可以使用Excel的“替换功能”外,还可以用函数来完成。常用的文本替换函数为substitute函数和replace函数。本篇我们讲解substitute函数,下一篇讲解replace函数。

substitute函数的基本语法结构是:

语法结构:substitute(text, old_text, new_text, instance_num)

Text:表示需要替换其中字符的文本,或对含有文本的单元格的引用 

Old_text:表示需要替换的就文本

New_text:用于替换Old_text的文本

Instance_num:可选。用来指定要替换第几个出现的Old_text

substitute函数用于在文本字符串中用new_text来代替old_text。让我们用几个例子来帮助我们理解这个函数的用法吧。

例一:去除空格

在下面这个例子中,我们要将“我 爱   学  习E X   C    EL!”中的空格去除掉。在C2单元格中输入“=SUBSTITUTE(A3," ","")”就可以了。

这里用空字符代替了字符串中的空格。

例二:统计报名人数

有时候,花名册是书写成这个样子的。怎样快速统计人数呢?

分析一下:报名人数彼标点符号“,”多1,因此只要求出有多少个标点符号就能算出有多少报名人数。同时,按照中文的输入方式,在逗号的后面会有一个空格用于分隔字符。因此输入下面的公式“=LEN(B10)-LEN(SUBSTITUTE(B10,",",""))+1”,就可以算出人数了。

  • 首先用len函数求出字符串的总长度。

  • 用“”来代替“,”后,此时字符串中已经没有标点符号了。

  • 用len函数来计算替换后的字符串的长度。

  • 总长度减去替换后的长度。其实就是逗号的个数。

  • 逗号个数加一就是报名人数。

例三:分列

我们经常会遇到这样的情况:需要将数字和文本混合书写在同一单元格内的字符串分列。下面的情况是不适合用Excel的分列功能的,因为文本字符的长度不一致。

此我们可以用substitute函数来实现这样的效果。这里需要用到数组公式。

在单元格C17中我们输入

“=LEFT(A17,SUM(LEN(A17)-LEN(SUBSTITUTE(A17,{0,1,2,3,4,5,6,7,8,9},""))))”,并三键回车

在单元格D17中我们输入“=RIGHT(A17,LEN(A17)-LEN(C17))”

它基本的逻辑是:

  • 用空字符分别替代字符串中的数字0~9。

  • 分别计算替代后的新的字符串的长度。

  • 用替代前的字符串的长度减去替代后的字符串的长度,实际得出的是每个数字的个数。

  • 将以上这些数字的个数相加,其和就是字符串中数字的位数。

  • 用left函数将数字从字符串中提取出来。

  • 最后用right函数将文本提取出来。

substitute函数这个分列的应用是比较复杂的。大家如有问题请私信我哦!

-END-

欢迎关注【Excel应用之家】专栏,了解更多的Excel实际应用技能,尽在Excel应用之家!

版权声明:本文归Excel应用之家专栏(微信公众号Excel应用之家)原创撰写,未经允许不得转载。欢迎关注专栏/公众号。

(0)

相关推荐

  • 规范单词句子3函数:upper,lower,proper随你挑

    今天说3个函数,都是和单词有关的.分别是upper,lower,proper. upper:将一个文本字符串中的所有小写字母转换为大写字母,不改变文本中的非字母字符.小写转大写. lower:将一个文 ...

  • 快速提取字符串,向左向右从中间!

    行云里讲堂(ID:xingyunli2022) 践行终身学习,专注个人提升. 整理编辑:枏北 LEN函数功能是返回文本串的字符数. MID函数作用是从一个字符串中截取出指定数量的字符. LEFT函数作 ...

  • 如何将代码补齐为指定位数?

    <偷懒的技术>读友群有朋友提问,如何将A列长短参差不齐的代码(代码的首字母均只有一位),用0补齐为五个字符长度,效果如B列C列如示. 一.公式1 公式思路 在自定义格式中,0是占位符,当数 ...

  • 文本函数len和lenb的用法

    之前说过很多文本函数,今天说说len和lenb的用法.这个函数有2种形式,一种是len,一种是lenb.len是返回字符串中字符的个数,而lenb是返回字符串中字节的个数.它们的区别就在于后缀的b,b ...

  • 史上最强文本连接函数textjoin的用法

    我们昨天学了2个文本连接函数,今天来学一个功能更强大的文本连接函数--textjoin.此函数也是新增函数,要office2016版以上才可能有这个函数,它的功能是使用分隔符连接文本字符串区域. -0 ...

  • 瞬间搞定大量数据!这4个Excel函数太牛了!

    哈喽大家好,我是小可~ 在实际应用中我们经常会用到数据粘贴,数据导入等大量数据的处理. 在这些过程中我们经常会遇到数据中包含不确定的空格.换行,导致在做数据匹配.引用.查找时出现错误. 今天小可带来四 ...

  • 文本函数char和code的用法

    接着来说文本函数,今天讲2个可以互转的函数,那就是char和code.char函数返回对应于数字代码的字符.code函数返回文本字符串中第一个字符的数字代码. 可以简单理解为:在excel中,一个数字 ...

  • 【视频】Excel函数每日一讲(52):Substitute函数

    以上视频的所有教学内容,选自教材<玩转Office轻松过二级>(第3版) 书上包含所有方法.技巧.题目,可以自己看书自学. 字都认识,为啥要让别人念呢? 选自本书第9章前言 公式和函数,E ...

  • SUBSTITUTE函数提取字符经典应用一例

    动手操作是熟练掌握EXCEL的最快捷途径! 利用SUBSTITUTE函数配合MID函数和REPT函数可以有效地提取特殊符号间的字符.例如上图中提取的著作名称. 01 这个用法是SUBSTITUTE函数 ...

  • Excel教程:SUBSTITUTE函数解决文本单元格的判断和计算,你会吗?

    每天一点小技能 职场打怪不得怂 编按:SUBSTITUTE替换函数,在文本单元格数据的判断.比较.计算中常常起到至关重要的作用.下面,将用两个工作实例,全面分析,怎么用SUBSTITUTE函数和其他函 ...

  • Excel中的替换神技,SUBSTITUTE函数使用技巧详解

    梅英疏淡,冰澌溶泄,东风暗换年华. --<望海潮·洛阳怀古>宋.秦观 在Excel中,说到换不知道大家首先想到的是什么函数呢? 如果对函数了解比较多的话,可能会想到REPLACE函数以及S ...

  • SUBSTITUTE函数用法全总结

    HI,大家好,我是星光. 今天给大家分享一个函数,叫做SUBSITUTE. SUBSITUTE函数是Excel文本处理中最常使用的函数之一,可以将数据局部的旧值替换为新值,类似于基础操作里的查找与替换 ...

  • 如何用SUBSTITUTE函数隐藏个人信息?

    如何用SUBSTITUTE函数隐藏个人信息?

  • SUBSTITUTE函数,计算占位(LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1)

    函数名片 ●名称:SUBSTITUTE ●功能:将某一串文本中的值替换成其他值. ●套路:SUBSTITUTE(替换目标,目标里的那个字符,在换的新字符,[替换第几个]) 01 替换单个字符 将下表中 ...

  • 实例详解SUBSTITUTE函数的常见用法

    SUBSTITUTE函数格式为: SUBSTITUTE(text,old_text,new_text,instance_num) text为需要替换其中字符的文本(或含有文本的的单元格的引用) old ...

  • 全了,SUBSTITUTE函数常用套路集合!

    HI,大家好,我是星光.今天给大家集中火力聊一个函数:SUBSTITUTE.该函数是Excel最常用的文本函数之一,在数据分析过程中,常用于字符串的整理和清洗. SUBSTITUTE函数的基础语法是: ...