I have a spreadsheet. The left-most column numbers each row in my own unique numbering system. The numbers run from 1-41 followed by a letter. The cells of this column are defined as “general”. I ask Excel to sort, and everything looks fine - except items 15-27 are all intermixed with some semblance of order within.
There is nothing different about these cells! Why is it doing this? Has anyone experienced anything similar? Excel has other little quirks, like allowing wrapped text, but not knowing to adjust the row height to make the cell legible! Sheesh!
All experiences, Educated Guesses, and WAGs appreciated!
I assume you’re sorting the sheet by the column with your “unique number system”.
Depending on what the letters are that follow the numbers in this column, excel must be converting them to hex or something, and then sorting them by this.
Just a WAG. Although, if that were the case, I’d guess they’d all be in a mess.
Still - I bet it has to do with the letters after the numbers…
It would help to see the data in question. It could be that there is something different once you get to #15. Keep in mind that Excel will treat certain configurations as numbers (like hex, per the other poster) but others as a text string. For example, if I type in 2004/3/29, it will treat that as a date whether I want it to or not. But if I type in an apostrophe first, Excel treats it as a text string.
You could format your numbering system in two colums with the numbers in the left and the letters in the right. Sort first by the numbers, then secondarily by the letters and you should have no prob.
If you want to use this method and display the letters and numbers as being in the same cell you could, for example, use the following formula:
If you have simply numbers formatted as “general” your sort should be okay.
If every cell item is a mix of numbers and letters, then you will have some sorting weirdness.
For example:
Something like 1,10,16 will get sorted as “1,10,16” – in numerical order, as you’d expect.
However, data points “1a,10a, and 16a” would be sorted as “10a, 16a, 1a” because the computer is sorting the data by ascii values from left to right. Since 0<a, the “10a” gets sorted before the 1. One way around this is to put a “0” before the “1a” to make it “01a.”
Could this be the source of your problem? If not, could you tell us the data points you are trying to sort?
I found there were extraneous spaces in come cells which caused the sorting to go snafuy! Several people eneter data into this spreadsheet, so I was not aware of this. Looking on the screen, the extra spaces appear transparent in asthetics. But then, going into each cell, I caught the subtle difference.
Thanks y’all! To others with a similar experience, look for those extra spaces!
To prevent users from including spaces in their entries going forward, use Excel’s Data Validation feature to check their input. Highlight the input range, and choose Data Validation from the menu. Choose “Custom” from the Allow dropdown list, and (assuming the active cell is A1), type =A1=TRIM(A1) in the Formula box. On the Error Alert tab, enter an appropriate error message, such as “Entry can not include spaces”, and click OK.
Should users try to enter a value with either leading or trailing spaces, they will see your error message and will have to fix their entry before continuing.