TEXT 函数

参与者: Chris "Smitty" Smith Dave Ludwig

TEXT 函数可通过格式代码向数字应用格式,进而更改数字的显示方式。如果要按更可读的格式显示数字,或者将数字与文本或符号组合,它将非常有用。

注意: TEXT 函数会将数字转换为文本,这可能使其在以后的计算中难以引用。最好将原始值保存在一个单元格中,然后在另一单元格中使用 TEXT 函数。随后如果需要构建其他公式,请始终引用原始值,而不是 TEXT 函数结果。

语法

TEXT(value, format_text)

TEXT 函数语法具有下列参数:

参数名称

说明

value

要转换为文本的数值。

format_text

一个文本字符串,定义要应用于所提供值的格式。

概述

TEXT 函数最简单的形式表示:

  • =TEXT(Value you want to format, "Format code you want to apply")

下面是一些常用示例,可将其直接复制到 Excel​​ 自行进行试验。请注意引号内的格式代码。

公式

说明

=TEXT(1234.567,"$#,##0.00")

货币带有 1 个千位分隔符和 2 个小数,如 $1,234.57。请注意,Excel 将该值四舍五入到小数点后两位。

= TEXT(TODAY(),"MM/DD/YY")

目前日期采用 YY/MM/DD 格式,如 12/03/14

=TEXT(TODAY(),"DDDD")

一周中的当天,如周日

=TEXT(NOW(),"H:MM AM/PM")

当前时间,如下午 1:29

=TEXT(0.285,"0.0%")

百分比,如 28.5%

=TEXT(4.34 ,"# ?/?")

分数,如 4 1/3

=TRIM(TEXT(0.34,"# ?/?"))

分数,如 1/3。注意,这将使用 TRIM 函数删除带十进制值的前导空格。

=TEXT(12200000,"0.00E+00")

科学记数法,如 1.22E+07

=TEXT(1234567898,"[<=9999999]###-####;(###) ###-####")

特殊(电话号码),如 (123) 456-7898

=TEXT(1234,"0000000")

添加前导零 (0),如 0001234

=TEXT(123456,"##0° 00' 00''")

自定义 - 纬度/经度

注意: 虽然可使用 TEXT 函数更改格式,但这不是唯一的方法。不使用公式也可更改格式,方法是:按 CTRL+1(或在 Mac 上按 MAC 命令按钮图标的图像 +1),然后从“格式单元格”>“数字”对话框中选择所需格式。

下载我们的示例

可下载一个示例工作簿以及一些附加信息,该工作簿包含本文中可查到的所有 TEXT 函数示例。可执行相同操作,或自行创建 TEXT 函数格式代码。

下载 Excel TEXT 函数示例

还提供其他格式代码

可使用“格式单元格”对话框查找其他可用的格式代码:

  1. Ctrl+1(在 Mac 上按 MAC 命令按钮图标的图像 +1,调出“格式单元格”对话框。

  2. 从“数字”选项卡中选择所需格式。

  3. 选择“自定义”选项,

  4. 类型”框中即会显示所需的格式代码。本例中,选择“类型”框中的所有内容,分号 (;) 和 @ 符号除外。在下例中,我们仅选择和复制 yy/mm/dd。

  5. Ctrl+C 复制格式代码,然后按“取消”关闭“格式单元格”对话框。

  6. 现在只需按 Ctrl+V 将格式代码粘贴到 TEXT 公式中,例如:=TEXT(B2,"yy/mm/dd")。请确保粘贴格式代码时随附引号(即格式为“格式代码”),否则 Excel 将显示一条错误消息。

相关示例,显示如何使用“格式”>“单元格”>“数字”>“自定义”对话框让 Excel 代你创建格式字符串。

按类别划分格式代码

以下示例介绍了可如何向值应用不同的数字格式,即使用“格式单元格”对话框,然后使用“自定义”选项将这些“格式代码”复制到 TEXT 函数。

Excel 为何删除了前导零?

Excel 可查找当前输入单元格的数字,而不是看似文本的数字,如部件号或 SKU 编号。若要保留前导零,请在粘贴或输入值前将输入范围的格式设置为“文本”。选择要在其中输入值的列或范围,然后按 CTRL+1 调出“格式”>“单元格”对话框,并在“数字”选项卡上选择“文本”。现在,Excel 将会保留前导零。

如果已输入数据且 Excel 已删除前导零,可使用 TEXT 函数重新添加。可引用顶部单元格中的值,并使用 =TEXT(value,"00000")(公式中 0 的数量表示所需字符的总数),然后复制粘贴到范围内的其余位置。

使用 TEXT 格式化前导零的示例。  =TEXT(A2,"00000")

如果由于某种原因需要将文本值转换回数字,可乘以 1,如 =D4*1,或使用双一元运算符 (--),如 =-D4

如果格式中包含由数字符号 (#) 或零包围的逗号 (,),则 Excel 将用逗号分隔千位。例如,如果格式字符串是 "#,###",Excel 会将数字 12200000 显示为 12,200,000。

数字占位符后面的逗号将数字缩小 1000 倍。例如,如果格式字符串是 "#,###.0,",则 Excel 会将数字 12200000 显示为 12,200.0。

使用千位分隔符的 TEXT 函数示例

注释: 

  • 千位分隔符取决于你的区域设置。美国区域设置中的千位分隔符是一个逗号,但在其他区域设置中可能是句点 (.)。

  • 千位分隔符适用于数字、货币和会计格式。

以下是标准数字(仅用千位分隔符和小数)、货币和会计格式的示例。货币格式中允许插入你选择的货币符号,并将其在值旁边对齐,而会计格式会将货币符号与单元格左侧对齐,将值与右侧对齐。请注意以下货币和会计格式代码之间的差异,其中会计格式使用星号 (*) 分隔符号和值。

带数字、货币和会计格式的 TEXT 函数示例

若要查找货币符号的格式代码,请先按 Ctrl+1(或在 Mac 上按 MAC 命令按钮图标的图像 +1,选择所需格式,然后从“符号”下拉列表中选择符号:

“设置单元格格式”对话框中的货比符号选项

然后单击“类别”部分左侧的“自定义”,然后复制货币符号等格式代码。

TEXT 函数 - 自定义带符号的货币

注意: TEXT 函数不支持颜色格式,因此,如果从包括颜色的“格式单元格”中复制数字格式代码,如:$#,##0.00_);[Red]($#,##0.00),TEXT 函数将接受该格式代码,但不会显示颜色。

可混合使用“M”代表月、“D”代表日期以及“Y”代表年来更改日期的显示方式。

年、月和日的格式代码

TEXT 函数中的格式代码不区分大小写,因此可使用用“M”或“m”、“D”或“d”、“Y”或“y”。

Excel MVP Mynda Treacy

Mynda 建议...

如果与来自不同国家/地区的用户共享 Excel 文件和报表,建议你以他们的语言为他们提供报表。Excel 最有价值专家 Mynda Treacy以不同语言显示的 Excel 日期一文中提供出色的解决方案。它还包括可供你下载的示例工作簿。

可修改时间的显示方式,方法是混合使用“H”代表小时、“M”代表分钟或“S”代表秒,以及用“AM/PM”表示 12 小时制。

时、分和秒的时间格式

如果遗漏了“AM/PM”或“A/P”,时间则会按 24 小时制显示。

TEXT 函数中的格式代码不区分大小写,因此可使用“H”或“h”、“M”或“m”、“S”或“s”、“AM/PM”或“am/pm”。

可使用百分比 (%) 格式更改小数值的显示方式。

百分比的格式代码

可使用分数 (?/?) 格式更改小数值的显示方式。

分数的格式代码

科学记数法是一种以 1 和 10 之间的小数乘以 10 的幂的方式显示数字的方式。通常用于缩短较大数字的显示方式。

科学记数法的格式代码

Excel 提供了 4 种特殊格式:

  • 邮政编码 - "00000"

  • 邮政编码 + 4 - "00000-0000"

  • 电话号码 - "[<=9999999]###-####;(###) ###-####"

  • 社会安全号码 - "000-00-0000"

TEXT 函数的特殊格式

特殊格式因区域设置而异,但是如果你的区域设置没有任何特殊格式或者它们不能满足你的需求,可通过“格式单元格”>“自定义”对话框自行创建区域设置。

常见方案

TEXT 函数很少单独使用,通常与其他内容配合使用。假设你想合并文本和数值,如“报告打印日期:12/03/14”或“每周收入: $66,348.72”。你可将此内容手动键入 Excel,但这违背了让 Excel 执行此操作的目的。遗憾的是,在合并文本和带格式的数字(如日期、时间、货币等)时,Excel 不知道你所希望的显示方式,因此会省去数字格式。这便是 TEXT 函数的价值所在,因为它能使用“格式代码”(如日期格式“YY/MM/DD”)强制 Excel 按照你所希望的方式设置值的格式。

以下示例将显示不使用 TEXT 函数合并文本和数字的结果。本例中,我们使用与号 (&) 连接一个文本字符串、一个空格 (" ") 和一个带 =A2&" "&B2 的值。

不使用 TEXT 函数连接文本的示例

正如你看到的,Excel 删除了 B2 单元格中的日期格式。下一示例将展示 TEXT 函数如何使你应用所需的格式。

使用 TEXT 函数连接文本的示例

更新的公式为:

  • Cell C2:=A2&" "&TEXT(B2,"mm/dd/yy") - Date format

常见问题

遗憾的是,无法使用 TEXT 函数完成这一操作,你需要使用 Visual Basic for Applications (VBA) 代码。也可参阅以下链接中的方法:如何在 Excel 中将数值转换为英文单词

可以,可使用 UPPERLOWERPROPER 函数。例如,=UPPER("hello") 会返回“HELLO”。

可以,但是需要几个步骤。首先,选择要执行此操作的一个或多个单元格,按 Ctrl+1 调出“格式”>“单元格”对话框,然后在“对齐”>“文本控制”中勾选“自动换行”选项。接下来,调整已完成的 TEXT 函数,使其包括你希望具有换行符的 ASCII 函数 CHAR(10)。可能需要根据最终结果的对齐方式调整列宽。

使用 TEXT 和 CHAR (10) 插入换行符的示例。 ="Today is:"&CHAR(10))&TEXT(TODAY(),"MM/DD/YY")

本例中,我们使用: ="Today is: "&CHAR(10)&TEXT(TODAY(),"mm/dd/yy")

这称为科学记数法。如果单元格的格式设置为“常规”,Excel 会自动转换超过 12 位的数字,如果如果单元格的格式设置为“数字”,则会自动转换超过 15 位的数字。如果需要输入较长的数字字符串但不希望转换,则在将值输入或粘贴到 Excel 之前,先将所涉及的单元格的格式设置为“文本”。

Excel MVP Mynda Treacy

Mynda 建议...

如果与来自不同国家/地区的用户共享 Excel 文件和报表,建议你以他们的语言为他们提供报表。Excel 最有价值专家 Mynda Treacy以不同语言显示的 Excel 日期一文中提供出色的解决方案。它还包括可供你下载的示例工作簿。

另请参阅

创建或删除自定义数字格式

将文本转换为数字

所有 Excel 函数(按类别)

扩展你的技能
了解培训
抢先获得新功能
加入 Office 预览体验计划

此信息是否有帮助?

谢谢您的反馈!

谢谢你的反馈! 可能需要转接到 Office 支持专员。

×