保留前导零和较大数字
- 2022-04-10
- 来源/作者： Wps Office教程网/ 菜鸟图库
- 215 次浏览
您是否曾经在 Excel 中导入或输入包含前导零 (如00123或 1234 5678 9087 6543 的大型数字) 的数据？例如社会保险号、电话号码、信用卡号、产品代码、帐号或邮政编码。Excel 将自动删除前导零, 并将大量数字转换为科学记数法 (如 1.23 e + 15), 以便允许公式和数学运算处理它们。本文介绍了如何保持数据的原始格式, Excel 将其视为文本。
导入文本数据时将数字转换为文本
较新版本 Office 2010-2013
在导入数据时, 使用 Excel 的Get & 转换 (Power Query)体验将各列的格式设置为文本。在这种情况下, 我们将导入文本文件, 但对于从其他源 (如 XML、Web、JSON 等) 导入的数据, 数据转换步骤是相同的。
-
单击 “数据” 选项卡, 然后单击 “获取数据” 按钮旁边的 “文本/CSV “。如果看不到 “获取数据” 按钮, 请转到 “新建查询>从文本>”, 然后浏览到您的文本文件, 然后按 “导入”。
-
Excel 会将数据加载到预览窗格中。在预览窗格中按 “编辑” 以加载查询编辑器。
-
如果需要将任何列转换为文本, 请通过单击列标题选择要转换的列, 然后转到 “开始>转换>”数据类型> 选择文本。
提示: 您可以通过Ctrl + 单击选择多个列。
-
下一步, 单击 “更改列类型” 对话框中的 “替换当前”, Excel 会将所选列转换为文本。
-
完成后, 单击 “关闭 & 加载”, Excel 会将查询数据返回到工作表。
如果未来的数据发生变化, 您可以转到 “数据>刷新”, Excel 将自动更新您的数据, 并为您应用转换。
在 Excel 2010 和2013中, 有两种方法可导入文本文件并将数字转换为文本。建议的方法是使用 power query, 这可在下载 Power query 加载项时使用。如果无法下载 Power Query 加载项, 则可以使用 “文本导入向导”。在这种情况下, 我们将导入文本文件, 但对于从其他源 (如 XML、Web、JSON 等) 导入的数据, 数据转换步骤是相同的。
-
单击功能区中的 ” Power Query ” 选项卡, 然后从文本中选择 “获取外部数据>”。
-
Excel 会将数据加载到预览窗格中。在预览窗格中按 “编辑” 以加载查询编辑器。
-
如果需要将任何列转换为文本, 请通过单击列标题选择要转换的列, 然后转到 “开始>转换>”数据类型> 选择文本。
提示: 您可以通过Ctrl + 单击选择多个列。
-
下一步, 单击 “更改列类型” 对话框中的 “替换当前”, Excel 会将所选列转换为文本。
-
完成后, 单击 “关闭 & 加载”, Excel 会将查询数据返回到工作表。
如果未来的数据发生变化, 您可以转到 “数据>刷新”, Excel 将自动更新您的数据, 并为您应用转换。
使用自定义格式保留前导零
如果你想要在工作簿中解决此问题, 因为它不是由其他程序用作数据源, 因此你可以使用自定义或特殊格式保留前导零。这适用于包含少于16位的数字代码。此外, 你可以用短划线或其他标点符号设置号码代码的格式。例如, 若要使电话号码更具可读性, 可以在国际代码、国家/地区代码、区域代码、前缀和最后几个数字之间添加短划线。
号码代码
示例
自定义数字格式
社交 债券
012345678
000-00-0000 012-34-5678
电话
0012345556789
00-0-000-000-0000 00-1-234-555-6789
邮政 区号
00123
00000 00123
步骤
-
选择要设置格式的单元格或单元格区域。
-
按Ctrl + 1加载 “设置单元格格式” 对话框。
-
选择 “数字” 选项卡, 然后在 “类别” 列表中, 单击 “自定义”, 然后在 “类型” 框中键入数字格式, 如000-00-0000表示社会保险号码代码, 或00000为五位数的邮政编码。
提示: 还可以单击“特殊”，然后选择“邮政编码”、“邮政编码 + 4”、“电话号码”或“社会保险号码”。
有关自定义代码的详细信息，请参阅创建或删除自定义数字格式。
注意: 这不会还原格式设置之前删除的前导零。它只会影响在应用格式后输入的数字。
使用 TEXT 函数应用格式
你可以使用数据旁边的空列, 然后使用TEXT 函数将其转换为所需的格式。
号码代码
示例 (在单元格 A1 中)
TEXT 函数和新格式
社交 债券
012345678
= 文本 (A1, “000-00-0000”) 012-34-5678
电话
0012345556789
= 文本 (A1, “00-0-000-0000”) 00-1-234-555-6789
邮政 区号
00123
= 文本 (A1, “00000”) 00123
向下舍入信用卡号码
Excel 的最大精度为15个有效位, 这意味着对于包含16个或更多位数的任何数字 (如信用卡号), 任何超过15位的数字都将舍入到零。对于16位或更大的数字代码, 必须使用文本格式。为此, 您可以执行以下两项操作之一:
-
将列的格式设置为文本
选择数据区域, 然后按Ctrl + 1以启动 “>单元格格式” 对话框。在 “数字” 选项卡上, 单击 “文本”。
注意: 这将不会更改已输入的数字。它只会影响在应用格式后输入的数字。
-
使用撇号字符
您可以在数字前面键入撇号 (‘), Excel 会将其视为文本。
返回页首
需要更多帮助吗？
可随时在 Excel 技术社区中咨询专家，在解答社区获得支持，或在 Excel User Voice 上建议新功能或功能改进。
