Irregular Sorting in Excel

[Paul_Castelino]

I am having a very strange issue. The SORT result of a Data Column in an Excel file is incorrect when I sort using my Windos Laptop. However, when I do the same sorting function from another computer (or by another User on his computer), the data sorts correctly.

I have tried copying the data (paste values only) into a new file but the results are still the same.

[#2]

@Paul_Castelino 

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?

[#3]

I tried Sorting thru the DATA, SORT option as well as using the SORT formula. Both give the same details from my Laptop.

The file is stored in OneDrive / Sharepoint.

[#4]

@Paul_Castelino 

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.

[#5]

Hi. Does Regional Sertings really effect sorting of TEXT? My Data instead of sorting from 1-9 and then A-Z is sorting randomly (only on my computer). The order is not making sense. And as I mentioned if I use my iPad to sort the same data, it sorts in correct order i.e. 1-9 and A-Z.

Other files on my Laptop are sorting in correct order, so dont think it's a Regional Sorting Issue.

Also moved the file from OneDrive to my local drive, but the sorting is still irregular.

[#6]

@Paul_Castelino 

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?

[#7]

Select one cell in the column you want to sort.
Press Ctrl + A, to select the entire region.
Check the selected area, to make sure that all the data is included.
On the Excel Ribbon, click the Home tab.
In the Editing group, click the arrow on Sort & Filter.
Click Custom Order.

[#8]

Correct.

[#9]

Data is in a Table format so the Sorting steps are correct. The issue is that ot does not sort in correct order from my Laptop but the same file sorts correctly from other devices.

[#10]

@Paul_Castelino 

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.

[#11]

@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.

[#12]

@Paul_Castelino 

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))

[#13]

@Paul_Castelino 

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.

[#14]

@Sergei Baklan 

@Detlef Lewin 

Tried entering the formula but its giving an error message.

I hope you have the file. What corrections would I need to do?

[#15]

@Paul_Castelino 

Decode the error message.

A workaround could be:

=SUBSTITUTE(D2,UNICHAR(8203),"")

And then copy/paste the cells as values.

[#16]

@Paul_Castelino 

You may use

=SUBSTITUTE(C2,UNICHAR(8203),"")

[#17]

@Detlef Lewin 

Oops, we answered practically simultaneously

[#18]

@Sergei Baklan 

Yeah, I practically waited an extra 40 seconds for that to happen. :)

[#19]

@Detlef Lewin 

@Sergei Baklan 

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.

[#20]

@Paul_Castelino 

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.