Forum Discussion
6 digit number entries with decimal not sorting in correct order in Excel
I am entering data into an Excel spreadsheet that I would like to sort from the largest quantity to smallest. The numbers include a decimal with 3 places. When I select the column and sort it from largest to smallest, Excel is placing the rows with 85.280 and 97.010 as being a larger quantity than cells with entries such as 343.470 and 263.760. I thought maybe that Excel is only taking the first number into consideration but all the cells that have numbers with 3 digits to the left of the decimal are sorting correctly. I've tried formatting the cells into every possible option with no change. How can I make Excel realize that 85.xxx is not bigger than 343.xxx?
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.
8 Replies
- It definitely sounds like excel is not recognizing those entries as numeric numbers. You could try adding 2 of them together (=A1+A2) and see if excel adds them or gives an error. A few possibilities include: data was imported as text, the column was set to text when data was entered, excel is set to use comma as decimal separator and period as thousands separator (but even if that was the case if you always have 3 decimal places it would just look like a thousands separator instead of a decimal separator).
as for what to do, maybe try highlighting the column and under Data -> Data Tools select 'Text to Columns' and walk through the wizard to convert the datamtarlerThank 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.
