Forum Discussion
Text not sorting correctly
I have one spreadsheet with 2 columns and 555 rows - I have made sure I have no leading spaces, no hidden rows or columns, no filters -- I've attempted formatting as Text and as General -- but it will sort correctly a portion (to row 314) then a second section correctly sorted. I've tried everything I've seen suggested -- I've even cut the bottom section and inserted into the top section - resorted -- and it still split the data into two separate groups.
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
If data copy/pasted from Web that could be some invisible symbols - is that the case?
I may have copied part of it from another source. How do I get rid of hidden codes?
I inspected the doc, found no hidden rows, columns or codes.....
