Retain zeros in a cell

Question

Retain zeros in a cell

Louise Wooding 0

How do I enter a number but retain the zeros at the beginning? The numbers will all be different lengths - I thought of creating a custom number but can't get it to work. TIA

2 answers

Your answer

Answer 1

HansV 462K MVP Volunteer Moderator

If these values are primarily identifiers, such as product numbers, you should store them as text instead of as numbers. You can do this by formatting the cells as Text before entering the values, or by prefixing the values with an apostrophe '. The apostrophe will be displayed in the formula bar but not in the cell itself.

Louise Wooding 0 Reputation points

2025-08-27T15:03:05.1066667+00:00

We need them to be stored as *numbers [edit] in case we need to use them in VLookUps. How could we do that, but not using the apostrophe? Thank you
Barry Schwarz 3,916 Reputation points

2025-08-27T15:58:51.85+00:00
To force the values to text:

Select the cells where you are about to enter the values.

Hold the Ctrl key down, press 1, and release the Ctrl key.

In the resulting Format Cells display, select the Numbers tab.

Under Category select Custom.

In the Type field to the right, backspace over the current contents and enter 00...00 with as many zeros as you want.

Click OK.

Enter your data in these cells.

Each value you enter will be padded on the left with enough zeros to bring the total number of characters up to the number of zeros you specified in the format.

BTW, VLOOKUP works fine with numbers.
HansV 462K Reputation points MVP Volunteer Moderator

2025-08-27T16:00:36.3866667+00:00

Format the cells as Text before entering the values:
Louise Wooding 0 Reputation points

2025-08-28T17:13:33+00:00

sorry - i typed it wrong into my question. I need the to be stored as numbers, not text. Any ideas?
HansV 462K Reputation points MVP Volunteer Moderator

2025-08-28T17:24:45.69+00:00

If you really need the values to be stored as numbers and still preserve leading zeros as entered, you would have to assign an individual custom number format to each cell. That would be a lot of work, and as far as I know VBA code would not help, so it'd be all manual.

So I strongly recommend going with Text format. It should be possible to set up your VLOOKUP or XLOOKUP to work with that.

Answer 2

Sting-Ng 3,810 Microsoft External Staff Moderator

Hi @Louise Wooding

Welcome to the Q&A Community

I’m here to assist with your problem.

To retain leading zeros when entering numbers of varying lengths in Excel (while storing them as text for VLOOKUP compatibility, without using an apostrophe), follow these steps:

Select the cell(s) or range where you'll enter the data.
Right-click and choose Format Cells (or press Ctrl+1 on Windows / Cmd+1 on Mac).
In the Format Cells dialog, go to the Number tab.
Under Category, select Text.
Click OK.

User's image

Now, when you enter a value like 00123 into those cells, Excel will treat it as text and preserve the leading zeros exactly as typed. This works for any length, as it's not applying a fixed numeric format.

For VLOOKUP: Ensure the lookup value and the table array column are both formatted as text for consistent matching. If needed, you can convert existing numeric data to text using the TEXT function (e.g., =TEXT(A1, "0")) or by selecting the range, going to Data > Text to Columns > Delimited > Next > Next > Text (under Column data format) > Finish.
Noted: Once stored as text, arithmetic operations won't work directly on these values (e.g., summing them), but you can convert temporarily with VALUE() if needed (e.g., =VALUE(A1) + 10).

User's image

We appreciate your kind patience and understanding that sometimes the initial response may not immediately resolve the issue or there may be some misunderstandings about your scenario, but we would love to hear updates from you and find out further suggestions.

Thank you for your kindness and understanding. If you need anything else, please feel free to contact me.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

User's image

Louise Wooding 0 Reputation points

2025-08-28T17:16:35.5933333+00:00

Apologies - we need them to be stored as numbers ideally, not text. Can't get this to work, even with creating a custom number as this will only allow me to retain them if there are a specific number of zeros preceeding the numbers in all of the cells but they will be different lengths.
Barry Schwarz 3,916 Reputation points

2025-08-28T22:25:26.5366667+00:00

What can't you get to work? Tell us what you are trying. Tell us why the result is unacceptable.

A custom format of the form 00...00 (with whatever the appropriate number of zeros is) will retain the value as a number and display it with leading zeros.
Sting-Ng 3,810 Reputation points Microsoft External Staff Moderator

2025-08-29T01:14:30.79+00:00
Hi @Louise Wooding

Welcome to the Q&A Community

I’m here to assist with your problem.

I'm recommended you if you need them to be stored as numbers ideally, not text :

Use an apostrophe prefix:

If you don't want to pre-format, simply type a single apostrophe (') before the number, like '00123.

This forces Excel to treat it as text without changing the cell format. The apostrophe won't appear in the cell but will be visible in the formula bar.

Since you mentioned wanting them as numbers "ideally" (likely for math operations), you can still perform calculations by converting the text to a number on-the-fly in formulas:

Use the VALUE() function. For example, if cell A1 contains '00123 (stored as text), a formula like =VALUE(A1) + 10 will treat it as 123 and output 133.

This keeps the original cell intact with zeros while allowing numeric use elsewhere.

For bulk operations, you could create a helper column with =VALUE(A1) dragged down, then use that for sums, averages, etc.

If your goal is to display all numbers with leading zeros padded to a consistent maximum length (e.g., always 10 digits total, regardless of input), while storing as numbers:

Use a custom format like "0000000000" (10 zeros for 10 digits).

Enter without zeros (e.g., type 123), and it will display as 0000000123.

But again, this adds zeros uniformly and doesn't retain varying input zeros—it's for presentation only.

We appreciate your kind patience and understanding that sometimes the initial response may not immediately resolve the issue or there may be some misunderstandings about your scenario, but we would love to hear updates from you and find out further suggestions.

Thank you for your kindness and understanding. If you need anything else, please feel free to contact me.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Share via

Retain zeros in a cell

2 answers

Your answer