Sort numbers correctly in Excel
| Introduction | |
| Sort numbers correctly |
In this article, you will learn how to sort numbers that Excel treats as text.
Sort numbers correctly
Excel may sort numbers as text, producing an order such as 1, 10, 11, 12, 2, 21, 3.
In that order, Excel is comparing characters rather than numeric values. Formatting the cells
as numbers may not fix values that are stored as text.
One workaround is to perform an arithmetic operation on the values in a helper column, then sort
by that column. You can fill a formula down the column or use VBA. For example, to sort 3,000
rows using the numbers in column B:
Sub fill_cells()
Dim i As Integer
For i = 1 To 3000
Cells(i, 3).Value = Cells(i, 2).Value / 1000
Next i
End Sub
Column C now contains the values from column B divided by 1,000. Select column C, open the Data tab, and sort by it.
Article author: Andrei Olegovich