I think you forgot to upload the file you changed with spaces; there is no new spaces and I can't sort. It looks the same to me as the others I posted, nothing is changed. Can you give more detail in the post or in the excel file to explain what you are doing exactly to get rid of the spaces and also how to tell if there are spaces in the cell (character numbers make no sense to me)?

I will get that information to you later if needed. When I tested it, I copy and special pasted all the values after the substitute formula. This allowed me to sort it. You probably may need to do the same. If pasting special is not an option we will figure something out.

Let me know if you still need me to re-upload or not.

Thanks

Ok. If I do substitute formula with 1 space " " under old text, referring to column J text, in column O, and then in the column next to it, I copy and special paste values, it sorts the column next to it correctly, but this only works if I substitute 1 space; otherwise it sorts like the "bad" column I have.

Can you explain to me why the spaces mess up the sort, even when I copy, paste special, values or multiply the distance column by 1, which has worked in the past? How can I avoid this in the future when I remove numbers from a text string?

Also, I don't understand what you mean when you said, "Instead of placing a space in the formula I copy and pasted a space from your distance values". What does that mean? What formula are you referring to?

No problem this is a fair question. The formula to be used is =substitute(J5," ","")+0 Now here is the situation. It is best explained with an experiment. Open up notepad or wordpad or even word. Type any single word. Right after that word type another word (do not include a space) now place your cursor between those 2 words hold ALT and press 255 the let go of Alt. You will see that it will produce a space. This is not the usual space that is created when you press the space bar it is a char character. go ahead and do ALT and press 2 then let go. Did you get a smiley face? these are char characters. try ALT+217 or ALT+16 they all make different char characters. For some reason the data in column I has Char character (Alt+255) instead of a space.

To try this write your substitute formula like so
=substitute(j5,"
Now instead of space press ALT + 255 and then another quote so it looks like this
=substitute(j5" "
then finish off your formula and try your sort it should work.
=substitute(j5" ","",)+0

Sorry for the lengthy explanation.


As far as to how to prevent this in the future look to where you copied the data from in column I from it has these char spaces instead of regular spaces.

Let me know if this clears it up for you.