Text not sorting correctly

[whooooop84]

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. 

[#2]

If data copy/pasted from Web that could be some invisible symbols - is that the case?

[#3]

I may have copied part of it from another source.  How do I get rid of hidden codes?

[#4]

I inspected the doc, found no hidden rows, columns or codes.....

[#5]

Take LEN() of any suspicious cell, if it's more than number of visible characters you have something extra.

[#6]

Not sure I understand your instructions -- unfamiliar with what LEN() is.  Are you saying I must do this in every cell?

[#7]

Nope, just one cell to test if it is really some invisible character here. If so, you may CLEAN() all cells into new column and sort on it. Or Ctrl+H such character if you find what is it.

Perhaps you may attach some sample file to test.

[#8]

I tried it on a row that I had moved from the lower half to the upper half and then sorted -- it moved it back to the lower half.  Sigh.  Here is a small sample -from the bottom of the top section and the top of the bottom section.....

This post gave me an error when first posted:  "Your post has been changed because invalid HTML was found....." -- could this mean it has hidden code?

​Weighted Shortest Job First (Agile)	​Weighted Shortest Job First (WSJF) is an economic model for prioritizing “jobs” based on product development flow. WSJF is calculated as the cost of delay divided by job duration. In SAFe, “jobs” are the epics, features, and capabilities that are developed by ARTs. There are three primary elements to the cost of delay: 1) user-business value, 2) time criticality, and 3) risk-reduction opportunity enablement value.
​X	​Presale - X=New Business
​Y	​Off-Y Renewal= Division Add/Product Add on Existing Business
​YTD	​Year to Date
​ZCD	​Zero Critical Defects
​ZR	​Renwal - ZR= Existing Business Renewal
Adj	Adjustment
AFIT	After Federal Income Tax
Agile	A development methodology that centers around the need to think fast, be flexible, innovate, and bring products and services to market quickly.  – At its heart, Agile is about responsiveness to change, continuous learning, collaboration, and doing the most valuable thing first. Agile is a highly disciplined, iterative methodology, which divides the product being developed into small units of work. For teams developing software, it can help teams respond to the unpredictability of building software through incremental, iterative work cadences known as iterations.
AIMS	Accelerated Integrated Market Solutions.
AM	Agile Modeling

[#9]

That won't help - could you please attach Excel file with few rows which not sorting

[#10]

Sorry - due to security settings unable to do that.  Guess I'd better just start retyping the whole thing. 

[#11]

I see. However, you may copy your file, remove all sensitive information and keep only these few rows which were in your previous post. But as Excel file.

[#12]

Sorry Sergei -- I know you're trying to help me -- but I don't have the time to copy the entire file, delete sensitive information on every row........my system will not allow me to upload or attach a file to something going out.  I'd be better off spending my time recreating the spreadsheet.  I appreciate your assistance.

[#13 · Accepted solution]

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

[#14]

You are my HERO!  Thanks so much - worked beautifully!

thanks!

Lee

[#15]

Lee, you are welcome

[#16]

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)

[#17]

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

[#18]

First paste your text to notepad.
Then (re)select the text in notepad, copy and paste it to Excel and try again.

[#19]

Hi Bart,

How Notepad helps with ignoring hyphens on sorting?

[#20]

@Sergei Baklan 

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