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
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 |
That won't help - could you please attach Excel file with few rows which not sorting
- NM, found solution, seems like windows system region format was settled incorrectly.
- Yes, same with checked and unchecked.
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.
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.
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.
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.
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.
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).
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
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)
Lee, you are welcome
You are my HERO! Thanks so much - worked beautifully!
thanks!
Lee
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
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.
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.
Sorry - due to security settings unable to do that. Guess I'd better just start retyping the whole thing.
