注:text函数的返回值均为文本类型
=text(Value,Format_text)
目录
一、对日期格式化
=text(日期,格式代码)
=text(A1,“格式代码”) // A1=2024/4/7 9:05:02(日期类型)
格式代码 | 输出 | 说明 |
---|---|---|
yyyy年mm月dd日 h:mm:ss | 2024年04月07日 9:05:02 | 可任意插入文字或符号 |
yy | 24 | 同y |
yyyy | 2024 | 同yyy、yyyyy、更多y |
m | 4 | |
mm | 04 | |
mmm | Apr | |
mmmm | April | |
mmmmm | A | 同更多m |
d | 7 | |
dd | 07 | |
ddd | Sun | |
dddd | Sunday | 同更多d |
h | 9 | |
hh | 09 | 同更多h |
h:m | 9:5 | m和mm在h后面才表示分钟(不用紧跟着) |
h:mm | 9:05 | 更多m和不在h后面的m都为月份 |
s | 2 | |
ss | 02 | 同更多s |
aaa | 日 | 只能是aaa和aaaa,除此之外的a都直接显示a |
aaaa | 星期日 |
扩展1
格式代码 | 输出 | 说明 |
---|---|---|
h:mmAM/PM | 9:05AM | 同am/pm、A/P、a/p |
[h] | 1089345 | ? |
[m] | 65360705 | ? |
[s] | 3921642302 | ? |
扩展2
格式代码 | 输出 | 说明 |
---|---|---|
[dbnum1]yyyy | 二〇二四 | 对年月日、时分秒通用 |
[dbnum2]yyyy | 贰零贰肆 | |
[dbnum3]yyyy | 2024 | 全角数字 |
[dbnum4]yyyy | ⅢⅠⅢⅤ | |
[dbnum5]yyyy | ⅲⅰⅲⅴ | |
[dbnum6]yyyy | CACE | |
[dbnum7]yyyy | cace | |
=ASC(TEXT(A1,“[dbnum3]yyyy”)) | 2024 | 转换为半角数字 |
二、对数值格式化
=text(数值,格式代码)
=text(A1,“格式代码”) // A1=123456789(数值类型)
格式代码 | 输出 | 说明 |
---|---|---|
#,### | 123,456,789 | 添加千位分隔符,并保留整数(四舍五入,下同) |
0.00 | 123456789.00 | 保留2位小数 |
#,###.00 | 123,456,789.00 | 添加千位分隔符,并保留2位小数 |
#, | 123457 | 显示为1000的整倍数 |
#,###.0, | 123456.8 | 显示为1000的整倍数,并保留一位小数 |
0.0, , | 123.5 | 显示为1000,000的整倍数(看逗号数量),且保留一位小数 |
进阶
=text(数值,“格式代码”)
数值 | 格式代码 | 输出 | 说明 |
---|---|---|---|
10 | ¥0元 | ¥10元 | |
10 | ¥#元 | ¥10元 | |
12 | 0000 | 0012 | 位数不够补0,位数多了原样显示 |
0.3 | 0% | 30% | |
12233334444 | 000-0000-0000 | 122-3333-4444 |
三、条件判断
text主要对数值进行条件判断,其格式代码默认分为4个条件区段,各区段间用分号隔开。
当不满足任一条件时,text将直接放回原值。
1、默认条件区段
=text(数值,“[>0];[<0];[=0];[非数值]”)
数值 | 格式代码 | 输出 | 说明 |
---|---|---|---|
12 | 大了;小了;刚刚好;这个不是数字 | 大了 | |
-5 | 大了;小了;刚刚好;这个不是数字 | 小了 | |
0 | 大了;小了;刚刚好;这个不是数字 | 刚刚好 | |
奥特曼 | 大了;小了;刚刚好;这个不是数字 | 这个不是数字 | |
奥特曼 | 大了;小了;刚刚好 | 奥特曼 | 不满足任何条件时显示原内容 |
12 | !0;小了;刚刚好;这个不是数字 | 0 | 感叹号为转义字符 |
12 | 0;小了;刚刚好;这个不是数字 | 0 | 不加转义字符的0代表显示原数字 |
其他条件判断:
=text(数值,“[>0];[<0];[=0]”) // 有3个条件区段时
=text(数值,“[>=0];[<0]”) // 有2个条件区段时
扩展
A1 | 公式 | 输出 |
---|---|---|
120 | =TEXT(A1-100,“上升;下降;持平;非数值”) | 上升 |
100 | 同上 | 持平 |
80 | 同上 | 下降 |
abc | 同上 | 非数值 |
2、自定义条件区段
注:text最多允许自定义前2个条件区段
数值 | 格式代码 | 输出 | 说明 |
---|---|---|---|
60 | [>=60]及格;不及格 | 及格 | 大于等于60及格,否则不及格 |
abc | [>=60]及格;[<60]不及格;查无此人 | 及格 | 大于等于60及格,小于60不及格,否则显示查无此人 |
12 | [>0]大;[<0]小;[=0]等 | #VALUE! | 报错,第3个条件区段不允许自定义 |
四、补充
公式 | 输出 | 说明 |
---|---|---|
=text(20240428,“0000年00月00日”) | 2024年04月28日 | 对数值格式化 |
=text(“2024/4/28”,“yyyy年m月d日”) | 2024年4月28日 | 对文本格式化 |
=text(“2024-4-28”,“yyyy年m月d日”) | 2024年4月28日 | 对文本格式化 |