Excel | Adding additional 0s in front of a number

Dear experts,

I have a list of values:

0
1
14
15
41
57
267
488
748
2006
9016
9028
9240

The max character is 4.
I want to add 0 in front of those that does not have character

ie.

1 becomes 0001
14 becomes 0014
267 becomes 0267

Please advise how to configure the cell format.

Many thanks.
trihoangAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Dan CraciunConnect With a Mentor IT ConsultantCommented:
Or go to Format Cells->Custom and put 0000 in the Type field.

HTH,
Dan
0
 
gowflowCommented:
as a formula if your data is in Col A starting from A1 put this formula in B1 and drag it down till end of data
=TEXT(A1,"0000")

gowflow
0
 
helpfinderIT ConsultantCommented:
you can do it manually if it is acceptable for you. Sort the values A>Z and combine appropriate number of zeros with original cell - like in attached sample
sample.xlsx
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.

 
Rgonzo1971Commented:
Hi,

the custom format would be

0000;;0000;@

Open in new window

if you want zero to appear as 0000 or if as 0 try

0000;;0;@

Open in new window

Regards
0
 
Saqib Husain, SyedEngineerCommented:
Another formula

=REPT(0,4-LEN(A1))&A1
0
 
SteveCommented:
Another simple formula:

=RIGHT(A1+10000,4)
0
All Courses

From novice to tech pro — start learning today.