保留前导零和较大数字

您是否曾经在 Excel 中导入或输入包含前导零 (如00123或 1234 5678 9087 6543 的大型数字) 的数据?例如社会保险号、电话号码、信用卡号、产品代码、帐号或邮政编码。Excel 将自动删除前导零, 并将大量数字转换为科学记数法 (如 1.23 e + 15), 以便允许公式和数学运算处理它们。本文介绍了如何保持数据的原始格式, Excel 将其视为文本。

导入文本数据时将数字转换为文本

较新版本 Office 2010-2013 

在导入数据时, 使用 Excel 的Get & 转换 (Power Query)体验将各列的格式设置为文本。在这种情况下, 我们将导入文本文件, 但对于从其他源 (如 XML、Web、JSON 等) 导入的数据, 数据转换步骤是相同的。

  1. 单击 “数据” 选项卡, 然后单击 “获取数据” 按钮旁边的 “文本/CSV “。如果看不到 “获取数据” 按钮, 请转到 “新建查询>从文本>”, 然后浏览到您的文本文件, 然后按 “导入”。

  2. Excel 会将数据加载到预览窗格中。在预览窗格中按 “编辑” 以加载查询编辑器。

  3. 如果需要将任何列转换为文本, 请通过单击列标题选择要转换的列, 然后转到 “开始>转换>”数据类型> 选择文本。

    Power Query-转换为文本后的数据

    提示: 您可以通过Ctrl + 单击选择多个列。

  4. 下一步, 单击 “更改列类型” 对话框中的 “替换当前”, Excel 会将所选列转换为文本。

    获取 & 转换 > 将数据转换为文本

  5. 完成后, 单击 “关闭 & 加载”, Excel 会将查询数据返回到工作表。

    如果未来的数据发生变化, 您可以转到 “数据>刷新”, Excel 将自动更新您的数据, 并为您应用转换。

在 Excel 2010 和2013中, 有两种方法可导入文本文件并将数字转换为文本。建议的方法是使用 power query, 这可在下载 Power query 加载项时使用。如果无法下载 Power Query 加载项, 则可以使用 “文本导入向导”。在这种情况下, 我们将导入文本文件, 但对于从其他源 (如 XML、Web、JSON 等) 导入的数据, 数据转换步骤是相同的。

  1. 单击功能区中的 ” Power Query ” 选项卡, 然后从文本中选择 “获取外部数据>”。

  2. Excel 会将数据加载到预览窗格中。在预览窗格中按 “编辑” 以加载查询编辑器。

  3. 如果需要将任何列转换为文本, 请通过单击列标题选择要转换的列, 然后转到 “开始>转换>”数据类型> 选择文本。

    Power Query-转换为文本后的数据

    提示: 您可以通过Ctrl + 单击选择多个列。

  4. 下一步, 单击 “更改列类型” 对话框中的 “替换当前”, Excel 会将所选列转换为文本。

    获取 & 转换 > 将数据转换为文本

  5. 完成后, 单击 “关闭 & 加载”, Excel 会将查询数据返回到工作表。

    如果未来的数据发生变化, 您可以转到 “数据>刷新”, Excel 将自动更新您的数据, 并为您应用转换。

使用自定义格式保留前导零

如果你想要在工作簿中解决此问题, 因为它不是由其他程序用作数据源, 因此你可以使用自定义或特殊格式保留前导零。这适用于包含少于16位的数字代码。此外, 你可以用短划线或其他标点符号设置号码代码的格式。例如, 若要使电话号码更具可读性, 可以在国际代码、国家/地区代码、区域代码、前缀和最后几个数字之间添加短划线。

号码代码

示例

自定义数字格式

社交 债券

012345678

000-00-0000 012-34-5678

电话

0012345556789

00-0-000-000-0000 00-1-234-555-6789

邮政 区号

00123

00000 00123

步骤   

  1. 选择要设置格式的单元格或单元格区域。

  2. 按Ctrl + 1加载 “设置单元格格式” 对话框。

  3. 选择 “数字” 选项卡, 然后在 “类别” 列表中, 单击 “自定义”, 然后在 “类型” 框中键入数字格式, 如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 上建议新功能或功能改进。

office办公软件入门基础教程 » 保留前导零和较大数字

本文链接:【保留前导零和较大数字】https://www.officeapi.cn/68285.html

OFFICE,天天学一点,进步多一点

联系我们