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

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...
- BDusk
  Copper Contributor
  Oct 18, 2022
  Thank you for the additional information. For my purposes, the alpha-numeric sorting works just fine but this is good to know!