Formatting different zip code types within a column

[Guest]

In Excel, with the .csv file extension, we have a column of zip codes that
are set up differently for each record. For an example, we will have 6037,
60371022 and 25463. If we highlight the whole column and select "zipcode",
the 6037 becomes 06037 (like it should), the 25463 stays the same, but the
60371022 doesn't get the leading zero. If we use the zip plus 4, then the
6037 becomes 06037-0000 which is also incorrect. Is there a way to format
all three types at the same time without doing too much manual manipulation?
Please advise. Thanks

 

[Guest]

The first step is to get the data in the column in pure text format. Re-name
a copy of the .csv file .txt.

This will evoke the Import Wizard. Tell the Wizard that the column is text.



The next step is to enter this macto in a standard module:

Sub going_postal()
Dim s As String
For Each r In Selection
s = r.Value
If Len(s) = 4 Then
s = "0" & s
End If
If Len(s) < 6 Then
r.Value = s
Else
If Len(s) = 8 Then
r.Value = "0" & Left(s, 4) & "-" & Right(s, 4)
Else
r.Value = Left(s, 5) & "-" & Right(s, 4)
End If
End If
Next
End Sub

The third step is to select the cells you want to convert and run the macro.
If you start with:
08549
8549
85491234
085491234

the macro will convert it into:
08549
08549
08549-1234
08549-1234


If you need any help installing and running the macro, just update this post.

 

[Dave Peterson]

Try a custom format.

Format|Cells|Number tab|Custom category
[>99999]00000-0000;00000

 

[Guest]

That works perfectly except for the cells where the zip code is, for an
example, 27656 which is a valid zip code. It's changing it to 00002-7656.
How can I accommodate that? Thanks!

Try a custom format.

Format|Cells|Number tab|Custom category
[>99999]00000-0000;00000

In Excel, with the .csv file extension, we have a column of zip codes that
are set up differently for each record. For an example, we will have 6037,
60371022 and 25463. If we highlight the whole column and select "zipcode",
the 6037 becomes 06037 (like it should), the 25463 stays the same, but the
60371022 doesn't get the leading zero. If we use the zip plus 4, then the
6037 becomes 06037-0000 which is also incorrect. Is there a way to format
all three types at the same time without doing too much manual manipulation?
Please advise. Thanks

 

[Guest]

Never mind! I wrote the formula wrong! Thanks! That was perfect and easy!

Try a custom format.

Format|Cells|Number tab|Custom category
[>99999]00000-0000;00000

In Excel, with the .csv file extension, we have a column of zip codes that
are set up differently for each record. For an example, we will have 6037,
60371022 and 25463. If we highlight the whole column and select "zipcode",
the 6037 becomes 06037 (like it should), the 25463 stays the same, but the
60371022 doesn't get the leading zero. If we use the zip plus 4, then the
6037 becomes 06037-0000 which is also incorrect. Is there a way to format
all three types at the same time without doing too much manual manipulation?
Please advise. Thanks