Excel大神都爱用的text函数7个经典用法，你会几个？

各位小伙伴大家好，我是Excel归故乡。TEXT函数可谓是一个很神奇的函数，说简单也非常简单，但是也有大神将其用的出神入化。本期就先给大家介绍一下TEXT函数比较简单实用的7个经典用法。

1、将日期变为星期几的形式。

我们在实际工作中，经常会遇到计算周末加班时长、周末加班补贴等情况，但是我们如何根据日期快速地判断出是星期几呢？这里就可以使用到TEXT函数来将日期格式进行转换。这里用到的格式代码是”aaaa”，对应的公式就是=TEXT(A2,”aaaa”)。下面看一下实际效果：

并且这里我们使用TEXT转换后内容是真实存在的，并不是像自定义格式那样仅仅改变了外显格式，所以我们在使用条件计数，条件求和等公式的时候，可以将星期几直接作为条件。（彩蛋：将aaaa改成aaa看看是什么效果~）

2、字符链接日期变成数字的处理办法

不知道大家在做报表的时候有没有需要在标题嵌入日期的情况，比如说“Excel归故乡2021年10月20日企划日报”，这个日期我们不想每天修改，所以就采用today函数来自动根据当天日期生成，当我们直接用连字符来讲文字和日期相连的时候却发现，日期被打回原形，变成了数字（看过我视频的小伙伴应该都知道日期的真值是数字），这该如何是好呢？此时我们可以用TEXT函数来将日期格式进行固定，然后再链接就可以了。这里用到的日期代码是”yyyy年m月d日”，对应公式为=” Excel归故乡”&TEXT(A2, ”yyyy年m月d日”)&”企划日报”，前后连接的文字可以根据自己的实际情况来设置。我们来看一下不用TEXT函数直接连接和使用TEXT函数连接的效果差异：

直接连接：

使用TEXT函数进行格式固定后连接：

（彩蛋：试试将年月日的日期格式改动一下，看看有什么意外发现）

3、从身份证提取出生日期（最短公式法）

从身份证提取出生日期，应该是每位HR逃脱不掉的问题了，网上的方法也是五花八门，这里给大家介绍一个最短的公式，一步到位。

=--TEXT(MID(A2,7,8),'0000-00-00')

首先是mid(A2,7,8)函数将身份证中代表出生年月日的8位数字给提取出来，例如20200704，但是只是一串数字，并不是我们需要的日期格式，说到格式那就可以用TEXT函数来进行格式的设置，简单来说就是在年月日之间加上代表日期的短横线，我们用0作为数字占位符，在对应的地方用短横线来隔开，进而得到这个公式=TEXT(MID(A2,7,8),'0000-00-00')。经过这一步之后我们可以得到日期格式的日期，但是这时候的日期是文本格式，不能正常参与计算（感兴趣的小伙伴，可以进入筛选页面，如果是正常日期格式的话筛选栏可以看到年月日的自动划分），如下图所示：

如果需要实现真正的日期格式，其实很简单，我们只需要在TEXT函数前面加上两个减号，或者乘以1，原理和将文本格式的数字转为数值是一样的。最后需要注意的是将单元格设置为日期格式，最终公式=--TEXT(MID(A2,7,8),'0000-00-00')

（彩蛋：试试两个减号改成DATEVALUE，效果一样）

4、批量统一工号位数（用0在前面将不足的位数补齐）

我们在编写工号的时候的，通常为了满足系统需求，或者是为了美观，要求工号的位数必须是一样的，比如要求都是6位，并且不足6位的用0在前面补齐，比如000188。我们很多小伙伴采用的是先将单元格设置为文本格式，然后挨个输入工号，这样非常耽误时间，实际上我们也可以用TEXT函数来批量实现这个功能。这里用到的格式代码是占位符0，对应的公式就是=TEXT(A2,”000000”)，这里根据需求，统一为多少位工号，就填写多少个0。下面是实际效果：

5、手机号分段显示

手机号根据我们的阅读习惯，通常会被分成三段，如：188-8888-8888，那我们如何批量将未分段的手机号进行分段？其实我们在提取出生日期的案例已经讲过了，可以直接利用TEXT函数来分段，公式为=TEXT(A2,”000-0000-0000”)，分隔符的短横线可以换成空格等，这个用法非常简单，就不做过多阐述。

6、取两个时间之间整的小时数

经常有一些HR会问到一个问题，如何计算上班小时数，从上班打卡到下班打卡到底上了多少个小时的班？18:00算下班，后面的时间算加班，那又加了多少个小时的班？这些都是常见问题，有的小伙伴可能看到后束手无策，但其实这个问题TEXT函数就可以解决~首先我们知道，两个时间，用较大的日期减去较小的时间，最后得到的还是一个时间，这个时间就是时差，但是我们只要小时部分，我们就可以用到格式代码”h”，算相差小时数的公式就可以写为=TEXT(B2-A2,”h”)，这时候就可以得到两个时间之间相差的小时整数了。效果图：

（彩蛋：如果把h换成代表分钟的m，代表秒钟的s会是什么情况呢？）

7、简单的条件判断（类似基础的if判断）

我们在自定义格式当中讲解过，自定义格式可以用来做基础的if条件判断，其实TEXT函数也可以实现这个功能，但是TEXT函数局限于三段判断，具体的格式代码我们在自定义格式中有详细的讲解。这里的公式可以写为=TEXT(A2,'[>90]优秀↑;[>60]及格;不及格↓')，效果如下：

以上就是TEXT函数的7个简单但是又实用的经典用法。有时候一个函数用到极致的时候，你就是高手，一起学起来吧~