The 9 digit numbers are text.

Format all to General.

Copy an empty cell.

Select the range of "numbers" and Paste SpecialAddOKEsc.


Gord Dibben MS Excel MVP

On Sat, 12 Dec 2009 13:55:01 -0800, Attman68
wrote:

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" wrote:

Hi

You could use a custom format such as:

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

which should force the correct format.

Hope this helps!

Richard


On 5 Mar, 14:14, dandiehl wrote:
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.