Forum Discussion
6 digit number entries with decimal not sorting in correct order in Excel
mtarlerWell that's the thing. It is sorting the other cells in the column correctly that have 3 numbers to the left of the decimal. Just out of curiosity, I added a "0" in front of the 97 and 85 to see what would happen and it worked! So it appears that there has to be a consistent number of numbers to the left of the decimal throughout the column for it to be able to sort.
mtarlerThank you for the reply. I followed your suggestion and it separated the data into 3 columns. It may be important to mention, which I didn't initially, that there are words following the number in the cell. What I am hoping for is to be able to sort them without having to parse the numerical quantity into its own column. Is this possible?
I tried to attach a screenshot but it appears that is not possible (perhaps my account is too new?)
Here are examples of the data in each cell. I will use dashes to simulate the cell/row divisions. The number quantity at the front is what I would like Excel to sort them by.
------------------------------------------------------------
97.010 MIZUMI 1X1 MOSAIC SEA BLUE PEARL
5 Sheets/5.45 SF/Box (17 Boxes & 4 Sheets)
------------------------------------------------------------
85.280 sq ft AGATE MARTINI VICENZA RIBBED
5 Sheets/5.2 SF/Box (16 Boxes & 2 Sheets)
------------------------------------------------------------
343.470 sq ft AGATE HEXAGON VICENZA SILK
5 Sheets/5.35 SF/Box (64 Boxes & 1 Sheet)
-------------------------------------------------------------
263.760 sq ft MIZUMI 1/2 X 4 ENGLISH BLUE NATURAL
5 Sheets/4.2 SF/Box (62 Boxes & 4 Sheets)
-------------------------------------------------------------
The 365 formula
= SORTBY( list,--TEXTBEFORE(list, " ") )
would select the first digits and convert from text to a number, which is then used to sort the list.
- Thank you Peter for this suggestion. It's a little beyond my level of Excel knowledge but I look forward to trying this at some point when I'm ready to do a deeper dive.