Forum Discussion

Copper Contributor

Oct 12, 2022

Solved

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

excel

BDusk
Oct 13, 2022
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.

mtarler

Silver Contributor

Oct 12, 2022

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

BDusk

Copper Contributor

Oct 12, 2022

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)

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

PeterBartholomew1
Silver Contributor
Oct 13, 2022
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.
- BDusk
  Copper Contributor
  Oct 18, 2022
  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.
mtarler
Silver Contributor
Oct 12, 2022
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.
- BDusk
  Copper Contributor
  Oct 13, 2022
  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.
  - mtarler
    Silver Contributor
    Oct 13, 2022
    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...