Incorrect Sorts

From Jan, a workbook that doesn’t sort quite right. The four numbers in column A are sorted ascending with a header row


This often happens with imported or copied and pasted data. It all looks normal because the cells are right justified. If you remove the justification, you would see that only the 4 is really a number and the other numbers are really text that look like numbers. Excel sorts text and numbers separately.


This can be fixed by converting all the “text” values to actual numbers, as described here. Put a 1 in an unused cell, copy it and Paste Special > Mulitply over the range. That will coerce all the entries in the range to numbers and it will sort it correctly.

Posted in Uncategorized

3 thoughts on “Incorrect Sorts

  1. Why waste all those keystrokes typing a 1 to convert text to numbers, then later go back to delete the 1? Instead, copy a handy blank cell, and use Paste Special, Operation – Add.

    – Jon

  2. It’s so painful watching users continually press F2, Enter.
    I use the technique Jon mentions:
    copy blank/pastespecial addition

    When formulas act this way, find/replace = with =

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.