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

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

使用Excel Power Query &转换 (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 个数字的数字代码。 此外,可以使用短划线或其他标点符号设置数字代码的格式。 例如,若要使电话号码更具可读性,可以在国际代码、国家/地区代码、区号、前缀和最后几个号码之间添加短划线。

数字代码

示例

自定义数字格式

社交
security

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 函数和新格式

社交
security

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 技术社区中咨询专家或在 Answers 社区获取

需要更多帮助?

扩展你的技能
了解培训

此信息是否有帮助?

你对翻译质量的满意程度如何?
哪些因素影响了你的体验?

谢谢您的反馈意见!

×