Excel技巧，超实用的Excel文本函数！

我们工作中经常需要使用excel对数据中的文本字符进行提取、查找和替换等操作，通常都会用到LEFT、RIGHT、MID、FIND、SEARCH、REPLACE、SUBSTITUTE…等文本函数，今天小琥就来给大家分享一下Excel中17个常用的文本函数。文章内容较长，建议先收藏再阅读哦！

LEN函数和LENB函数

LEN函数：是返回文本字符串中的字符个数。

LENB函数：是返回文本字符串中字符的字节数。

LEN和LENB的区别

LEN是计算字符的个数，不管是单字节还是双字节，始终按1计数。LENB是计算字符的字节数，半角状态下输入的英文字母、数字、标点符号，每个字符按1个字节计算的；汉字、全角状态下输入的英文字母、数字、标点符号，每个字符按2个字节计算。

LEN函数和LENB函数语法：

=LEN(要计算字符数的文本)

=LENB(要计算字节数的文本)

LEN和LENB两个函数都只有一个参数，为必填参数，参数为要计算其长度的文本，空格也会被计算进去。

LEFT函数和LEFTB函数

LEFT函数：是从字符串的左侧开始，以字符个数为单位，提取指定个数的字符。

LEFTB函数：是从字符串的左侧开始，以字节数为单位，提取指定个数的字符。

LEFT函数和LEFTB函数语法：

LEFT(在哪提取,提取的字符个数)

LEFTB(在哪提取,提取的字节数)

第一参数：必需，包含要提取字符的文本字符串。第二参数：可选，指定要由LEFT或LEFTB提取的字符的数量，该参数必需大于或等于0.如果忽略，默认值为1。LEFT和LEFTB的区别

这两个函数的用法类似，只不过LEFT是按照字符数计算的，LEFTB是按字节数计算的。另外下文所有区分字符和字节的文本函数，区别都是一样的，下文不再赘述！

示例：要求提取A列姓名中的姓氏，男性用先生表示，女性用女士表示

在C2单元中输入公式=LEFT(A2)&IF(B2="男","先生","女士")，将公式向下填充至C15单元格。公式解析：

因为姓氏处于姓名的第一个字符，所以这里我们可以直接使用LEFT函数提取姓名的第一个字符即可，首先用LEFT函数提取A列姓名的第一个字符：LEFT(A2)，这里只提取一个字符，所以第二参数可以省略。然后再使用if函数对B列的性别进行判断，如果是男，则显示先生，否则显示女士。最后再使用连接符号&将提取出来的姓氏和称呼连接起来，最终就完成了想要的结果。

RIGHT函数和RIGHTB函数

RIGHT函数：是从字符串的右侧开始，以字符个数为单位，提取指定个数的字符。

RIGHTB函数：是从字符串的右侧开始，以字节数为单位，提取指定个数的字符。

RIGHT函数和RIGHTB函数语法

RIGHT(在哪提取,提取的字符个数)

RIGHTB(在哪提取,提取的字节数)

第一参数：必填参数，包含要提取字符的文本字符串。第二参数：可选参数，指定要由RIGHT或RIGHTB提取的字符的数量。该参数必需大于或等于0.如果忽略，默认值为1。示例：要求将下表联系方式中的电话号码提取出来

在C2单元中输入公式=RIGHT(B2,LEN(B2)*2-LENB(B2))，将公式向下填充至C12单元格。公式解析：

因为B列联系方式中的号码位数不固定，所以无法直接使用RIGHT函数从右侧提取指定个数的字符，所以这里我们要先计算出电话号码的第一个数字所在的位置，首选使用LEN函数和LENB计算出电话号码的第一个数字所在的位置：LEN(B2)*2-LENB(B2)（因为B列的联系方式是汉字和数字的组合，汉字1个字符按照2个字节计算，数字1个字符按照1个字节计算，用2倍的字符数减去1倍的字节数，得到的结果就是电话号码首个数字所在的位置）。然后将计算出来的首个数字的位置作为RIGHT函数的第二参数，也就是使用RIGHT函数从右侧提取，指定个数的字符。

MID函数和MIDB函数

MID函数：是从字符串中的指定位置开始，以字符个数为单位，提取指定数量的字符。

MIDB函数：是从字符串中的指定位置开始，以字节为单位，提取指定数量的字符。

MID函数和MIDB函数语法

MID(在哪提取,开始位置,提取字符的个数)

MIDB(在哪提取,开始位置,提取的字节数)

第一参数：必填。包含要提取的字符的文本字符串。第二参数：必填。文本中要提取的第1个字符的位置。文本中第1个字符为1，第2个字符为2......依此类推。第二参数：必填。指定希望MID或MIDB从文本中返回字符的个数。示例：要求提取下方身份证号码中的出生日期

因为身份证号码中的出生年月日是从第7位开始，要提取8位数字，

所以在B2单元格中输入公式=MID(A2,7,8)，向下填充至B12单元格。即可将身份证号码中的出生年月日提取出来。

FIND函数和FINDB函数

FIND函数：是从字符串中的指定位置开始，以字符个数为单位，返回找到的第1个匹配字符串的位置。

FINDB函数：是从字符串中的指定位置开始，以字节单位，返回找到的第1个匹配字符串的位置。

FIND函数和FINDB函数语法：

FIND(要查找的文本，包含查找文本的字符串，开始查找位置)

FINDB(要查找的文本，包含查找文本的字符串，开始查找位置)

第一参数：必填，要查找的文本。第二参数：必填，包含查找文本的字符串。第三参数：可选，指定要从哪个位置开始搜索。如果省略，则默认从第一个位置开始查找。另外FIND与FINDB查找时对字母区分大小写，并且不能使用通配符。如果不区分大小写或者使用通配符，可以使用SEARCH和SEARCHB函数，下文将会介绍该函数的使用方法。

示例：要求将下表地址中的城市名提取出来

在B2单元格中输入公式=LEFT(A2,FIND("市",A2))，并向下填充至B8单元格。公式解析：

因为地址中的城市名都包含“市”字，所以首先使用FIND函数查找“市”字所在的位置：FIND("市",A2)然后将返回的位置作为LEFT函数的第二参数提取，结果就可以将每个城市名提取出来了。

SEARCH函数和SEARCHB函数

SEARCH函数：是返回一个指定字符串在原始字符串中第一次出现的位置（以字符个数计算），从左到右查找，忽略大小写。

SEARCHB函数：是返回一个指定字符串在原始字符串中第一次出现的位置（以字节数计算），从左到右查找，忽略大小写。

SEARCH函数和SEARCHB函数语法：

SEARCH(要查找的文本，包含查找文本的字符串，开始查找位置)

SEARCHB(要查找的文本，包含查找文本的字符串，开始查找位置)

第一参数：必填参数，要查找的文本。第二参数：必填参数，包含查找文本的字符串。第三参数：可选参数，指定要从哪个位置开始搜索。如果省略，则默认从第一个位置开始查找。示例：要求将下表中的姓名提取出来

在C2单元格中输入公式：=LEFTB(A2,SEARCHB("?",A2)-1)，并填充至C13单元格，公式解析：

A列中的姓名有两个字的也有三个字的，所以无法直接使用LEFT函数提取，我们前面讲过一个中文汉字是两个字节，数字是一个字节，这里使用SEARCHB函数结合通配符“?”提取第一个单字节字符的位置,也就是第一个数字所在的位置，因为要提取的是姓名，所以这里要减1，就会得到姓名中最后一个汉字所在的位置，即公式：SEARCHB("?",A2)-1最后再使用LEFB函数进行提取就可以了，因为查找位置时是按照字节为单位计算的，所以也要使用LEFTB函数按照字节进行提取。

REPLACE函数和REPLACEB函数

REPLACE函数：是将一个字符串中的部分字符，根据指定的字符数用另一个字符串进行替换。

REPLACEB函数：是将一个字符串中的部分字符，根据指定的字节数用另一个字符串进行替换。

REPLACE函数和REPLACEB函数语法

REPLACE(要替换的原始文本，开始位置，替换个数，新文本)

REPLACEB(要替换的原始文本，开始位置，替换个数，新文本)

第一参数：必填，要替换其部分字符的文本。第二参数：必填，要用新文本替换的原始字符串中字符的位置。第三参数：必填，希望REPLACE或REPLACEB使用新文本替换原始字符串中字符（字节）数。示例：要求将下表中的手机号码中间用7个星号（*）代替，保留前3位和后1位数字

在C2单元格中输入公式=REPLACE(B2,4,7,"*******")，并向下填充至C12单元格。

公式的含义是，使用REPLACE函数，从手机号的第4位开始，替换掉7个字符，替换成*******（7个星号）

REPT函数

REPT函数：是按照给定的次数，重复显示文本。

REPT函数语法

REPT(重复显示文本,重复次数)

第一参数：必填，要重复显示的文本。第二参数：必填，要重复的次数。示例：要求将下表学生的成绩用条形图显示出来

在C2单元格中输入公式=REPT("|",B3)，向下填充至C12单元格，并将C列文本颜色设置为橙色。

本示例是利用REPT函数将竖线"|"重复n次显示，再设置颜色达到条形图的效果。

另外上述REPLACE函数中将手机号替换成7个星号的示例就可以使用REPT函数代替，改写后的公式如下：=REPLACE(B2,4,7,"REPT("*",7)")

SUBSTITUTE函数

SUBSTITUTE函数：是将字符串中的部分字符以指定的新文本替换。

SUBSTITUTE函数语法

SUBSTITUTE（(要替换的原始文本，旧文本，新文本，第N个旧文本）

第一参数：必填，需要替换其中字符的文本，或对含有文本的单元格的引用第二参数：必填，需要替换的旧文本。第三参数：必填，用于替换旧文本的新文本。第四参数：选填，用来指定要以新文本替换第几次出现的旧文本。如果指定了位置，则只替换满足要求的旧文本；否则将会替换原始文本中的每一处旧文本。示例：要求统计出下表方各部门的人数

在C2单元格中输入公式=LEN(B2)-LEN(SUBSTITUTE(B2,"、",""))+1，并向下填充至C8单元格。公式解析：

B列中的各个部门的姓名都是使用顿号分隔的，顿号的个数加1就等于各个部门的人数，这里只要统计出顿号的个数，就能统计出各部门的人数了，所以这里先使用LEN函数计算出包含顿号的字符的总个数：LEN(B2)，然后使用SUBSTITUTE函数将顿号替换成空值：SUBSTITUTE(B2,"、","")，再在外层嵌套一个LEN函数，计算出不包含顿号的字符个数：LEN(SUBSTITUTE(B2,"、",""))最后再用原来的字符个数减去不包含顿号的字符个数，再加上1结果就是各个部门员工的人数。

TRIM函数

TRIM函数：是用来删除字符串前后的空格，但是如果空格在字符串的中间，则会保留一个空格。

TRIM函数语法

TRIM(文本字符串)，该函数只有一个参数。

示例：要求将下表衣服编码中的尺码提取出来

在B2单元格中输入公式=TRIM(RIGHT(SUBSTITUTE(A2,"-",REPT("",99)),99))，并填充至B7单元格。公式解析：

首先使用SUBSTITUTE函数，将A列衣服编码中的分割线"-"替换成99个空格，SUBSTITUTE(A2,"-",REPT("",99))然后再使用RIGHT函数从右提取99个字符，(RIGHT(SUBSTITUTE(A2,"-",REPT("",99)),99)，虽然这里会提取到很多空格，但是我们再使用TRIM函数将多余的空格删除掉就可以了。

以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

学习资料见知识星球。

以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利​​​​！

更多技巧， www.excelbook.cn

欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；