文本函数

TEXT 函数

本文介绍 Microsoft Excel 中 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 ），然后选择所需的格式的单元格格式>数字对话框。

下载我们的示例

您可以下载的所有文本函数示例，您将在本文中，找到 plus 一些额外内容的示例工作簿。您可以跟上进度，或创建您自己的文本函数格式代码。下载 Excel 文本函数示例

还提供其他格式代码

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

  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 将保留您前导 0 的。

如果已输入数据且 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”。

可修改时间的显示方式，方法是混合使用“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函数很少使用本身和最常与其他内容配合使用。假设您要合并文本和数字的值，例如"报告打印： 03/14/12"，或"每周收入: $66,348.72"。您可以键入的导入到 Excel 手动，但该破坏了遇到 Excel 的目的帮您。遗憾的是，当您将组合文本和格式的数字，如日期、 时间、 货币等，Excel 不知道所需来显示它们，以便它降低数字格式。这是其中TEXT函数是非常重要，因为它允许您以强制 Excel 的值设置格式使用格式代码，如"MM/DD/YY"日期格式所需的方式。

在下面的示例中，您将看到会发生什么情况如果您尝试在不使用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 中的一个数值

可以，可使用 UPPER、LOWER和PROPER 函数。例如，=UPPER("hello") 会返回“HELLO”。

是的但它需要执行一些步骤。首先，选择单元格希望它发生，并使用Ctrl + 1弹出格式 > 单元格对话框，然后对齐 > 文本控件> 选中自动换行选项。接下来，调整您已完成的文本函数来加入 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 之前，先将所涉及的单元格的格式设置为“文本”。