Solved

Excel | Adding additional 0s in front of a number

Posted on 2014-03-20
6
268 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 50

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Outlook Free & Paid Tools
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

830 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