Zip Code Formatting

[Guest]

I have a spreadsheet with data imported from a database program. One of the
columns is the zip code. Some of the zip codes have zip-plus-4 and others
just have the old 5-digit zip code. I want to be able to sort by the zip
code, meaning I'd end up with 5-digit zip codes interspersed amoung the zip +
4 codes.

If I format the cells in the column as "special" zip + 4 formatting, it adds
a five-digit field before those that don't already have the "plus 4" part.
So what was 01080 becomes 00000-1080. Not acceptable.

If I format the cells as text I loose the leading zeros. 01080 above
becomes just 1080. Still not acceptable.

Any ideas? Am I missing something?

Jerry

 

[Bearacade]

Assuming your data is in column A, In another column (say B) put thi
in.

=LEFT(A1,5)

Now drag and fill as far as your data go.

 

[Guest]

That will just delete the "plus four" side of those zipcodes, but I don't
want that. I want to retain the zipcodes as they are, but be able to sort
them all in proper zipcode order. So it might look like this:
01804
01804-1234
11456
12345
12345-2245

etc.

'nuther idea?

Jerry

 

[Bearacade]

Did you try the

=IF(LEN(A1)=5,A1&"-0000")

This will add -0000 to cells that doesn't have +4

 

[Dave Peterson]

Maybe you could use a custom format of:
[>99999]00000-0000;00000
to make it look pretty.

But for sorting, I think I'd use a extra column and convert the number to text.
=TEXT(A1,"[>99999]00000-0000;00000")

Then sort by that extra column.

 

[Guest]

I had to tweak it a bit to this:
=IF(LEN(A1)=5,A1&"-0000",A1) becasue if the existing number was already in
the zip + 4 format, it would return "FALSE".

However, this formula does not work on zip codes that start with a zero
(mostly New England areas). 01804 doesn't get "converted" because its length
is seen as less than 5.

Any ideas of how to get around that quirk?

Jerry

 

[Ragdyer]

You could use 2 'helper ' columns.

You could do this 2 ways.
If you're going to do this often, in an on-going basis, use the Text
formulas, where you can save the 2 columns of formulas, and paste your new
data in the "separate me" column.

If it's a one shot deal use TTC (Text To Columns), which takes some setting
up each time.

1 ] Text formulas:

"Separate Me" column is ColumnA.

In B1 enter,
=LEFT(A1,5)

In C1 enter,
=(LEN(A1)>5)*RIGHT(A1,4)

Select *both* B1 and C1 and drag down to copy as needed.

Then, select A1 to Cn,and then sort by Column B, then by Column C.

You should now have your data sorted as desired.
Use or copy Column A as needed.

You can clear ColumnA or simply overwrite it the next time you have data to
import and sort.

2 ] TTC

Select the column of data and make sure you have 2 empty adjoining columns,
then:
<Data> <Text To Columns> <Fixed Width> <Next>,
Click in the "Preview Window" and place the 'Break line" *after* the dash,
*before* the last 4 numbers.
Then <Next>
In the Preview Window the first column is selected by default, so just click
on "Text" under "Column Data Format".
Click in the second column to select it, and also change this to "Text".
Then click in the Destination Box and change the default address (original
data location) to the fist cell of the next empty adjoining column.
This preserves the original data from being overwritten.
Then <Finish>

Now, select all 3 columns and sort on the second, then the third column.
Your original data is now sorted as you wish.

 

[daddylonglegs]

Doesn't Bearacade's last suggestion work for you, alternatively...

=IF(LEN(A1)<6,TEXT(A1,"00000")&"-0000",A1)

 

[David McRitchie]

Hi Jerry,
Zip codes should really be text instead of numbers, to convert them
in place to text with a macro retaining all digits that you have, see
Fix up for 5 digit US zip codes (#fixUSzip5)
http://www.mvps.org/dmcritchie/excel/join.htm#fixUSzip5