Forum Discussion
Irregular Sorting in Excel
Decode the error message.
A workaround could be:
=SUBSTITUTE(D2,UNICHAR(8203),"")And then copy/paste the cells as values.
Oops, we answered practically simultaneously
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.
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.
