• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 509
  • Last Modified:

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.
0
Tiras25
Asked:
Tiras25
  • 2
  • 2
  • 2
  • +2
2 Solutions
 
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
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
abitounCommented:
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
 
Patrick MatthewsCommented:
Change the numberformat to Text; that will force Excel to treat entries into that range as text, even if they "look" like numbers.
0
 
Tiras25Author Commented:
OK apostrophe and Text formatting works.  

Thank you!
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now