Retain zeros in a cell

Question

Retain zeros in a cell

Louise Wooding 155

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

0 comments

Answer accepted by question author

Anonymous

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 155 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 6,101 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.
Anonymous

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.

2 additional answers

Your answer

Louise Wooding 155 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 6,101 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.

Answer 1

Louise Wooding 155

Hi. so sorry to bother you - I'm trying to post another question but I just keep getting told there's an error. This new AI feature it now uses doesn't work very well and rarely answers the question but then if I unselect AI, I don't get any further and just get an error message every time. I notice this has changed recently with how it works and isn't as efficient. Is there a problem with posting questions recently? I used to use this a lot and it worked amazingly well when you got through to 'real people'. Thank you and, again, sorry to bother you. Louise

Anonymous

2025-09-03T23:42:30.5633333+00:00

Hi @Louise Wooding

Welcome to the Q&A Community

I’m here to assist with your problem.

I understand you're experiencing issues when trying to post a new question, especially with the AI feature enabled. Here are a few steps you can try to resolve the problem:

Clear your Browser cached: Sometimes cached data can interfere with new updates. Try clearing your browser cache and cookies, then reload the page. Refer to: How to Manage and Clear Your Cache and Cookies | Edge Learning Center

Try a different Browser or Device: If the issue persists, switching to another browser (like Chrome, Edge, or Firefox) or device may help isolate the problem.

Disable AI temporarily: If the AI feature isn’t working well for your needs, you can try disabling it — but I understand this has caused errors. If you do disable it, make sure to refresh the page before posting again : Control use of Generative AI features for users (preview) | Microsoft Learn

Additionally, you can share feedback on my support by click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment" . I sincerely appreciate it.

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.
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Anonymous

2025-09-08T02:31:21.8133333+00:00

Hi @Louise Wooding

Welcome to the Q&A Community

I’m here to assist with your problem.

Any update on your issue ?

Additionally, you can share feedback on my support by click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment" . I sincerely appreciate it.

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.
Louise Wooding 155 Reputation points

2025-09-09T10:04:14.4033333+00:00

Hi, thank you for your response. I can't use another device, I use the one laptop. I also don't want to start using a different browser, I'm happy with my current set up. I've tried posting several times and always get the same error, even after a reboot/refresh etc. I unselect the AI option because more often than not, it doesn't offer a correct answer any way. Not a great update in my opinion - I use this community a lot and always get great rapid responses - like yours - from knowledgeable people. It just doesn't work any more :(
Anonymous

2025-09-09T23:34:58.27+00:00

Hi @Louise Wooding

Welcome to the Q&A Community

I’m here to assist with your problem.

Thank you very much for reaching out and sharing your concerns.

Regarding the Excel issue you mentioned earlier, I’m not sure if it has already been resolved. If the solution I provided has helped address your problem, I would greatly appreciate it if you could click "Accept Answer" to acknowledge my support.

As for your AI Option inquiry, I truly regret the inconvenience. However, as a forum moderator, my access and permissions are limited, which restricts my ability to assist you directly with this issue. I kindly suggest that you submit your information to the Ideas · Community. Our product team is in charge of the site, and they constantly check customer reviews and feedback, The higher votes, the more attention the related team will pay on. And a lot of the features are developed and improved based on customer feedback.

I sincerely hope your issue will be resolved soon. Thank you again for your understanding and cooperation. 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.
Anonymous

2025-09-11T00:13:17.1433333+00:00

Hi @Louise Wooding

Welcome to the Q&A Community

I’m here to assist with your problem.

Thank you very much for reaching out and sharing your concerns.

Regarding the Excel issue you mentioned earlier, I’m not sure if it has already been resolved. If the solution I provided has helped address your problem, I would greatly appreciate it if you could click "Accept Answer" to acknowledge my support.

As for your AI Option inquiry, I truly regret the inconvenience. However, as a forum moderator, my access and permissions are limited, which restricts my ability to assist you directly with this issue. I kindly suggest that you submit your information to the Ideas · Community. Our product team is in charge of the site, and they constantly check customer reviews and feedback, The higher votes, the more attention the related team will pay on. And a lot of the features are developed and improved based on customer feedback.

I sincerely hope your issue will be resolved soon. Thank you again for your understanding and cooperation. 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.
Louise Wooding 155 Reputation points

2025-09-11T11:55:09.2+00:00

Hi Sting_ng and thank you for your response. Regarding my initial issue of the retaining the leading zeros in a number, my issue wasn't resolved. Not because nobody replied but because it appears there isn't a simple way to do this if all the numbers are different lengths. I don't want to covert to text, I wanted to retain them as numbers. I could create a custom number format but it would have to be on every cell and I just don't have the capacity to do that. I'll choose Accept answer because it's been confirmed I can't easily do what I was hoping for. Again, thank you for your responses, it's appreciated. Louise
Anonymous

2025-09-11T23:39:39.2833333+00:00

Hi @Louise Wooding

Welcome to the Q&A Community

I’m here to assist with your problem.

Thank you very much for reaching out and sharing your concerns.

I sincerely hope your issue will be resolved soon. Thank you again for your understanding and cooperation. It's appreciated.

Answer 2

HansV 462.7K 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 155 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 6,101 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 462.7K Reputation points MVP Volunteer Moderator

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

Format the cells as Text before entering the values:
Louise Wooding 155 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 462.7K 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.