try a custom number format like this.
[<100000]00000;00000-0000

to get the format to "set in" follow this procedure.
1. Copy an empty cell
2. Select the cells you want to "set in" the number format
3. Select Edit-Paste Special
4. Select "add"

When you change the format of text to a number format Excel doesn't
automatically recognise a string of numbers as text. You have to trigger it
by either editing it or using the above method. I call this "Setting in the
format"

"dm" wrote:

I copied a column of zip codes that has nine digit codes and five digit
codes. Then I pasted it into another worksheet, selected Format, Special,
zip + 4. Nothing happens. If I double click one of the cells, the format will
change. The nine digit zip codes will be okay but the five digits will appear
as 0001-2345 rather than 12345. It's also time consuming to go through a
whole spreadsheet double clicking cells. How do I get both five and nine
digit codes to be properly formatted? This happens whether I'm using
copy/paste, importing from Access or using an Excel file exported from
Crystal Reports.