在 Excel 與 Google Sheets 中保留前導零

Which versions this applies to

The results below come from Google Sheets on November 12, 2026. TEXT, VALUE, LEN, ISTEXT, and ISNUMBER exist in Excel as well, per Microsoft’s documentation. I did not re-test these cells in desktop Excel today, and I did not test a custom number format (a display-only setting) or the typing trick of starting a cell with an apostrophe.

A number cannot keep a leading zero

When the value is a real number, the zeros on the left are not part of it. I stored 1 in B2 (the value you are left with if you enter 001 as a number):

=ISNUMBER(B2)    → TRUE
=ISTEXT(B2)      → FALSE
=LEN(B2)         → 1

LEN is looking at the number as text only long enough to count characters, and there is only one: 1. There is no hidden 00 in front.

Store the code as text

I typed 001 as text in B3, and I also built it with a formula:

=ISTEXT(B3)     → TRUE
=LEN(B3)        → 3
=VALUE(B3)      → 1
="001"          → 001   (ISTEXT TRUE, LEN 3, VALUE 1)

The text form keeps three characters, including the zeros. VALUE is the switch that turns it back into the number 1. Use the text form when the zeros are part of a code (employee 007, SKU 001). Use VALUE only when you deliberately want a number.

The same pattern showed up on 007: LEN was 3 and VALUE returned 7.

Pad a number for display with TEXT

If the underlying value is already a number, TEXT can emit a zero-padded string:

=TEXT(B2,"000")           → 001
=ISTEXT(TEXT(B2,"000"))   → TRUE

The result is text, not a number. That is what you want for a code you are going to concatenate or export. It is not what you want if the next formula needs to add or average the values; in that case keep a numeric column and pad only at the edge.

A custom number format can also display 001 while leaving the value as 1. I did not set a custom format in this test, so I am not quoting a format-code result. The TEXT formula above is the version I actually ran.

SPLIT will strip the zeros

B4 held the text 001,002. The first piece after a comma split was not text 001:

=INDEX(SPLIT(B4,","),1,1)                    → 1
=ISNUMBER(INDEX(SPLIT(B4,","),1,1))          → TRUE

Sheets coerced the piece to a number and the zeros disappeared. That gotcha is covered in more detail in Split Text by Delimiter. If you need the padded codes after a split, pad them with TEXT afterwards, or extract with LEFT/MID so the source stays text.

Going the other way — a number stored as text that you do want as a number — is Convert Numbers Stored as Text.

Excel and Google Sheets differences

The functions I used (TEXT, VALUE, LEN, ISTEXT, ISNUMBER) are in both products, according to Microsoft. SPLIT is Sheets-only; the coercion to a number is a Sheets result from this file. I did not re-run these cells in Excel today, and I did not test Excel’s apostrophe prefix or a custom format.

Common mistakes

• Entering 001 as a number and expecting LEN to be 3. Against the number 1, LEN was 1.
• Running VALUE on a code you still need padded. VALUE("001") was 1.
• Splitting a comma-separated list of padded codes and trusting the first cell to stay 001. It became 1, and ISNUMBER was TRUE.
• Treating TEXT(1,"000") as a number. ISTEXT was TRUE; it is a string you can export, not something to average.

FAQ

How do I keep leading zeros on a code like 001?

Store it as text ("001" in this test had LEN 3 and ISTEXT TRUE), or build it from a number with TEXT(n,"000"), which returned the text 001.

Why did 001 become 1 after I used the value in a formula?

VALUE("001") returned 1. SPLIT of "001,002" also returned the number 1 for the first piece. Anything that coerces the text to a number will drop the zeros.

How do I pad a number to three digits?

=TEXT(1,"000") returned 001, and ISTEXT of that result was TRUE. Change the number of zeros in the format string to change the width.

Can I type an apostrophe in front of 001 to keep the zeros?

That is a common data-entry trick in Excel. I did not test typing an apostrophe into a cell in this run, so I am not quoting a result for it. A formula that concatenates an apostrophe character produced the visible text '001, which is a different thing: the apostrophe is then part of the value.

Will TEXT still work in Excel?

Microsoft documents the TEXT function for Excel. I did not re-test TEXT(1,"000") in desktop Excel today.

Related tutorials

• Convert Numbers Stored as Text in Excel and Google Sheets
• Split Text by Delimiter in Excel and Google Sheets
• Extract Numbers from Text in Excel and Google Sheets

Live-test findings

Google Sheets, November 12, 2026. Number 1: ISNUMBER TRUE, ISTEXT FALSE, LEN 1. Text "001": ISTEXT TRUE, LEN 3, VALUE 1. Formula ="001": same three results. TEXT(1,"000") returned 001 with ISTEXT TRUE. INDEX(SPLIT("001,002",","),1,1) returned 1 with ISNUMBER TRUE. Text "007" had LEN 3 and VALUE 7.

Sources

• Microsoft Support: TEXT function: https://support.microsoft.com/en-us/office/text-function-20d5ac4d-7b94-49fd-bb38-93d29371225c
• Microsoft Support: VALUE function: https://support.microsoft.com/en-us/excel/functions/value-function
• Microsoft Support: LEN function: https://support.microsoft.com/en-us/excel/functions/len-function
• Google Docs Editors Help: TEXT: https://support.google.com/docs/answer/3094139
• Google Docs Editors Help: VALUE: https://support.google.com/docs/answer/3094220