Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel | Adding additional 0s in front of a number

Posted on 2014-03-20
6
Medium Priority
?
288 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 31

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 35

Accepted Solution

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

HTH,
Dan
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 53

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 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