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
SergeiBaklan I have a similar issue with sort not working in the attached file while trying to sort the first column at first level and the second column at second level. I have changed the format of these two columns as text but that did not make a difference. Any help is appreciated.
That's another issue. Most probably you had mix of texts and numbers to which you applied text format. But Excel remembers they are numbers.
After above change you may try to sort again, the message shall appear
Result depend on which option you select. Not sure what it shall be.
That's an opposite situation - you have both numbers and texts and would like to sort everything as text. If to avoid VBA the workaround could be in using helper column where all values are converted to texts and sort by it. Easiest is to use formula like
= A1 & ""
For example, let say we have such set of values - column A is the source and column B is helper one.
In column A 33 is the number, all the rest are texts. If we sort column A in ascending order 33 will on first place
With adding helper column B and sorting by it we have correct values order
SergeiBaklan Thank you. I've tried it both ways and it still does not sort accurately. My data is a combination of text and numbers and I need all of it to be treated as text. I've even copied the column content to Notepad and pasted it back in without success.