Forum Discussion
Excel not sorting numbers correctly - smallest to largest etc.
I made an account just to let anybody Googling this know...
My numbers had spaces after them, darn things! =ISNUMBER could tell they weren't "numbers" even though I'd set the cell format to Number. =CLEAN didn't work; paste special > values didn't work.
What worked was.... =TRIM! (I only thought of this because I just used Data > Trim Whitespace in Google Sheets last week.)
So if you paste special (as values) your data from the web, and/or clean them, and they still won't sort, try:
=TRIM(A1)
Just in case, CLEAN() removes characters with ASCII codes from 0 to 31, TRIM() removes space (code 32). But no one of them removes non-breaking space (code 160) which is the most often case when copy/paste from web.
Any other ideas? Neither =clean(A2) nor =trim(A2) work in my case.
My data is not from the web, is a result of a INDEX MATCH horizontal lookup.
- I realise it is now a year later, but for posterity try:
=SUBSTITUTE(A2,CHAR(160),"")
You can then wrap that in CLEAN and TRIM:
=CLEAN(TRIM(SUBSTITUTE(A2,CHAR(160),"")))
You'll occasionally find other white-space characters (e.g. "hairspace") and there are web pages that list these with their ASCII codes. Are they actually numbers or texts which looks like numbers?
Hello!
They are actually numbers but I found the solution to my problem... the issue was that excel automatically references the worksheet where you are pulling data from even if it is in the same active worksheet when using the match or even vlookup functions. I just had to delete that reference and problem fixed.
Find below detailed explanation:
https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-msoffice_custom-mso_2016/index-match-breaks-when-sorting/c848e231-9e53-4a8d-a5f6-f4bf1b9f0a12#:~:text=The%20fix%20is%20simple.,just%20reference%20the%20cell%20locations.
Thanks for the replay!
