Excel 中的 TEXT 函数及其他组合使用

本文目录

一、认识下TEXT 函数二、场景一：提取部分文字三、场景二：金额大写四、场景三：条件判断后输出文字五、场景四：数据显示固定位数六、综合实战：一张报表搞定所有七、写在最后

一、认识 TEXT 函数

TEXT 函数的作用：把数值"打扮"成你想要的文本格式。

语法：

=TEXT(值, "格式代码")

第一个参数：要处理的数值或单元格
第二个参数：用英文双引号包起来的格式代码。

二、场景一：提取部分文字

需求：日期中提取年份、数字编码里取前几位、金额截角分……

什么时候必须用 TEXT？

纯文本（如 "JD20240715-001"）→ 直接用 LEFT / RIGHT / MID 就行数字 / 日期（如 20240715、2024/1/1）→ 必须先用 TEXT 转成文本，再提取。因为 Excel 里日期本质是序列号（如 45658），数字 12 不会自带前导零，直接 LEFT 会得到意料外的结果。

下面列举三个例子必须使用 TEXT

① 从日期中提取「年份」，结合 LEFT

=LEFT(TEXT(A2, "yyyy-mm-dd"), 4)

单元格 A2 是日期 2024/07/15：

TEXT 先把它变成 "2024-07-15" → LEFT 取前 4 位 → "2024"

若不用 TEXT：日期在 Excel 里本质是数字 45418，LEFT(45418, 4) → 得到 "4541"，完全不是年份！

② 从数字编码中提取（保留前导零）

=LEFT(TEXT(A2, "00000000000"), 3)

单元格 A2 是数字 20240715001（订单号存为数字）：

TEXT 先补齐 11 位 → "20240715001" → LEFT 取前 3 位 → "202"（分类码）

如果不用 TEXT：如果订单号是 0012 开头的数字，Excel 会自动去掉前导零变成 12，LEFT(12, 3) → "12"，位数都不对！

③ 从金额中提取角分

=RIGHT(TEXT(A2, "0.00"), 2)

单元格 A2 是金额 1234.5：

TEXT 先格式化成 "1234.50" → RIGHT 取末 2 位 → "50"（即 5 角 0 分）

❌ 如果不用 TEXT：1234.5 直接 RIGHT(1234.5, 2) → 得到 "4."，小数点都进来了，根本不是角分！

纯文本场景不需要 TEXT

如果数据本身就是文本（如身份证号、带字母的订单号），可以直接使用用 LEFT / RIGHT / MID ：

需求	公式（无需 TEXT）
身份证提取生日	`=MID(A2, 7, 8)`
手机号脱敏	`=LEFT(A2,3)&"****"&RIGHT(A2,4)`

组合（TEXT + 提取函数）

需求	公式
从日期提取年月	`=LEFT(TEXT(A2,"yyyymmdd"),6)`
数字编码取末4位	`=RIGHT(TEXT(A2,"000000"),4)`
金额取整数部分	`=LEFT(TEXT(A2,"0.00"),FIND(".",TEXT(A2,"0.00"))-1)`

三、场景二：金额大写

需求：合同、发票、报销单上需要「壹贰叁肆」大写金额。

Excel 本身没有直接转换大写金额的函数，但 TEXT 函数可以搭配格式代码 [DBNum2]来实现

标准公式：

=TEXT(数值, "[DBNum2]") & "元整"

完整版（带角分处理）：

=IF(A2=0,"",IF(A2<0,"负","")&TEXT(INT(ABS(A2)),"[DBNum2]")&"元"&IF(INT(ABS(A2))=ABS(A2),"整",TEXT(MOD(ABS(A2),1)*100,"[DBNum2]")&"分"))

输入 1234.56→ 输出：壹仟贰佰叁拾肆元伍角陆分

组合使用

格式代码	效果	适用场景
`[DBNum1]`	一二三四	中文小写
`[DBNum2]`	壹贰叁肆	财务标准
`[DBNum3]`	１２３４	全角数字

四、场景三：条件判断后输出文字

x取：成绩及不及格、绩效达不达标、库存够不够,这些「是或否」怎么自动显示文字？

TEXT 函数 + IF 函数

① 成绩判定

=IF(B2>=60, "及格", "不及格")

② 业绩分级

=IF(B2>=100000,"超额完成",IF(B2>=60000,"达标","未达标"))

③ 结合 TEXT 输出更人性化的提示

=IF(C2>0,"盈利"&TEXT(C2,"0.00")&"元","亏损"&TEXT(ABS(C2),"0.00")&"元")

盈利 5000→ 盈利5000.00元 亏损 3000→ 亏损3000.00元

TEXT 格式代码里直接写条件

=TEXT(E2,"[>=100000]🔥超额;[>=60000]✅达标;❌未达标")

一个公式搞定三档判断，不用嵌套 IF！

五、场景四：数据显示固定位数

需求：工号 7 要显示成 0000007，库存 5 要显示成 005，分数 0.85 要显示成 85.00%……

这里的TEXT 函数主要作用是格式化数字。

🔑 常用格式代码

格式代码	效果	用途
`0`	强制显示数字（无则补0）	通用
`0.00`	保留2位小数	金额
`000000`	共6位，不足补0	工号、订单号
`#,##0`	千分位分隔	大数字
`0.00%`	百分比	比率
`yyyy-mm-dd`	日期格式	日期

实战案例：

① 工号补0

=TEXT(A2, "0000000")

7 → 0000007

② 金额加千分位 + 保留2位

=TEXT(B2, "#,##0.00")

1234567.8 → 1,234,567.80

③ 比率转百分比

=TEXT(C2, "0.00%")

0.856 → 85.60%

④ 日期统一格式

=TEXT(D2, "yyyy年mm月dd日")

2024/7/15 → 2024年07月15日

⑤ 数字 + 单位组合

=TEXT(E2, "0.0") & "公斤"

5.3 → 5.3公斤

六、综合实战：一张报表搞定所有

若你有一张销售明细表，包含订单号、金额、销售员、状态四列，用下面五个公式做汇总展示：

需求	公式
订单号脱敏	`=LEFT(A2,3)&"****"&RIGHT(A2,3)`
金额大写	`=TEXT(B2,"[DBNum2]")&"元整"`
业绩判定	`=IF(B2>=100000,"超额",IF(B2>=60000,"达标","未达标"))`
金额千分位	`=TEXT(B2,"¥#,##0.00")`
达成率	`=TEXT(C2/B2,"0.0%")`

七、写在最后

TEXT 函数就像 Excel 里的化妆师：

想切一段？找 LEFT/RIGHT/MID配合想变大写？用 [DBNum2]想判一下？配 IF函数想统一格式？用格式代码 0 / 0.00 / 0.00%

本文公式基于 Microsoft Excel 2016 及以上版本，部分写法在 WPS 中同样适用。

❤️ 希望这篇文章对你有帮助！

觉得有用？点赞 + 在看+转发~