May 01 2020 12:46 AM
May 01 2020 01:45 AM
Do you have the same result if you sort manually, not by function? If so, do you have the same locale as on other computer?
May 01 2020 02:28 AM
May 01 2020 02:53 AM
Okay, let start from scratch.
- is another sorting order on your laptop for sorting numbers or texts or months or all of above?
- sorting could depend on locale (regional settings), are they the same for the laptop and another computer on which you tested?
- if copy the file from OneDrive to local folder (not synced with OneDrive) will you have the same sorting order as for the OneDrive file?
- what are regional settings for OneDrive?
But first question what is sorting incorrectly. E.g. 5,9,1 is sorted as 9,5,1 or doesn't sorted at all or what.
May 01 2020 03:27 AM
May 01 2020 04:34 AM
So, do I understand correctly that's only for one concrete workbook, and perhaps only for one worksheet in this workbook? And if you sort exactly the same file (not copy/pasted data) on iPad it sorts it in a right way?
May 01 2020 05:17 AM
May 01 2020 05:51 AM
May 01 2020 08:25 AM
If your laptop is on Windows did you have a chance to test on another Windows machine? Or perhaps you could submit the file in question here to remove sensitive information.
May 02 2020 12:57 AM
@Sergei BaklanAttached is the file. In one column in the data sorted from another device and in another column is the file sorted from my laptop.
May 02 2020 01:16 AM
Check at least the first three characters of each text:
=UNICODE(MID($D2;1;1))
=UNICODE(MID($D2;2;1))
=UNICODE(MID($D2;3;1))
May 02 2020 02:20 AM
Yes, as @Detlef Lewin mentioned there are about hundred texts with not-printable unicode characters in front. Excel on Windows uses unicode sorting and to my knowledge there is no option to shift on ANSII sorting and back. Not sure how Excel for Mac works, based on your sample it performs ANSII sorting.
On Excel for Windows Power Query sorts correctly ignoring non-printable unicode characters.
May 02 2020 03:35 AM
Tried entering the formula but its giving an error message.
I hope you have the file. What corrections would I need to do?
May 02 2020 04:45 AM
Decode the error message.
A workaround could be:
=SUBSTITUTE(D2,UNICHAR(8203),"")
And then copy/paste the cells as values.
May 02 2020 04:46 AM
May 02 2020 04:47 AM
Oops, we answered practically simultaneously
May 02 2020 05:03 AM
May 02 2020 05:35 AM
Thanks a lot. I replaced the text with the result of the above formula and now sorts seems to work on my laptop.
Do help me here as to how did this happen and what I need to do to avoid such errors as we use collaboration a lot and have multiple users with both Windows and Mac accessing these files.
May 02 2020 01:51 PM
Most probably you copy/paste that data from web page. It includes so called zero-width space using in HTML.
One way to remove is to use some macro, sample of it is here https://superuser.com/questions/658125/how-do-i-remove-zero-width-characters-from-an-excel-file
One more possible way - enter in D2 formula as here
Copy D2 and Paste As value it into D3. Copy D3, select all your texts (column B here), Ctrl+H and here be sure that Find what: and Replace with: are clean (for the case backspace few times on each field), Ctrl+V (or Shift+Ins) into Find what: and Replace All.
Please note here are not visible effects - cell D3 looks empty (however =LEN(D3) is 1), Find what: is also looks empty.
Perhaps there are some other ways to replace zero-wide character, you may try to google for them - that is quite common case.