Forum Discussion
whooooop84
Nov 20, 2018Copper Contributor
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 wil...
- 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
SergeiBaklan
MVP
If data copy/pasted from Web that could be some invisible symbols - is that the case?
whooooop84
Nov 20, 2018Copper Contributor
I may have copied part of it from another source. How do I get rid of hidden codes?
- whooooop84Nov 20, 2018Copper Contributor
I inspected the doc, found no hidden rows, columns or codes.....
- SergeiBaklanNov 20, 2018MVP
Take LEN() of any suspicious cell, if it's more than number of visible characters you have something extra.
- whooooop84Nov 20, 2018Copper Contributor
Not sure I understand your instructions -- unfamiliar with what LEN() is. Are you saying I must do this in every cell?
- Bart van den DonkJan 13, 2019Copper ContributorFirst paste your text to notepad.
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