excel入门：=TEXT的基本用法和实战案例

1.基本用法

基本语句：=TEXT(数值，文本格式)

用法说明：TEXT函数可通过格式代码对数字应用格式，进而更改数字的显示方式。其中，参数①是我们想要改变格式的数值，它可以是文本，也可以是数字;而参数②就是我们想要参数①最终变成的格式，它的作用原理和自定义数字格式一致，基本用法共分为四个分段，用英文分号(;)区分隔开：

[正数的格式];[负数的格式];[0的格式];[文本的格式]

具体用法，我们会在下方案例中逐一说明。

2.实战案例

案例1：固定数字位数

以发票号码为例，发票号码均为8位数，但有时系统导出来的发票号码将其前面的0省略了，遇到这种情况时，如何将0批量自动补齐呢?只需输入=TEXT(A4,”00000000″)。

用法说明：

参数②为“00000000”，省略了负数、零值和文本的格式，这表示该格式对所有数值适用，但对文本不适用。此处的0是占位符，表示该数位如有有效数值，则取有效数值，否则以0填充该数位。以A4单元格为例，个位到十万位都有有效数值，所以这部分数值保持不变;前两位数没有有效数值，则用0填充，于是550277就变成了00550277，这就是TEXT函数的作用。

案例2：提取出生日期

如何从身份证号码中提取出生日期并按日期格式填写?这个问题的解决方案有很多，TEXT+MID函数的组合公式就是其中一种。

用法说明：

MID(A2,7,8)用于提取18位身份证号码中出生日期的8位字符串，而TEXT函数负责将8位数的出生日期字符串按0000-00-00的格式排列填写。这里需要着重说明的是，由于MID函数提取出来的日期是一个字符串，而非真正的日期，所以，不能使用”yyyy-mm-dd”来设置格式。只有当我们需要转变的是真正的日期的显示格式时，才可以使用y(年)、m(月)、d(天)这三个字符的组合来实现。

案例3：简单的条件判断

在某种程度上，TEXT函数可以作为三种条件结果判断的加强版IF函数来使用，将满足条件的数值转化为指定的格式。以判断区域公司盈亏情况为例，利润为正则显示为盈，负数则为亏，0则显示为平，我们只需要使用如下公式即可：=TEXT(B2,”[>0]盈;[<0]亏;平”)。

公式说明：

数字格式支持两次条件判断，即[条件1]格式1;[条件2]格式2;格式3，参数②”[>0]盈;[<0]亏;平”就是这种用法的一个实例。它对B列数值先进行条件1判断，如果大于0，则显示“盈”，如不大于0，则进行条件2判断，如果小于0，则显示“亏”;如前两个条件都不满足，则显示为“平”。

案例4：计算时间间隔

如何计算上班时长或者加班时长，一直是HR小哥哥小姐姐头痛的问题，不过还好，我们有TEXT函数，什么时间间隔，什么去除尾数，统统搞定!

用法说明：

参数②”[h]”表示将数值转化为以1/24为一个单位的小时数，且只取整数位。这里的h即为hour小时，同理也可以写成分钟m或者秒钟s，分别计算相隔的分钟和秒钟。

想知道更多计算时长的方法，还可以查看往期教程《996和955到底差了多少小时，你会算吗?》

案例5：阿拉伯数字与中文数字互换

中文数字和阿拉伯数字如何互换的问题，让很多花瓣头痛不已。解决方案就是将TEXT的第二参数设置为”[DBnum1]”即可，”[DBnum1]”可以将阿拉伯数字转化为中文小写数字，但仅限整数。

公式1：

=TEXT(A2,”[DBnum1]”)

公式2：

{=MATCH(A2,TEXT(ROW($1:$9999),”[DBnum1]”),)}

公式说明：

公式1极易理解，它通过”[DBnum1]”将阿拉伯数字转化为中文小写数字;公式2比较生涩，它是将1-9999的阿拉伯数字逐一转变成中文小写数字，形成一个用中文小写数字表示的从一到九千九百九十九的数组，最后使用MATCH来实现精确匹配，通过A列中文数字在该中文数组中的位置值来确定中文数字对应的阿拉伯数字。所以说，公式2的本质和公式1是一样的，实质上都是利用”[DBnum1]”来实现的，这一点着实巧妙!