sort 5-digit and 9-digit zips in same column

Posted by michelle on February 24, 2001 6:54 AM

Does anyone know how to format a column to allow me to sort 5-digit zip codes along with 9-digit zip codes in the same column without having Excel place all of the 9-digit zips at the bottom?

Posted by Dave Hawley on February 24, 2001 7:05 AM

Hi michelle

I'm sure I can help you but, if yu don't want all the nine digit codes at the bottom where do you want them ? In other what is the sort criteria ?

Dave
OzGrid Business Applications

Posted by Michelle on February 24, 2001 7:12 AM

sort by the first 5 digits only, ignoring the last 4 digits that appear on the 9-digit codes

thanks

Posted by Dave Hawley on February 24, 2001 7:22 AM

Ok, if your codes are in Column A in Column B cell B1 put:
=VALUE(LEFT(A1,5))

Copy down as far as needed.
Select Column B and Copy the entire Column.
With Column B still selected Go to Edit>PasteSpecial-Values.
Now sort Columns A and B by Column B.
Delete Column B

Any good ?

Dave

OzGrid Business Applications

Posted by michelle on February 24, 2001 7:37 AM

you're awesome -thanks