Zero's before a number

[Christina Calkins]

I appoligize in advance if this question has already been asked and I missed it. I did check though. I have a list of 5 digit zip codes and 3 digit zip codes in a spreadsheet (.xls) that I need to have a 0 in front of. Example:
62025
1453
3470
16117
16254
AND
955 960
10 27
28 29
30 39
40 49
60 69
70 79
80 89
120 139
140 149
150 169
What formula would I use to get the zero's in front of the ones that need it?
Thank you in advance.

Christina

 

[NARAYANK991]

Hi Christina ,

In the 3-digit codes , are the two codes in the same cell ? For instance , in the sample data :

955 960

are the above values in one cell ?

Do you want the output to be : 0955 0960 in one cell ?

Narayan

 

[Christina Calkins]

All of the zips are in different cells.

 

[Christina Calkins]

So for example if the zip code is 75, I need it to be 075 and if the zip code is 4567 I need it to be 04567.

 

[Somendra Misra]

Use below formula to make them of 5 digit

=IF(LEN(A1)<5,MID("0000",1,5-LEN(A1))&A1,A1)

Regards!

 

[Christina Calkins]

What formula do I use if I need them to be 3 digit? Do I replace the 5 with a 3?

=IF(LEN(A1)<5,MID("0000",1,5-LEN(A1))&A1,A1)

 

[Somendra Misra]

Hi use this one
=IF(AND(LEN(A1)<5,LEN(A1)>3),MID("00",1,5-LEN(A1))&A1,IF(LEN(A1)<3,MID("00",1,3-LEN(A1))&A1,A1))

Regards!

 

[NARAYANK991]

Hi Christina ,

Try this :

="0"&A1

where A1 contains the number , 2-digit , 3-digit ,....

Narayan

 

[Christina Calkins]

When I used the formula, I had to format the cell for zip code, which worked for the 5 digit ones, but not the 3 digit ones. Do I have to retype the numbers after I paste the formula? I was hoping that I didn't have to. I have almost 14,000 of them to do.

 

[Christina Calkins]

Narayan, I have figured it out. Please give me a few minutes to see.

 

[Christina Calkins]

It worked. OK, so here is my next question. I have 5 digit zips and 3 digit zips mixed in with 2 digit and 4 digits. The ones that are already 5 and 3's I need to keep the way they are. It's the ones that are 2 and 4 that need to have the zeros in front of them. As I said before, they are all seperate however, I have over 14,000 of them and sorting them doesn't always work at singling the 2 and 4 from the 3's and 5's. Is there a way to keep the one that I need to stay the way that they are and only change the ones that are 2 and 4?

 

[NARAYANK991]

Hi Christina ,

How do you want the 2-digit and 4-digit values to be displayed ?

67 becomes 067

1234 becomes 01234

If this is so , try this :

=IF(MOD(LEN(A1),2)=0,"0"&A1,A1)

Narayan

 

[Christina Calkins]

That is BEAUTFUL! Love it. Thank you so much, you have saved me tons of time. Have a great day.

-Christina

 

[Hui]

I would use:

=REPT("0",3-LEN(A1)) & A1

Change 3 to how ever many digits you want

 

[crouchsw]

You could also use
=TEXT(A1,"00000")
or
=TEXT(A1,"000")

This will handle either 3 or 5 digit codes:

=TEXT(A1,IF(LEN(A1)<4,"","00")&"000")

 

[Christina Calkins]

Thank you to everyone for all of the help.
-Christina