Applies ToMicrosoft 365 专属 Excel Excel 2024 Excel 2021 Excel 2019 Excel 2016 Excel 2013

是否在 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 将自动更新数据并为你应用转换。

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

如果想解决工作簿中的问题,因为其他程序未将其用作数据源,则可以使用自定义或特殊格式来保留前导零。 这适用于包含少于 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 位或更多位的任何数字(例如信用卡数字),超过第 15 位的任何数字将向下舍入为零。 如果数字代码为 16 位或更大,则必须使用文本格式。 为此,可以执行以下两项操作之一:

  • 将列的格式设置为文本

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

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

  • 使用撇号字符

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

返回页首

需要更多帮助吗?

可随时在 Excel 技术社区中咨询专家或在社区中获取支持。

需要更多帮助?

需要更多选项?

了解订阅权益、浏览培训课程、了解如何保护设备等。

社区可帮助你提出和回答问题、提供反馈,并听取经验丰富专家的意见。