forcing trailing zeros on 5-digit Zip

D

dandiehl

If you format a cell to use the Zip code +4 format, and you only enter the
5-digit code, Excel displays the entry as 00001-2345. Is there a way to
force the leading zeros to become trailing zeros? I would like it to display
as 12345-0000.
 
R

RichardSchollar

Hi

You could use a custom format such as:

[<100000]00000-"0000";00000-0000

which should force the correct format.

Hope this helps!

Richard
 
D

dandiehl

Excellent! Thank you very much, it works great.

Dan


RichardSchollar said:
Hi

You could use a custom format such as:

[<100000]00000-"0000";00000-0000

which should force the correct format.

Hope this helps!

Richard


If you format a cell to use the Zip code +4 format, and you only enter the
5-digit code, Excel displays the entry as 00001-2345. Is there a way to
force the leading zeros to become trailing zeros? I would like it to display
as 12345-0000.
 
A

Attman68

Hi,

I've having a related problem. I have a column of data which contains 9
digit numbers. These are zip codes + 4. Problem is that we received the
data as a continuous 9 digit number with no hyphen. For some other software
we use, I really need the hyphen. I went to "Format Cell" and found zip+4
formatting and custom formating which shows "00000-0000." I tried to apply
each and nothing happened. I even created another column, formatted with
that cell format and then pasted the numbers in. Nothing. In other columns,
I was able to reformat cell properties such as turning currency numbers into
regular numbers.

Little help? Thanks!

RichardSchollar said:
Hi

You could use a custom format such as:

[<100000]00000-"0000";00000-0000

which should force the correct format.

Hope this helps!

Richard


If you format a cell to use the Zip code +4 format, and you only enter the
5-digit code, Excel displays the entry as 00001-2345. Is there a way to
force the leading zeros to become trailing zeros? I would like it to display
as 12345-0000.
 
G

Gord Dibben

The 9 digit numbers are text.

Format all to General.

Copy an empty cell.

Select the range of "numbers" and Paste Special>Add>OK>Esc.


Gord Dibben MS Excel MVP

Hi,

I've having a related problem. I have a column of data which contains 9
digit numbers. These are zip codes + 4. Problem is that we received the
data as a continuous 9 digit number with no hyphen. For some other software
we use, I really need the hyphen. I went to "Format Cell" and found zip+4
formatting and custom formating which shows "00000-0000." I tried to apply
each and nothing happened. I even created another column, formatted with
that cell format and then pasted the numbers in. Nothing. In other columns,
I was able to reformat cell properties such as turning currency numbers into
regular numbers.

Little help? Thanks!

RichardSchollar said:
Hi

You could use a custom format such as:

[<100000]00000-"0000";00000-0000

which should force the correct format.

Hope this helps!

Richard


If you format a cell to use the Zip code +4 format, and you only enter the
5-digit code, Excel displays the entry as 00001-2345. Is there a way to
force the leading zeros to become trailing zeros? I would like it to display
as 12345-0000.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top