6 digit number entries with decimal not sorting in correct order in Excel

[BDusk]

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?

[#2]

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 data

[#3]

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

-------------------------------------------------------------

[#4]

excel cannot sort that column directly based on the lead number value in a text cell.
you can create another column that extracts that lead number/value something like:
=IFERROR(--LEFT(A1:A1000, SEARCH(" ",A1:A1000)-1),"")
and then use that column to sort both columns and then delete or hide that helper column.

[#5 · Accepted solution]

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

[#6]

That is correct, if you add a 0 or any character that excel considers <1 it will sort "correctly" but that sorting is a alpha-numeric / ASCII based sorting. It is not based on the numeric value of that number in the field. It is no different than sorting aab, aac, aba, abb, ect...

[#7]

@BDusk 

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.

[#8]

Thank you for the additional information. For my purposes, the alpha-numeric sorting works just fine but this is good to know!

[#9]

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.