Forum Discussion

Copper Contributor

Jun 23, 2018

Excel not sorting numbers correctly - smallest to largest etc.

I have been helping a friend organize a excel file with 4 digit numbers. These numbers are in the 6000 to 8000 range. When I filter or sort the numbers from smallest to largest there are numbers , ...

Diamond Contributor

Jan 06, 2021

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.

Copper Contributor

Apr 14, 2021

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.

Philip_Hinton
Copper Contributor
May 31, 2022
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.
SergeiBaklan
Diamond Contributor
Apr 15, 2021
JRVM1

Are they actually numbers or texts which looks like numbers?
- JRVM1
  Copper Contributor
  Apr 15, 2021
  SergeiBaklan
  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!
  - aisling_s
    Copper Contributor
    Nov 02, 2022
    Thanks! This was it for me, though I was using SUMIFS

Resources