forcing trailing zeros on 5-digit Zip

[Guest]

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.

 

[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

 

[Guest]

Excellent! Thank you very much, it works great.

Dan

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.

 

[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!

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.

 

[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!

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.