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.

Just insure that the cells in the original column have been formatted to Text
prior to entering data into them. When these cells are copied to another
column, the formatting will be correct.


For example if both A1 & A2 have been formatted as Text and then set to:

08759
01258-9876

then copying these cells will preserve the format of each one.

--
Gary's Student


"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.

Someone always comes up with a simple solution while I'm off figuring
out the complicated one! But this took me a few minutes so I'm going
to post it anyway.

Alternatively, highlight the appropriate range of cells and run this
macro. It formats each cell individually, based on the number of
characters in each cell.

Sub Zip_Format()
Dim rCell As Range
Dim Zip As Variant

For Each rCell In Selection.Cells
Zip = Trim(rCell.Value)
rCell.ClearContents
If Len(Zip) 5 Then
rCell.NumberFormat = "00000-0000"
Else
rCell.NumberFormat = "00000"
End If
rCell.Value = Zip
Next rCell

End Sub

Yesterday's suggestion didn't help?

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.

--

Dave Peterson

Sorry, Dave, but it didn't. Thanks anyway. dm

"Dave Peterson" wrote:

Yesterday's suggestion didn't help?

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.

--

Dave Peterson

Thanks, Dave. dm

"Dave O" wrote:

Someone always comes up with a simple solution while I'm off figuring
out the complicated one! But this took me a few minutes so I'm going
to post it anyway.

Alternatively, highlight the appropriate range of cells and run this
macro. It formats each cell individually, based on the number of
characters in each cell.

Sub Zip_Format()
Dim rCell As Range
Dim Zip As Variant

For Each rCell In Selection.Cells
Zip = Trim(rCell.Value)
rCell.ClearContents
If Len(Zip) 5 Then
rCell.NumberFormat = "00000-0000"
Else
rCell.NumberFormat = "00000"
End If
rCell.Value = Zip
Next rCell

End Sub

Thanks, Gary. But I'm not sure it will work when I'm importing from Access or
Crystal Reports. dm

"Gary''s Student" wrote:

Just insure that the cells in the original column have been formatted to Text
prior to entering data into them. When these cells are copied to another
column, the formatting will be correct.


For example if both A1 & A2 have been formatted as Text and then set to:

08759
01258-9876

then copying these cells will preserve the format of each one.

--
Gary's Student


"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.

Here's more info on what happened when I tried yesterday's suggestion. The
nine digit codes reformatted perfectly. However, the five digit codes came
out 00001-2345 rather than 12345.

dm

"Dave Peterson" wrote:

Yesterday's suggestion didn't help?

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.

--

Dave Peterson

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.

And you had another followup that said:


You don't want to use Zip+4 as your number format.

Try a custom format of
Format|Cells|Number tab|Custom category
[99999]00000-0000;00000

In that Type: box.

======
Didn't that work?

dm wrote:

Here's more info on what happened when I tried yesterday's suggestion. The
nine digit codes reformatted perfectly. However, the five digit codes came
out 00001-2345 rather than 12345.

dm

"Dave Peterson" wrote:

Yesterday's suggestion didn't help?

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.

--

Dave Peterson


--

Dave Peterson

And by posting multiple times and starting multiple threads, it makes it much
more difficult for you to find all the suggestions that you got.

dm wrote:

Here's more info on what happened when I tried yesterday's suggestion. The
nine digit codes reformatted perfectly. However, the five digit codes came
out 00001-2345 rather than 12345.

dm

"Dave Peterson" wrote:

Yesterday's suggestion didn't help?

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.

--

Dave Peterson


--

Dave Peterson