Imported txt File - Will Not Sort Correctly

[saintsfan]

I've saved my Outlook "distribution lists" to a .txt file and then imported them into Excel. I've combined those files with a file I had in Word of email addresses in an attempt to create a master email list for clients and customers. Anyway, now that I have one file, I'm trying to sort the file by email address and I get some crazy sort results. The entire list will sort but not correctly. It seems to start over from A to Z in certain spots... I've copied and pasted special as values... I've highlighted the columns and changed it all to text fields, same font etc... Still Excel is seeing something I'm not when it comes to the sort. I can highlight all and look for duplicates, that works fine... but when it comes to sorting it correctly it is hosed up. I'm using Office 2007... Any thoughts or advice?

 

[nbrcrunch]

perhaps you have leading spaces?

You can use LEN() to count spaces and then compare that to a manual count of suspect entries.

You can use TRIM() to remove spaces. Aftward, copy/paste-special values.

 

[saintsfan]

I've got something odd going on here. I used LEN() and notice that some addresses have lets say 27 characters counted... but should only be 26. There is space at the end. But when I delete the space manually the last letter (for example .com) the "m" will then erase not the space. If I manually place the "m" back on it adds the space back. Weird not sure why this is happening or how I can solve. I've never used TRIM() but will start messing with it now.

 

[saintsfan]

found the problem or so I think... well at least i solved it... cut and pasted my problem cells into new file. saved as .csv file and then opened Word. had some spaces when opened in word with "" around it. Not sure how, but anyway manually deleted them. Save the file as txt file and imported back into excel. pasted back to my original columns and now sorts just fine.

 

[nbrcrunch]

Glad to hear it is resolved. You may also want to take a look at the CLEAN() function. It removes non-printing characters. Open you help system in excel and search for text functions. it may be an eye-opener for you.

 

[saintsfan]

thx nbcrunch... yep I bet that would have done it and saved me some time for sure. good to know. thx again!