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]

Hi@Sergei Baklan

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).

[#3]

@Gwalior 

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.

[#4]

@Sergei Baklan 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.

[#5]

@Inquisitive 

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.

[#6]

@Sergei Baklan 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.

[#7]

@Inquisitive 

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

[#8]

I have strange sorting problem, new file, just typing 2 letters Y(problematic letter) and something between letters J to W. And trying to sort A to Z. Always letter Y are first than other letter.

[#9]

@Mantas_Gasiunas 

Did you say Excel in filtering and sorting that you have header?

[#10]

Yes, same with checked and unchecked.

[#11]

NM, found solution, seems like windows system region format was settled incorrectly.