保留前导零和大数

您是否曾经在 Excel 中导入或输入包含前导零(如 00123)或大数(如 1234 5678 9087 6543)的数据? 例如,社会安全号码、电话号码、信用卡号、产品代码、帐号或邮政编码。 Excel 会自动删除前导零,并将大数转换为科学表示法(如 1.23E+15)以便使用公式和数学运算。 本文处理如何将数据保留为 Excel 视为文本的原始格式。

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

在导入数据时 & Excel (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

=TEXT (A1,"000-00-0000")
012-34-5678

手机

0012345556789

=TEXT (A1,"00-0-000-000-0000")
00-1-234-555-6789


邮政 代码

00123

=TEXT (A1,"00000")
00123

信用卡号向下舍入

Excel 的最大精度为 15 个有效数字,这意味着对于包含 16 位或 16 位以上数字的任何数字(如信用卡号码)来说,超过第 15 位的任何数字将向下舍入到零。 对于 16 位或更大的数字代码,必须使用文本格式。 为此,可以执行两项操作之一:

  • 将列的格式设置为文本

    选择数据区域,然后按Ctrl+1启动"设置单元格>格式"对话框。 在"数字"选项卡上,单击"文本"。

    注意: 这不会更改已输入的数字。 它仅影响应用格式后输入的数字。

  • 使用撇号字符

    可以在数字前面键入 (") 撇号,Excel 会视为文本。

返回页首

需要更多帮助吗?

可随时在 Excel 技术社区中咨询专家,在解答社区获得支持,或在 Excel User Voice 上建议新功能或功能改进。

需要更多帮助?

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

此信息是否有帮助?

谢谢您的反馈!

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

×