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
Lee, you are welcome
Bart van den Donk
Jan 11, 2019Copper Contributor
Maybe it's another Text sorting problem, but hey there is already a toppic here.
When I activate Datafilter I can choose for sorting on a column. (in this case all is text!)
Strangely enough text with - (minus-sign) within, sort first removes them to put them back after sorting?
G-BOS
Geluid
G-PLAN
I expected:
G-BOS
G-PLAN
Geluid
Why? (I am using Office 365 / 2019)
- SergeiBaklanJan 11, 2019MVP
I guess that's by design from very beginning - Excel ignores apostrophes (') and hyphens (-) when sorts.
The workaround could be to replace all hyphens CHAR(45) with symbol CHAR(196), don't know how it is called. For that select you range, Ctrl+H, minus in first bar and keeping pressed Alt type 196 on numpad, release Alt. It looks like
Next time please start new conversation with separate question - go to https://techcommunity.microsoft.com/t5/Excel/bd-p/ExcelGeneral and click Start a New Conversation button