Sort Problem in Excel Spreadsheet 2

[fish2]

I am having a problem sorting a mail list by zip code it begins to sort it correctly and then it takes it out of order and begins to resort all over again. There were two lists combined one was exported to an excel spreadsheet from Blackbaud. Anyone with any insight please, please respond my head is about to explode. I believe it has something to do with the formatting but I have tried just about everything I can think of short of retyping....Thanks in advance for help!!!!

 

[jfhewitt]

I feel your pain! My wife has these problems now and then...often resulting from different zip formats. I got around it by formatting a new column in custom zip format, and copying the old mixed column, using paste special/add. At least I think that's what I did.

 

[SkipVought]

Hi,

You can't mix 5 & 5+4 formats AND use that column to sort.

However, if you make another column for a 5 digit ZIP, then sort on it, EVERYTHING will be A-OK! ;-)
[tt]
=left(Z1,5)
[/tt]
where column Z is the MIXED ZIPs




Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

make that formula
[tt]
=VALUE(IF(LEN(Z1)<5,Z1,LEFT(Z1,5)))
[/tt]


then format as ZIP 5

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[fish2]

Thank you the suggestions helped me solve my problem!!!!! woooo hooooo

 

[mscallisto]

Or you could highlite the column then
Format > Cells > Special > pick zip or zip +4
sort

 

[mscallisto]

Sorry I should have read the thread...didn't see you imported text data.

Yep jfhewitt is correct with the paste special

Sorry...going home now!