How to sort decimal numbers in a column?
If you have a list of decimal numbers, with the built-in Sort function in Excel, the data will be sorted by value normally. But, sometimes, you need the data to be sorted by the number on the right of the decimal point as following screenshot shown. Do you have any good method to solve this task in Excel?
To sort the decimal numbers based on the number on the right of the decimal point, you should create a helper column, and then sort the data by the new helper column, please do as follows:
1. Enter this formula: =INT(A2)&"."&TEXT(RIGHT(A2,LEN(A2)-(FIND(".",A2))),"000") into a blank cell beside your data, B2, for instance, see screenshot:
2. Then drag the fill handle down to the cells which you want to contain this formula, and a new column data is displayed, and then click Data > Sort to sort the data base on this new helper column, see screenshot:
3. In the popped out Sort Warning box, please select Expand the selection option, see screenshot:
4. Then click Sort button, and in the Sort dialog box, select the helper column you have created from the Sort by drop down list, and then choose Values from the Sort On section, at last select the sort order under the Order section, see screenshot:
5. And then click OK button, another Sort Warning prompt box is popped out, please select Sort numbers and numbers stored as text separately option, see screenshot:
6. Then click OK button, and the data has been sorted by the number on the right of the decimal point as you need. See screenshot:
7. At last, you can delete the contents of the helper column as you need.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 4 days agothis didn't work for me. 4.1 and 4.10 both turned into 4.001
- To post as a guest, your comment is unpublished.· 10 months agoThis is much easier if you're going to use a helper column... Copy the number list you're trying to sort into a helper column and remove all of the delimiters using Find & Replace ("." or ",", etc.) from those numbers. Sort lowest to highest, and your list will be in the right order.
- To post as a guest, your comment is unpublished.· 2 years agowhy not just normalize the length after the decimal instead of prefixing zeros after the decimal? e.g. 5.1 becomes 5.10,boom sorted?!