个Excel文本函数.docx
《个Excel文本函数.docx》由会员分享，可在线阅读，更多相关《个Excel文本函数.docx（24页珍藏版）》请在冰点文库上搜索。
个Excel文本函数
16个Excel文本函数，这篇文章教你全弄清楚！
利用Excel函数解决文本处理问题，是最常见不过的任务啦~
比如下面这些，你有没有遇到过？
合并两个单元格的内容？
把省市区分拆？
提取身份证号码中的出生日期？
批量替换数据的单位？
在我眼里，每个文本函数都是一个诗人……接下来就带你领略，各种文本函数的风骚之处。
一共分为六个部分，文本合并、文字提取、字符清洗、文字替换、精确查找、长度计算。
总有你要用上的！
文本合并
合并和提取是文本处理中最最常见的任务。
例如，将下表中各个单元格的文字合成1个新的句子：
而利用不同的函数公式，操作方法和结果都有所不同。
&连字符&能够直接将一个一个的文本连接起来，形成一个新的文本：
公式：
=A1&B1&C1
结果：
爱老虎油
然而，&连字符先天残疾，有很多个字符时，也不能直接引用整片区域进行合并，依然只能一个个手工添加。
操作过程如下：
Phonetic函数用Phonetic就省事很多，它可以引用一个区域（只能一个），将区域内所有单元格的文本型数据拼合在一起。
例如将下表中的全部文本拼合起来：
可以使用如下公式
公式：
=phonetic（A1:
D1）
结果：
爱老虎油
但是明明D1里有一个数字1314，为什么没出现在结果里呢？
因为，phonetic只是个兼职合并函数而已。
它本职工作是提取日文拼音，是日文版来客串一下的，人家只是恰好可以合并文字而已。
这个客串函数个性非常独特，因为它眼里只有文本型数据，对公式结果、数值、日期时间等等通通视而不见。
所以1314就是被狗吃掉了……
此公式的输入就比&连字符简单多了：
Concat函数
而Concat就比&和phonetic更加完美，因为它既能引用一个区域直接合并，又不会漏掉数值、日期和公式结果，还能引用多个区域，简直不能更完美了。
公式：
=concat（A1:
D1）
结果：
爱老虎油1314
不过，很遗憾，完美版本的Concat只有2016版才有。
在之前的版本中，它有个前身，叫Concatenate。
Concatenate和&公式一个德性，只能逐个添加。
名称又太长，还不如用&来得方便。
估计是Office工程师自己都忍不了了，才在2016版添加了一个加强版吧。
这个加强版可棒了：
以上三种合并函数，分别合成的结果放在一起，结果就是酱紫的：
一个宅男表哥用公式向喜欢的妹子发出三行爱的告白。
结果，这个妹子是个表妹，迅速用下面这个函数做出了回应……
Textjoin函数
用分隔符将多个字符串联起来。
公式：
textjoin（“！
”,True,A1:
D1）&”！
”
结果：
你！
是！
个！
好人！
于是，此公式的含义为，将A1：
D1中每个单元格的内容用叹号串联起来，如果有空格则忽略，最后缺少的叹号用&单独补一个。
因为有函数辅助输入工具条，写这个公式也不难：
有一个神奇的函数，可以按照指定的次数，将自己反复拼合起来，俗称自撸。
啊，不，是重复。
16个Excel文本函数，这篇文章教你全弄清楚！
2016-09-13 过河卒冲 摘自 幻方秋叶PPT 阅 989 转 49
转藏到我的图书馆
微信分享：
利用Excel函数解决文本处理问题，是最常见不过的任务啦~
比如下面这些，你有没有遇到过？
合并两个单元格的内容？
把省市区分拆？
提取身份证号码中的出生日期？
批量替换数据的单位？
在我眼里，每个文本函数都是一个诗人……接下来就带你领略，各种文本函数的风骚之处。
一共分为六个部分，文本合并、文字提取、字符清洗、文字替换、精确查找、长度计算。
总有你要用上的！
文本合并
合并和提取是文本处理中最最常见的任务。
例如，将下表中各个单元格的文字合成1个新的句子：
而利用不同的函数公式，操作方法和结果都有所不同。
&连字符&能够直接将一个一个的文本连接起来，形成一个新的文本：
公式：
=A1&B1&C1
结果：
爱老虎油
然而，&连字符先天残疾，有很多个字符时，也不能直接引用整片区域进行合并，依然只能一个个手工添加。
操作过程如下：
Phonetic函数用Phonetic就省事很多，它可以引用一个区域（只能一个），将区域内所有单元格的文本型数据拼合在一起。
例如将下表中的全部文本拼合起来：
可以使用如下公式
公式：
=phonetic（A1:
D1）
结果：
爱老虎油
但是明明D1里有一个数字1314，为什么没出现在结果里呢？
因为，phonetic只是个兼职合并函数而已。
它本职工作是提取日文拼音，是日文版来客串一下的，人家只是恰好可以合并文字而已。
这个客串函数个性非常独特，因为它眼里只有文本型数据，对公式结果、数值、日期时间等等通通视而不见。
所以1314就是被狗吃掉了……
此公式的输入就比&连字符简单多了：
Concat函数
而Concat就比&和phonetic更加完美，因为它既能引用一个区域直接合并，又不会漏掉数值、日期和公式结果，还能引用多个区域，简直不能更完美了。
公式：
=concat（A1:
D1）
结果：
爱老虎油1314
不过，很遗憾，完美版本的Concat只有2016版才有。
在之前的版本中，它有个前身，叫Concatenate。
Concatenate和&公式一个德性，只能逐个添加。
名称又太长，还不如用&来得方便。
估计是Office工程师自己都忍不了了，才在2016版添加了一个加强版吧。
这个加强版可棒了：
以上三种合并函数，分别合成的结果放在一起，结果就是酱紫的：
一个宅男表哥用公式向喜欢的妹子发出三行爱的告白。
结果，这个妹子是个表妹，迅速用下面这个函数做出了回应……
Textjoin函数
用分隔符将多个字符串联起来。
公式：
textjoin（“！
”,True,A1:
D1）&”！
”
结果：
你！
是！
个！
好人！
于是，此公式的含义为，将A1：
D1中每个单元格的内容用叹号串联起来，如果有空格则忽略，最后缺少的叹号用&单独补一个。
因为有函数辅助输入工具条，写这个公式也不难：
有一个神奇的函数，可以按照指定的次数，将自己反复拼合起来，俗称自撸。
啊，不，是重复。
Rept函数
它的语法是Rept（字符串,重复次数）。
好想让妹子用Rept再来一次暴击：
公式：
rept（A1,3）
结果：
你是个好人！
你是个好人！
你是个好人！
公式含义再明显不过了。
好喜欢Rept函数输入后按下Enter键的感觉：
文字提取
提取型文本函数，就是从1个文本中挑出一部分。
常用的提取函数就有Left、Right、Mid（middle的简写）三种。
Left函数
从左边开始算起，提取若干字符。
例如从下面诗句提取左边的7个字符：
公式：
=Left（A1,7）
结果：
一江春水向东流
Right函数而提取右边的7个字符，就要用函数Right：
公式：
=Right（A1,7）
结果：
我也没有女盆友
Left和Right函数语法结构相同：
函数名（字符串,提取数量）。
只是一左一右，提取的方向不一样。
但是第三个函数Mid就厉害得多，它能够从中间指定的位置开始提取。
Mid函数基本语法是Mid（字符串,起始位置,提取数量）
例如，从下面诗句中提取清泪两字，就可以从第11个字符开始提取2个字符（逗号也算一个）。
公式：
=Mid（A3,11,2）&Right（A3,1）
结果：
清泪流
注意到了嘛，上面的公式还用到了连字符&，将两个公式计算的结果拼在一起，得到了最终结果。
字符清洗
从网页上或神队友那弄来的数据，可能会有莫名其妙的空格和换行符。
这些字符虽然看不见，但却会导致公式计算结果出错。
所以通常会用Clean或Trim两个函数进行清洗。
他们语法也一样，都是函数名（字符串）。
但功能上却有细微的差别：
Clean函数
字面意思是清洁，它只清除换行符等看不见的非打印字符，却无法去除空格。
Trim 函数
字面意思是修剪，它会裁头去尾，将前后的空格以及文本内部多余的空格全部清除，但是按英文使用习惯，英文中间会自动保留一个空格字符作间隔。
例如，A1单元格中的字符串有很多多余的空格及2个换行符。
分别用Clean和Trim函数处理的结果如下：
文字替换
Replace和Substitue这哥俩的能力是”置换“。
功能类似，但是定位的方法不同。
Substitute函数
Substitute是以字符定位字符。
例如，找出诗句中的“船”字，然后替换成“床”，公式和结果如下：
公式：
=Substitute（A1,”船”,”床”）
结果：
百年修得同床度，霉霉三月又分手
Replace函数
而Replace则是以位置定位字符。
例如，从第13个字符开始提取1个字符“汉“，然后把”汉“替换成新的字符“妹“，公式和结果如下：
公式：
=Replace（A1,13,1,”妹”）
结果：
此情可待成追忆，我要做撩妹高手
感觉自己是在冒着跪榴莲的风险在写这篇推送……
替换函数还有一项独门秘技：
把替换为的字符写成空值（“”），替换就变成了删除。
看哪个字符不顺眼，一言不合就可以让它消失。
精确查找
Find和Search，都能掘地三尺，精准定位某个字符在文本中的具体位置。
Find函数
公式：
=Find（'King',A1）
结果：
2
计算结果说明King在整句中是从第2个字符开始出现的。
Search函数
公式：
=Search（'洪荒',A1）
结果：
6
结果表明，“洪荒”在整句中是从第6个字符开始的。
虽然Find和Search都是返回某个字符在字符串中的位置信息，结果都是一个数值。
但还是有细微差别，Search可以用通配符模糊查找。
例如，“K?
ng”就能把King、Kong、Kang都找出来，而Find却做不到。
但是，计算返回结果是一个位置数值，有用吗？
接着往下看，你就知道可以怎么用！
她是怎么知道的？
长度计算
Excel中有两把度量文本长度的尺子：
Len函数和LenB函数
Len函数
不管中文还是英文、数字，Len都将每个字符算作1。
公式：
=Len（A1）
结果：
15
两句七律加一个符号，总共15个字符。
LenB函数
LenB后面多出来的那个B是Byte（字节）的意思，是按字节来算。
1个汉字及中文标点都是双字节，长度都是2。
但英文字母和数字通常都是1个字节，长度只有1。
于是，同样是14个汉字和一个标点的诗句，用LenB的计算结果不一样。
公式：
=LenB（A1）
结果：
30
LenB和Len之间的差异，
常常用来处理中英文混合的情况，比如
公式：
=LenB（“King是好人”）-Len（“King是好人”）
结果：
3
由于汉字LenB计算出来的长度是Len的两倍，但英文字母长度相等，两个公式之差，就是汉字的个数。
以上结果正说明字符串中有3个汉字。
如果再配合Right函数，就可以把中文单独提取出来。
Text格式转换函数
text函数能够让单元格数值按照指定的格式显示出来，例如固定显示成4位数，不足就补0的处理方法如下：
公式：
=TEXT（A1,'0000'）
结果：
0069
以上就是常用的文本函数用法，然而很多人可能会有这样的疑问。
“合并和提取用在哪很好理解，可是查找一个字符并返回位置信息，到底有什么用？
”
有些函数计算结果自身并没有多大意义，却能够辅助完成其他任务。
比如Len先求出长度后，就可以按照长度来排个名次……
更厉害的是，计算结果还可以作为其他函数的参数啊。
比如要把下图中每一个数字提取出来变成QiuYeXXXX的格式，只用一个函数，是办不到的。
但是多个函数组合起来使用，上万行的数据也能双击搞定，只需要花几秒钟时间写一个公式就够了：
图中演示的公式就综合运用了4种文本函数，逐步求值的结果如下图：
文本函数远不止这些，但常用的就是下面这16个啦！
其他函数，需要用时再找吧~
我真的不会去背这些函数，但是会根据他们能解决的问题进行观察和梳理，留意他们的特点。
然后在看到其他案例时，自己再去了解一番同时加深理解。
函数看起来枯燥乏味，但只要摸清脉络，就会变得很好玩。
想当初爱上Excel，也就是纯粹觉得好玩。
而文本函数中最喜欢的，就是Rept了。
因为
它可以完美表达心情：
Rept（“你是个好人！
”,6）
Rept（'?
?
',9）
常用文本函数及应用实例
一、什么是文本：
在excel中，除数值、日期时间、逻辑值和错误值以外的所有值。
一个文本值由N个（0<=N<=32767）个字符组成，也叫字符串。
所谓字符串，是计算机可识别的单个符号，分为单字节字符和双字节字符。
所谓字节。
字节（Byte）是计算机信息技术用于计量存储容量和传输容量的一种计量单位。
字符好比人，字节好比房子,中文和中文的标点符号及全角字符都属于双字节字符，普通数字及英文字母，属于单字节字符。
单字节字符属于一个人住一套房子，而双字节字符是一个富二代住两套房子。
二、excel文本函数范围
在Excel2003中有34个规范文本函数及很多非规范文本函数。
主要包括lenmidleftrightfindsearchsubstitudereplacetrimcleanrept及这些函数后带b的函数。
1、len，返回文本字符串中的字符数；lenb，返回文本字符串中用于代表字符的字节数。
Len相当于返回人数，而lenb相当于返回房子数。
语法，lenb（text）、lenb（text）。
Text是要查找的其长度的文本，空格及控制符均作为字符进行计数。
函数返回值：
数值（整数）。
以下例子将探讨不同参数的下的字符及字节长度
A、其中的时间格式下的，字符长度是转换成数字后计算。
如2010-1-31，其转换为数字后是40209，其长度是5。
B、时间参数09:
25，转化为数字格式后是0.392361111111111，其长度就变为17了。
C、逻辑值TRUE是按照单词字符计算的。
现在发现，时间和日期参数，其字符长度有很强的隐蔽性，当将其转换成数字时，就发现庐山真面目了。
就像现在的贪官，表面廉洁奉公，其实内在的坏水很长很长的。
♀
TEXT参数的特性：
1、文本值，按文本本身进行计算。
2，数值，转换成数字型文本计算。
3，日期、时间，按转化为数值后的文本计算。
4逻辑值，按转换后的TRUE、FALSE进行计算。
5、查看转换结果，A1&””.从以上特性来看，text除了错误值以外，可谓参数包罗万象。
需要说明的是，Text 是要查找其长度的文本，空格及控制字符都将作为字符进行计数。
具体应用如下：
如果我们要计算中文个数，LEN（）的一个简单运用即可。
这里我们主要运用了，中文占据双字节，二字母占据单字节。
2、LEFT，基于所指定的字符返回文本字符串的第一个或前几个。
LEFTB，基于所指定的字节返回文本字节的第一个或前几个。
其中文含义是，左边。
主要用于提取字符左边的字符。
有点像中国一句俗话：
枪打露头鸟。
LEFT函数，主要狙击站在树枝左边的鸟。
语法：
LEFT（text,num_chars）,
LEFTB（text,num_bytes）
Num_chars指定所提取的字符，num_bytes指定所提取的字节。
需要说明的是：
num_chars（num_bytes）必须大于或等于0。
如果num_chars（num_bytes）大于文本长度，则函数返回所有文本。
如果省略num_chars（num_bytes），则假定其为1。
如果num_chars（num_bytes）不是整数，则按其整数部分返回结果
返回结果如下表所示，
RIGHT函数，和LEFT函数，区别在于从右边还是从左边。
也就是说，RIGHT函数，主要打击站在树枝右边的鸟。
如表所示：
NUMBER型参数的特性：
1、若是数字型文本，函数内部会将它转换成数值。
2、若数值不是整数，先取整再计算。
3、MID,返回文本字符串中从指定位置开始的特定数目的字符。
MIDB，则是返回字节数。
函数返回值，是文本。
语法：
MID（text,start_num,num_chars）
MIDB（text,stsrt_num,num_bytes）
MID中文是中间的意思，主要提取字符中的值，但是，它的功能也涵盖了left及right.多少也有点“越权”。
O（∩_∩）O。
当然，只要能为“人民办事实”，都是好函数啊。
参数说明：
如果start_num大于文本长度，则MID返回空文本（""）。
如果start_num小于文本长度，但start_num加上num_chars超过了文本的长度，则MID只返回至多直到文本末尾的字符。
如果start_num小于1，则MID返回错误值#VALUE!
。
如果num_chars是负数，则MID返回错误值#VALUE!
。
如果num_bytes是负数，则MIDB返回错误值#VALUE!
。
示例如表：
例如提取身份证信息。
在这里巧妙的利用的len（）函数，取整，及除以2.2得循环得到6和8
4、FIND/FINDB，在一个文本值中查找另一个文本值（区分大小写）；SEARCH/SEZRCHB，返回从start_num开始首次找到特定字符或文本字符串的位置上特定字符的编号。
使用SEARCH可确定字符或文本字符串在其他文本字符串中的位置，（不区分大小写）。
函数返回值均为数值。
FIND语法：
FIND（find_text,within_text,start_num）
FINDB（find_text,within_text,start_num）
Find_text 是要查找的文本。
Within_text 是包含要查找文本的文本。
Start_num 指定开始进行查找的字符。
within_text中的首字符是编号为1的字符。
如果忽略start_num，则假设其为1。
SEARCH语法：
SEARCH（find_text,within_text,start_num）
SEARCHB（find_text,within_text,start_num）
说明：
Find_text 是要查找的文本。
可以在find_text中使用通配符，包括问号（?
）和星号（*）。
问号可匹配任意的单个字符，星号可匹配任意一串字符。
如果要查找真正的问号或星号，请在该字符前键入波形符（~）。
Within_text 是要在其中查找find_text的文本。
Start_num 是within_text中开始查找的字符的编号
示例如下：
5、SUBSTITUTE，在文本字符串中用新文本值代替旧文本值。
函数返回值：
文本。
中国有句俗话，叫：
旧貌换新颜，SUBSTITUTE可谓恰如其分。
中国三十年来的改革开放展现的新迹象，SUBSTITUTE函数也可表达一二啊。
语法：
SUBSTITUTE（text,old_text,new_text,instance_num）
Text 为需要替换其中字符的文本，或对含有文本的单元格的引用。
Old_text 为需要替换的旧文本。
New_text 用于替换old_text的文本。
Instance_num 为一数值，用来指定以new_text替换第几次出现的old_text。
如果指定了instance_num，则只有满足要求的old_text被替换；否则将用new_text替换Text中出现的所有old_text。
示例如下
SUBSTITUTE，函数用来计算单元格数字出现的次数，非常巧妙。
6、REPLACE，使用其他文本字符串并根据所指定的字符数替换某文本字符串中的文本；REPLACEB。
函数返回值：
文本
语法：
REPLACE（old_text,start_num,num_chars,new_text）
REPLACEB（old_text,start_num,num_bytes,new_text）
Old_text 是要替换其部分字符的文本。
Start_num 是要用new_text替换的old_text中字符的位置。
Num_chars 是希望REPLACE使用new_text替换old_text中字符的个数。
Num_bytes 是希望REPLACE使用new_text替换old_text中字节的个数。
New_text 是要用于替换old_text中字符的文
示例：
7、TRIM（）,清楚除单词之间的单个空格外的所有空格。
在从其他应用程序中获取带有不规则空格的文本时，可以使用函数TRIM。
TRIM函数不由得让人想起当下的高房价，普通人的住房空间，几乎被清除了。
难道是TRIM函数捣鬼么？
函数返回值：
文本
语法,TRIM（text）
CLEAN（），删除文本中不能打印的字符。
CLEAN函数，不由的想起城市的清洁工，他们起早贪黑的清理着大街小巷的纸屑、落叶，带给人们一个清洁的环境。
CLEAN堪称excel中的“清洁工”.
函数返回值：
文本
语法CLEAN（text）
示例
上面，TRIM将两的空格及字符之间的多余空格均被清除。
而CLEAN函数则将换行符清除。
8、REPT（）,按照指定次数重复显示文本。
此函数可不断重复显示某一文本字符串，来达到填充的效果。
函数返回值：
文本
语法
REPT（text,number_times）
Text 需要重复显示的文本。
Number_times 是指定文本重复次数的正数。
说明
如果number_times为0，则REPT返回""（空文本）。
如果number_times不是整数，则将被截尾取整。
REPT函数的结果不能大于32,767个字符，否则，REPT将返回错误值#VALUE!
。
现在小巷卖菜的小商贩们，经常使用，一个可以重复播放的扩音器，在大街小巷叫卖。
比如“香蕉9毛，香蕉9毛，香蕉9毛，香蕉9毛，香蕉9毛，香蕉9毛，”如若在古代，这样的叫卖，喉咙时受不了的啊，呵呵。
REPT函数可算是与生活相接轨了。
示例
九、应用
应用1
方法一，是一个基本的应用，利用
升级会员 