Solved

Excel | Adding additional 0s in front of a number

Posted on 2014-03-20
6
277 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 500 total points
ID: 39942066
Or go to Format Cells->Custom and put 0000 in the Type field.

HTH,
Dan
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 52

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
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…
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…

630 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