Excel文本函数TEXT使用方法大全

Excel文本函数TEXT使用方法大全TEXT函数是一个超级好用的格式化文本函数，今天韩老师详细给大家讲来。

函数基础功能：TEXT 函数可通过格式代码对数字应用格式，从而更改数字的显示方式。

如果要按更可读的格式显示数字，或者将数字与文本或符号组合，它将非常有用。

语法TEXT(数值,格式代码)Value：数值，或是计算结果为数字值的公式，也或对包含数字值的单元格的引用。

Format_text：文本形式的数字格式。

text返回的一律都是文本形式的数据。

如果需要计算，可以先将文本转换为数值，然后再计算。

文本型数值遇到四则运算会自动转为数值。

但文本会不参与sum之类的函数运算。

分类应用TEXT 函数主要是通过格式代码来应用格式的。

今天，韩老师来讲TEXT函数格式日期与时间的用法：1、格式日期先看下图，TEXT函数通过不同的格式代码，转换日期格式的结果：（格式代码，即是TEXT 公式的第二个参数，下图公式中“”内的部分）公式中的代码及其含义：m 将月显示为不带前导零的数字。

mm 根据需要将月显示为带前导零的数字。

mmm 将月显示为缩写形式（Jan 到Dec）。

mmmm 将月显示为完整名称（January 到December）。

d 将日显示为不带前导零的数字。

dd 根据需要将日显示为带前导零的数字。

ddd 将日显示为缩写形式（Sun 到Sat）。

dddd 将日显示为完整名称（Sunday 到Saturday）。

yy 将年显示为两位数字。

yyyy 将年显示为四位数字。

另：阿拉伯数字与中文数字转换时：格式参数为'[dbnum1]'：普通的大写，如“七百八十九”；格式参数为'[dbnum2]'：财务专用大写，如“柒佰捌拾玖”；格式参数为'[dbnum3]'：阿拉伯数字之间加单位，如“７百８十９”；但用'[dbnum3]'转成的数字是全角，所与如果转换成普通的半角，TEXT函数之外要套用ASC函数。

2、格式时间公式中的代码及其含义：h 将小时显示为不带前导零的数字。

[h] 以小时为单位显示经过的时间。

如果使用了公式，该公式返回小时数超过 24 的时间，请使用类似于[h]:mm:ss 的数字格式。

hh 根据需要将小时显示为带前导零的数字。

如果格式含有 AM 或PM，则基于12 小时制显示小时；否则，基于24 小时制显示小时。

m 将分钟显示为不带前导零的数字。

注释 m或mm代码必须紧跟在h或hh代码之后或紧跟在ss 代码之前；否则，Excel 会显示月份而不是分钟。

[m] 以分钟为单位显示经过的时间。

如果所用的公式返回的分钟数超过 60，请使用类似于[mm]:ss 的数字格式。

mm 根据需要将分钟显示为带前导零的数字。

注释m或mm代码必须紧跟在h或hh 代码之后或紧跟在ss代码之前；否则，Excel 会显示月份而不是分钟。

s将秒显示为不带前导零的数字。

[s] 以秒为单位显示经过的时间。

如果所用的公式返回的秒数超过 60，请使用类似于[ss] 的数字格式。

ss 根据需要将秒显示为带前导零的数字。

如果要显示秒的小数部分，请使用类似于 h:mm:ss.00 的数字格式。

AM/PM、am/pm、A/P、a/p 基于12小时制显示小时。

时间介于午夜和中午之间时，Excel 会使用AM、am、A 或a 表示时间；时间介于中午和午夜之间时，Excel 会使用 PM、pm、P 或p 表示时间。

3、千分位分隔符要将逗号显示为千位分隔符或按倍数1,000 缩放数字。

,（逗号）在数字中显示千位分隔符。

如果格式中含有被数字符号(#) 或零包围起来的逗号，Excel 会分隔千位。

位占位符后的逗号会以1,000 为单位计量数字。

例如，如果format_text 参数为'#,###.0,'，Excel会将数字12,200,000显示为12,200.0。

公式中的代码及其含义：'#,###' 只保留整数'#,###.00' 保留两位小数'#,' 显示为1,000的整倍数'#,###.0,' 显示为1,000的整倍数，且保留一位小数'0.0,,' 显示为1,000,000的整倍数，且保留一位小数其中：# 只显示有意义的数字而不显示无意义的零。

4、格式数字、货币公式中的代码及其含义：'0.00' 只保留整数'#,##0' 千分位分隔符，只保留整数'#,##0.00' 千分位分隔符，保留整数两位小数'$#,##0' 只保留整数'$#,##0.00' 保留两位小数'$#,##0.00_);($#,##0.00)' 两位小数，负数'$ * #,##0' 只保留整数，$与数字间一个空字符'$ * #,##0.00' 两位小数，$与数字间一个空字符5、加0前导符补充位数6、百分比7、特殊格式8、条件区段判断8.1四个条件区段：TEXT函数的格式代码默认分为4个条件区段，各区段之间用半角分号间隔。

默认情况下，这四个区段的定义为：[>0];[<0];[=0];[文本]【举例1】按区段条件判断，然后返回相应结果：公式：=TEXT(A2,'0.00;-0;0;文本')的含义是：A2单元格的值，按照四种情况返回结果：>0，保留两位小数；<0，只保留整数；=0，返回0值；文本，返回“文本”二字。

【举例2】按区段条件，强制返回相应结果：公式：=TEXT(A8,'1!0!0;5!0;0;文本')的含义是：A8单元格的值，按照四种情况返回结果：>0，返回100；<0，返回50；=0，返回0值；文本，返回“文本”二字。

公式中使用的感叹号（英文半角）是转义字符，强制其后的第一个字符不具备代码的含义，而仅仅是数字。

比如：1!0!0，将两个0强制成数字0，而不是数字格式代码0。

在实际应用中，可以使用部分条件区段。

8.2三个条件区段：三个区段为：[>0];[<0];[=0]【举例3】公式：=TEXT(A15,'盈利;亏损;平衡')的含义是：A15单元格的值，按照三种情况返回结果：>0，返回“盈利”；<0，返回“亏损”；=0，返回“平衡”；8.3两个条件区段：两个区段的为：[>0];[<0]【举例4】公式：=TEXT(A22,'盈利;亏损')的含义是：A22单元格的值，按照两种情况返回结果：>0，返回“盈利”；<0，返回“亏损”；一个区段的，就不讲了，昨天前天的两篇文章，都算是一个区段的。

9、自定义条件区段TEXT函数除了可以使用默认区段以外，还可以自定义条件区段。

9.1四个自定义条件区段：四个区段的定义为：[条件1];[条件2];[不满足条件的其他部分];[文本]【举例5】公式：=TEXT(A38,'[>=85]优秀;[>=60]合格;不合格;无成绩')的含义是：A38单元格的值，按照自定义的四种情况返回结果：>=85，返回“优秀”；>=60，返回“合格”；不满足以上条件的数值，返回“不合格”；非数值，返回“文本”二字。

9.2三个自定义条件区段：三个区段的定义为：[条件1];[条件2];[不满足条件的其他部分]【举例6】公式：=TEXT(A46,'[>=85]优秀;[>=60]合格;不合格')的含义是：A46单元格的值，按照自定义的四种情况返回结果：>=85，返回“优秀”；>=60，返回“合格”；不满足以上条件，返回“不合格”；9.3两个自定义条件区段：两个区段的定义为：[条件];[不满足条件的其他部分]【举例7】公式：=TEXT(A54,'[>=60]合格;不合格')的含义是：A54单元格的值，按照自定义的四种情况返回结果：>=60，返回“合格”；不满足以上条件，返回“不合格”；10、巧用TEXT嵌套自定义多条件区段以上举例中，我们可以看到，成绩只能判断到“优秀、合格、不合格”级别，如果再多级别，一个TEXT就解决不了了。

TEXT函数也可以嵌套解决这个问题：【举例8】要求：90分及以上，返回“优秀”；70分及以上，返回“良好”；60分及以上，返回“合格”；60分以下，返回“不合格”。

结果如下：公式：TEXT(TEXT(A62-60,'[>=30]优秀;不合格;0'),'[>=10]良好;合格')，分解来解释：TEXT(A62-60,'[>=30]优秀;不合格;0')对A62-60进行分段计算：如果>=30，返回“优秀”；如果<0，返回“不合格”；不满足以上条件，返回成绩的整数。

如果成绩中有小数，最后一个区段可以写成0.0，或0.00.通过这个公式，把成绩分段成了>=90,<60,60~89三个区段。

TEXT(TEXT(A62-60,'[>=30]优秀;不合格;0'),'[>=10]良好;合格') 这一部分，对60~89的成绩，减去60，然后计算：如果>=10，返回“良好”；否则，返回“合格”。