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

x
Solved

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

Many thanks.
0
Question by:trihoang

LVL 31

Expert Comment

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

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

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

HTH,
Dan
0

LVL 53

Expert Comment

ID: 39942069
Hi,

the custom format would be

``````0000;;0000;@
``````
if you want zero to appear as 0000 or if as 0 try

``````0000;;0;@
``````
Regards
0

LVL 43

Expert Comment

ID: 39942073
Another formula

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

LVL 24

Expert Comment

ID: 39942177
Another simple formula:

=RIGHT(A1+10000,4)
0

## Featured Post

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…
###### Suggested Courses
Course of the Month6 days, 4 hours left to enroll