你遇到的情况The Symptom You See

在 Excel 或表格软件里打开一份 CSV,18 位身份证号、11 位手机号、19 位银行卡号突然变成 1.2345E+17 这样的"科学计数法",而且更糟的是:一旦保存,后面的位数(比如身份证号末尾的校验码)就真的丢了,再也还原不回来。这是 CSV / Excel 用户最高频的痛点之一。

You open a CSV in Excel or another spreadsheet, and an 18-digit ID, 11-digit phone number or 19-digit bank card number suddenly becomes something like 1.2345E+17 — scientific notation. Worse: once saved, the trailing digits (e.g. the ID checksum) are really gone and can't be restored. It's one of the most common CSV/Excel headaches.

为什么会这样Why It Happens

表格软件看到一串很长的纯数字,会"自作主张"把它当成数值(数字类型)。超过约 11 位后,它就用科学计数法显示以节省空间;而浮点精度只有约 15 位有效数字,超出部分会被截断。CSV 本身只是文本,问题出在打开方式——软件把整列按"常规"格式解析成了数字。

Spreadsheets see a long string of digits and "helpfully" treat it as a numeric value. Beyond roughly 11 digits they display it in scientific notation to save space, and floating-point precision tops out around 15 significant digits — anything beyond is truncated. CSV is just text; the problem is the way it's opened: the software parses the whole column as numbers.

方法一:把列设为"文本"再导入(最稳妥)Method 1: Set the Column to "Text" Before Import (Safest)

不要让软件自动判断类型,而是主动指定该列为文本:

Don't let the software guess — declare the column as text yourself:

  • 在 Excel 里用 数据 → 从文本/CSV 导入(而不是双击打开)。
  • 进入"分列"向导,第 3 步把目标列的格式选为 "文本"。
  • 在 Google Sheets 导入时,把该列格式设为 "纯文本"。详见 CSV 导入 Google Sheets。
  • In Excel use Data → From Text/CSV (not a double-click open).
  • In the "Text to Columns" wizard, step 3 sets the target column format to "Text".
  • In Google Sheets import, set that column to "Plain text". See Import CSV into Google Sheets.

方法二:CSV 里给数字加前置单引号Method 2: Prefix the Number with a Single Quote in the CSV

在 CSV 中,把长数字写成 '11010519491231002X(前面加一个英文单引号)。表格软件会把这一格当作文本,单引号本身不会显示,也不会参与计算。适合已经生成好的 CSV 手动修补少数行。

In the CSV, write the long number as '11010519491231002X (leading single quote). The spreadsheet treats the cell as text; the quote itself isn't displayed and isn't used in calculations. Good for patching a few rows in an existing CSV by hand.

方法三:用等号把数字写成公式文本(批量可行)Method 3: Use an Equals Sign to Write It as Text (Bulk-Friendly)

在 CSV 单元格里写成 ="11010519491231002X"(等号加双引号包裹)。表格软件会把它当作"返回文本的公式",既保留全部位数,又能被正常复制。注意导出时要确认软件没有把等号当作公式二次计算。

Write the cell as ="11010519491231002X" (equals sign wrapping the value in double quotes). The spreadsheet treats it as "a formula returning text" — all digits are preserved and it copies cleanly. Just confirm your export tool doesn't re-evaluate the equals sign as a formula.

关键提醒:一旦在 Excel 里以"常规"格式打开并保存,被截断的位数已永久丢失,只能回到原始 CSV 重新用文本方式导入。所以"预防"远比"补救"重要。关于日期与数字的更多格式坑,参见 CSV 日期与数字格式处理。
Key reminder: once opened as "General" and saved in Excel, the truncated digits are permanently lost — you must go back to the original CSV and re-import as text. Prevention beats repair. For more date/number pitfalls, see CSV Date & Number Formatting.

用 365Lab 在线工具,从源头避开Avoid It at the Source with 365Lab

与其每次和 Excel 斗智斗勇,不如在生成 CSV 时就处理好:用 365Lab 在线 CSV 工具 在浏览器本地对"身份证号/手机号"这类列先做文本化或加前置单引号,再导出干净的 CSV。整个过程数据不上传,既安全又能保证位数一个不丢。需要理解更底层的格式规则,可先看 CSV 与 Excel 的区别 与 用 Excel / 表格软件打开 CSV。

Instead of fighting Excel every time, handle it when the CSV is born: with the 365Lab CSV toolkit you can "textify" or prefix-quote ID/phone columns locally in your browser, then export a clean CSV — private, and not a single digit lost. For the deeper format rules, start with CSV vs Excel and Opening CSV in Excel / Sheets.

立刻保住你的长数字:打开在线工具,把身份证号、手机号列设为文本后再导出 CSV。

Keep your long numbers now: open the toolkit and set ID/phone columns to text before exporting the CSV.

打开 CSV 在线工具 →Open the CSV Toolkit →