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 函数更改格式,但这不是唯一的方法。 可以通过在 Mac) 上按 CTRL+1 (或 +1 来更改格式,然后从“ 设置单元格 > 数字 格式”对话框中选取所需的格式。
下载我们的示例
可下载一个示例工作簿以及一些附加信息,该工作簿包含本文中可查到的所有 TEXT 函数示例。 可执行相同操作,或自行创建 TEXT 函数格式代码。下载 Excel TEXT 函数示例
还提供其他格式代码
可以使用“ 设置单元格格式 ”对话框查找其他可用的格式代码:
-
在 Mac) 按 Ctrl+1 (+1 打开“ 设置单元格格式 ”对话框。
-
从“数字”选项卡中选择所需格式。
-
选择 “自定义 ”选项。
-
“类型”框中即会显示所需的格式代码。 本例中,选择“类型”框中的所有内容,分号 (;) 和 @ 符号除外。 在下例中,我们仅选择和复制 yy/mm/dd。
-
按 Ctrl+C 复制格式代码,然后按 “取消” 关闭“ 设置单元格格式 ”对话框。
-
现在只需按 Ctrl+V 将格式代码粘贴到 TEXT 公式中,例如:=TEXT(B2,"yy/mm/dd")。 请确保将格式代码粘贴到引号中, (“格式代码”) ,否则 Excel 将显示错误消息。
按类别划分格式代码
下面是一些示例,说明如何使用“ 设置单元格 格式”对话框将不同的数字格式应用于值,然后使用 “自定义” 选项将这些 格式代码 复制到 TEXT 函数。
常见方案
TEXT 函数很少单独使用,通常与其他内容配合使用。 假设你想合并文本和数值,如“报告打印日期:12/03/14”或“每周收入: $66,348.72”。 你可将此内容手动键入 Excel,但这违背了让 Excel 执行此操作的目的。 遗憾的是,在合并文本和带格式的数字(如日期、时间、货币等)时,Excel 不知道你所希望的显示方式,因此会省去数字格式。 这便是 TEXT 函数的价值所在,因为它能使用“格式代码”(如日期格式“YY/MM/DD”)强制 Excel 按照你所希望的方式设置值的格式。
以下示例将显示不使用 TEXT 函数合并文本和数字的结果。 本例中,我们使用与号 (&) 连接一个文本字符串、一个空格 (" ") 和一个带 =A2&" "&B2 的值。
正如你看到的,Excel 删除了 B2 单元格中的日期格式。 下一示例将展示 TEXT 函数如何使你应用所需的格式。
更新的公式为:
-
Cell C2:=A2&" "&TEXT(B2,"mm/dd/yy") - Date format
常见问题
遗憾的是,不能使用 TEXT 函数执行此操作;需要使用 Visual Basic for Applications (VBA) 代码。 以下链接具有一种方法: 如何在 Excel 中将数值转换为英语单词。
可以,但是需要几个步骤。 首先,选择希望发生这种情况的单元格,并使用 Ctrl+1 打开“设置 > 单元格格式”对话框,然后使用“对齐 > 文本”控件 > 检查“环绕文本”选项。 接下来,调整已完成的 TEXT 函数,使其包括你希望具有换行符的 ASCII 函数 CHAR(10)。 可能需要根据最终结果的对齐方式调整列宽。
本例中,我们使用: ="Today is: "&CHAR(10)&TEXT(TODAY(),"mm/dd/yy")
这称为 “科学表示法”,如果单元格 () 格式设置为 “常规”,则 Excel 会自动转换超过 12 位的数字;如果单元格 () 格式设置为 数字,则 Excel 会自动转换 15 位数字。 如果需要输入较长的数字字符串,但不希望将其转换,请在将值输入或粘贴到 Excel 之前,将问题单元格的格式设置为 “文本 ”。
Mynda 建议... 如果与来自不同国家/地区的用户共享 Excel 文件和报表,建议你以他们的语言为他们提供报表。 Excel 最有价值专家 Mynda Treacy在以不同语言显示的 Excel 日期一文中提供出色的解决方案。 它还包括可供你下载的示例工作簿。 |