Excel Sort Numeric Column By Number

Solution 1:

It sounds as if your numbers are being interpreted as text by Excel. A good indication of this is that they automatically align to the left of each cell. Numbers normally are aligned to the right.

If this is the case, a simple fix is to move into a cell, press F2 then Enter. Repeat for each cell containing the numbers that don't sort. This works by putting the cell into edit mode and then out again. If the contents can be interpreted as a number then Excel converts it to a number.

If you have too many cells to do this manually, you can use the VALUE function to create another column contain true numeric values and then paste them (by value) back over the offending cells.

Solution 2:

I know this is an old post, but since it's at the top of the search results for this kind of thing...

There's a much easier way to do this. To convert numbers stored as text to numbers:

1. Select the whole column.
2. On the Data tab, click the Text to Columns button.
3. On the wizard that opens, just go ahead and click Finish. You don't need to go through the extra steps since they're for if you want to split columns.

And presto! Your column is now formatted as actual numbers and you should be able to sort smallest to largest instead of A to Z.

Solution 3:

You can do this on the data directly in a single shot by without creating working columns. My favoured method is using Paste Special (I normal multiply by the "text" by 1)

Debra Dalgleish provides 7 methods here, http://www.contextures.com/xlDataEntry03.html

The Paste Special method as listed by Debra is

• Select a blank cell
• Choose Edit > Copy
• Select the cells that contain the numbers
• Choose Edit > Paste Special
• Select Add
• Click OK
• To apply number formatting, choose Format > Cells
• On the Number tab, select the appropriate format, then click OK

Solution 4:

Just change your single digits (1,2,3,etc.) to 01, 02, 03. Then sort A to Z.