Quote:
Originally Posted by jyfuller
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.