第12关 文本函数

在前面的学习中，我们学会了【逻辑函数】、【统计函数】和【查询函数】，主要用来处理表格中的数值。

数据的类型除了数值就是文本，今天我们就来学习【文本】处理函数。

点击下载实操练习，打开【1-文本函数初体验】，相信很多同学尤其是HR肯定都处理过这样的表格：

在工作中，我们经常会需要对不同类型的内容进行提取，而因为原始表格经常数据不对齐，文本字符长短不一等等问题，而不得不手动处理。

姓名和邮箱多个信息挤在一个单元格里，我们前面的课程里已经强调过：在Excel里，每一个单元格只能有一个信息！否则将无法对其进行进一步处理。

这里我们可以用今天将要学到的【文本函数】来提取员工邮件信息，首先我们用LEFT函数来提取员工姓名：

所以我们必须把【A列】的信息进行拆分。前面我们学习了【分列】的方式进行信息拆分，但是这里因为每个人名字的字数不同，所以无法按固定宽度进行拆分。

请将函数公式：=LEFT(A2,LENB(A2)-LEN(A2))复制粘贴到【B2】单元格，【员工名称】就提取出来啦！

我们再来提取邮箱信息：

请将函数公式：=RIGHT(A2,LEN(A2)-LEN(B2))复制粘贴到【C2】单元格，【邮箱】也提取出来啦！

统一向下填充，挤在【A列】的信息就轻松拆分成两列啦：

我们用两个简单的函数就实现了信息精确拆分，这两个函数到底是怎么实现的呢？一起来进入【文本函数】的学习吧！

刚才我们复制粘贴的就是两个【文本函数】，在处理文本数据的时候，【文本函数】能帮助我们准确定位，快速提取，批量处理【目标信息】。

1.初识文本函数

接下来我们就逐一讲解使用频率最高的【文本函数】，让大家以后遇到文本不再手动处理。

2.提取文本类函数

首先，我们来学习用于提取文本的三大文本函数：left函数、right函数和mid函数。

“left”

打开案例：【2-提取文本类函数】，我们结合实操案例同步学习：

这是一张公司员工信息表，我们需要补充的是【部门】、【编号】、【出生日期】这三列的内容。

首先我们来填写【部门】，在最右侧有一个【部门编码代码】对照表，要想得出员工所在的部门，只需要根据最左侧的【员工编号】首字母对应【上部门编码代码】中相应的部门就可以了。

这里我们就可以用【文本函数】中的【left函数】提取每个员工的部门编号：【left函数】专门用来提取文本中最左侧的字符。

书写格式：=left(字符串,字符个数)。

我们可以理解为：=left(文本,从左边起剪几位) 。

这个题目中，员工编号的第一位为就是部门代码 =LEFT(A2,1)。

这样员工所属的部门编码就提取出来了，我们再用上一节课学习的vlookup函数，把每个字母所代表的部门填写上：

=VLOOKUP(LEFT(A2,1),$H$2:$I$6,2,0)

写好函数公式后下拉，每个员工对应的部门就都填写好啦！接下来，我们来提取【编号】。

“right”

这里我们需要用到的是和left函数相对的right函数。这两个文本函数同样是用于提取文本内容，但是left函数是提取文本中最左侧的字符，而right函数是提取文本中最右侧的字符。

同样在【A列】的【员工编号】这里，每个员工的编号就跟在部门代表的字母后面。我们现在需要把字母后面的数字提取到【编号】下。

根据【员工编号】可以看出来，编号为四位数，所以这里的函数公式为：=RIGHT(A2,4)

我们要提取的数字信息在【A列】的右侧，所以用right函数：

书写格式：=right(字符串,字符个数)

我们可以理解为：=right(文本,从右边起剪几位)

“mid”

【部门】和【编号】填好了，我们来提取员工的【出生日期】，你会发现可以用第5关学过的【分列】功能进行提取，但接下来我们将用【文本函数】来完成这个任务。

这里我们用到的是文本函数中专门用来提取文本中间的字符的【mid函数】：

书写格式：=mid(字符串,开始位置,字符个数)

我们可以理解为：=mid(文本,从第几位剪，剪几位)

想要从身份证中提取出出生日期，我们先来确定一下在身份证中代表出生日期的字符是哪些：

出生日期是从第7位开始的后8个字符，所以公式为=MID(C2,7,8)。

包含了出生日期信息的字符被提取出来了，但是这一串文本型的数值不能直观看出年月日，这里可以嵌套【TEXT函数】把它转换成“xxxx年xx月xx日”的形式，【TEXT函数】的含义先卖个关子，待会会讲到，我们先直接复制下面写好的公式到【F2】单元格：

=TEXT(MID(C2,7,8),"0000年00月00日")。

相信同学们都已经做出来了，掌握了最常用的三大提取文本类函数。接下来我们就来开启定位辅助类函数的学习：

3.定位辅助类函数

打开案例：【3-find函数】，我们继续边学边练：

“find”

首先我们来找到【逗号所在位置】，这里用到的文本函数是：find函数，来查找文本中字符的位置。

书写格式：=find(要查找的字符串,被查找字符串)

我们可以理解为：=find(字符,包含字符的文本)

这个表格里又出现了多个信息挤在同一个单元格里的情况，我们要提取物品的重量，可以用刚刚学到的right函数，但是提取文本长度不是固定的值，该怎么办呢？

题目的要求就是从【A2】单元格找到“，”所在的位置： =FIND("，",A2)。

tips：如何确定查找的逗号“，”是中文的还是英文的，直接在单元格中复制粘贴出来即可。

紧接着我们计算出文本的长度，在【文本函数】中专门就有一个计算文本长度的len函数。我们直接把要计算出文本长度的单元格放进len函数里就可以了：

算出了文本长度，我们来继续提取【重量】，这里我们可以用right函数：

【重量：6kg】文本长度= 【A2】单元格文本长度 - 【规格：304024，】文本长度（含逗号前的文本长度）

=RIGHT(A2,(C2-B2))

“len、lenb”

接下来，打开案例案例：【4-len-lenb函数】，我们一起来看一下：

字符数和字节数有什么区别？1个数字或英文字母=1个字符=1个字节，1个汉字=1个字符=2个字节，课程里的文本长度是指文本的字符数。所以，我们可以用len和lenb函数来计算汉字文本长度：

文本中汉字的长度=文本的字节数-文本长度

文本长度 =LEN(A2)

文本字节数=LENB(A2)

我们先分别算出【文本长度】和【文本字节数】，然后就可以求出【汉字长度】啦：

算出汉字的长度了吗？紧接着我们根据【邮箱信息】和【汉字长度】来提取出【姓名】，操作如下：

姓名提取出来了，我们用算好的【文本长度】-【汉字长度】计算出邮箱文本长度 =B2-D2

最后，我们再用right函数提取邮箱 =RIGHT(A2,F2)

邮箱就提取出来了！你都做出来了吗？快来对照老师的结果看看自己做对了没有吧：

“&”

4.其他文本类函数

打开案例：【5-其他文本函数】，我们来学习其他常见的文本函数。

根据我们平时写日报的经验可以知道，其实这里日报的内容也就只需要把前面已经有的信息汇总在一起就可以了。这里我们可以直接用&连接法连接单元格和文本。

格式：单元格&"文本"

tips：①单元格和文本连接，或单元格与单元格连接，都需要用&连接；②文本需要用英文双引号“”括起来。

这次是我们做销售的同学经常用到的业绩统计表，我们先要根据前面已有的数据来输出【日报内容】。

我们来仿照第一条日报的内容，用&连接生成日报：

书写完成后，我们来观察一下输出的内容：日期显示成了【43598】， 完成率变成了【0.811353383458647】，我们需要对这两个信息的显示方式进行转换：

“text”

转换日期和百分比为文本格式需要用到的是【text函数】。

text函数可以将数字转换为文本并且定义文本的格式，它的书写格式也很简单：=TEXT(数值, 文本格式)

常见的text函数使用情况可以收藏下图：

我们参照上图，先来转换日期 =TEXT(J2,"yyyy年m月d日")

同样的，转换百分比 =TEXT(J5,"0%")

日期和完成率设置好以后，我们同步更新一下日报公式：

=B3&"你好，你的业务指标为"&C3&"，截止"&TEXT(A3,"yyyy年m月d日")&"的销售业绩为"&D3&"，完成率"&TEXT(E3,"0%")&"，业务单元排名第"&F3&"名，请确认。"

书写完成并向下填充，日报内容就都做好啦！你做出来了吗？快对照下图看看自己有没有做对吧：

本节课程，我们掌握了常用的【文本函数】：

我们依次学习了提取文本函数、定位辅助类函数、以及其他的文本函数：

恭喜你完成第12关的学习，轻轻扫描下方的二维码，就可以到我们公众号领取价值99元的通关大礼包喔~

5.下关预告

下载表格

第12关-12-超全实用Excel图表模板