Solved

Excel | Adding additional 0s in front of a number

Posted on 2014-03-20
6
267 Views
Last Modified: 2014-03-20
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.
0
Comment
Question by:trihoang
6 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39942059
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
 
LVL 19

Expert Comment

by:helpfinder
ID: 39942062
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
 
LVL 34

Accepted Solution

by:
Dan Craciun earned 500 total points
ID: 39942066
Or go to Format Cells->Custom and put 0000 in the Type field.

HTH,
Dan
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39942069
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39942073
Another formula

=REPT(0,4-LEN(A1))&A1
0
 
LVL 24

Expert Comment

by:Steve
ID: 39942177
Another simple formula:

=RIGHT(A1+10000,4)
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel Conditional Statements 11 39
Alphabetical Order for Letters 2 21
Copying from excel I am getting extra text 11 43
excel count months in date range 6 12
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question