Format cell in Excel to accept zeros in front

Excel 2010.  How can I format cells in Excel so it accepts zeros(000) in front of the numbers.  I tried General, Numbers, etc - nothing helps.

Please advice.
LVL 17
Tiras25Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Patrick MatthewsConnect With a Mentor Commented:
Change the numberformat to Text; that will force Excel to treat entries into that range as text, even if they "look" like numbers.
0
 
Hendrik WieseInformation Security ManagerCommented:
Try a custom value of 0000 then it will show leading zeros
0
 
Scanman999Commented:
Just used the CUSTOM option in the Cell Format dialog and put 000000 to format any numbers to 6 digits in this case
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Hendrik WieseInformation Security ManagerCommented:
sorry so you would go Format Cells > click on Custom and in the Type field where the default value is General just type in 0000 and click ok.

Now when you enter 5 in the field it will display as 0005
0
 
abitounConnect With a Mentor Commented:
that is the best option for a lot of fields but it actually does not add the zeroes, it just mask the zeroes. in other words it is only a visual effect. if you want to add the zeroes for rea (for example if you are saving a text file and the id's need zeroes) you can add an apostrophe (') before the first zero and excel will treat it as such, unfortunately if you save it as csv and reopen it excel will truncate the zeroes again. if you have a huge list you can use the followingformula to achieve your goal:

="'"&repeat(0,10-len(a2))&a2
in the above formula a2 has the number without zeroes, 10 is the total length i want for the field, then i add a2 and gives me a perfect field of 10 charachters with leading zeroes.
0
 
Tiras25Author Commented:
That still doesn't work for me.  I have a number in the cell [1788348].  I need to keep this number with 00 in front [001788348].  I put zeros in, and when go next cell it switches back to 'no-zeroes' format.  

Custom still doesn't help....
0
 
abitounCommented:
when you go to custom you put:
00####### so custom knows to add two zeeroes in front of the number
0
 
Tiras25Author Commented:
OK apostrophe and Text formatting works.  

Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.