Forum Discussion
Text not sorting correctly
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
Again, similar problem, but harder for me to solve...
I've got a spreadsheet generated by some other software, which doesn't appear to filter / sort properly.
There is only one sheet. When I apply filter to the table of data on that sheet, sorting on any of the columns does not work. Nothing happens when I select any of the sort options. Filters appear to work.
If I do the following:
- Select the whole sheet (click on top left corner)
- Ctrl+C
- Create a new sheet
- Ctrl+V
- Apply Filter
Then sorting works fine!
What is wrong with the first table of data?
I've attached the problem spreadsheet (after anonymising anything sensitive).
Third party software bit incorrectly creates Excel file, it marks all cells as merged, even if physically they are not merged.
Since such "merged" cells are of different size in the same column (again, virtually)), sorting doesn't work. If simply duplicate the sheet (move & copy with creating the copy) Excel fixes an issue for the duplicated sheet.
Another way is staying on any cell within the range Ctrl+A (range will be selected), when Ctrl+1 and here clear merging setting:
With that sorting works.