Sort on first 5 digits of ZIP

[Mary]

I have a column of zip codes that I want to use in a sort.
I only want to sort on the first 5 digits. Some of the zip
codes are 12345-1234 others are just 12345. How can i
either remove the '-1234' or sort on only the first five
digits.

thanks

Mary

[Jan Karel Pieterse]

Hi Mary,

Create an additional column with this formula (Zip's in column A, I
started on row 2):

=LEFT(A2,5)

Double click the fill handle (the small square bottom left of the cell
selector, your mouse pointer changes to a cross when you hover over it)
to fill the formula down all the way to match your entries.
Now use the new column as the sort key.

Regards,

Jan Karel Pieterse
Excel MVP

[Bernard Liengme]

Hello Mary,
I think we ned to insert a column to hold values on which to
sort.
Try this to filter out the long Zip Codes
=IF(ISERROR(FIND("-",A2)),A2,LEFT($A2,FIND("-",$A2)-1))

It looks for hyphen. If that fails, it reproduces the whole
value, otherwise it gives the stuff to the left of the hyphen.

Best wishes
Bernard

[Bernard Liengme]

Oh my! What a long winded way I used.... Must be coffee time
Bernard

[Dave Peterson]

If your zipcodes are really numbers that have been formatted as Special (zip+4),
then maybe something like this in an extra column:

=LEFT(TEXT(A1,"00000-0000"),5)

Then drag down.

--

Dave Peterson
ec3...@msn.com

[RagDyer]

>>Some of the zip codes are 12345-1234 others are just 12345<<

You could insert a "helper" column next to the zip codes and enter this:

=Left(A1,5)
And copy it down as far as necessary.

Then, just select all pertinent columns, including the "helper" column, and
then sort on the "helper", and delete it later if necessary.

HTH

RD

"Mary" <Maryzz...@mail.com> wrote in message
news:424701c1df24$b5e624a0$3aef2ecf@TKMSFTNGXA09...