Forum Discussion
Text not sorting correctly
- Nov 20, 2018
I played with the text in your post. Without Excel file not definitely sure, but most probably you have Unicode symbol Zero Width Space https://www.fileformat.info/info/unicode/char/200b/index.htm at the front of some of your strings.
How to fix without formulas
Your source text sorted ascending looks like
In any empty cell, let say in D2, make reference on any cell with text in question, here is =A2. Next cell shows that string has 2 symbol length, even if we see only one. In F2 enter the formula =LEFT(D2,1) which extracts that Unicode symbol. Enter and Ctrl+C (copy) the cell.
After that press Ctrl+F to open Find window, click on Find what bar and paste the symbol by Ctrl+V. After that click Replace tab
and in it Replace All
(Please note, if you start with Ctrl+H, Find & Replace window, above doesn't work).
That removes Zero Width symbol in your sheet and in particular in D2 you see "normal text" with length as expected
Re-sort your table - it shall be in right order now.
Again, that's probable scenario, but I'm not sure without the Excel file.
Good luck
I may have copied part of it from another source. How do I get rid of hidden codes?
Then (re)select the text in notepad, copy and paste it to Excel and try again.
- SergeiBaklanJan 14, 2019MVP
Hi Bart,
How Notepad helps with ignoring hyphens on sorting?
- ss642001Dec 17, 2019Copper Contributor
after coping the column data to notepad
you prepare the excel column to text and then copy the data to the column
all is copied as text it works for me