Correcting sorting problems with text and numbers

Applies to
Microsoft Excel 2000

Correcting formats and retyping

If only a few rows were out of order when you sorted the list, the quickest way to correct the problem is to format any text cells as numbers and then re-enter the values to store them as numbers.

  1. In the sorted list, determine which rows are out of order.
  2. Click a cell that contains a number stored as text.
  3. On the Format menu, click Cells.
  4. In the Category box , if Text is selected, click a number format, such as Number or Currency.
  5. Do one of the following:
    • If the number in the cell begins with an apostrophe, delete the apostrophe in the Formula Bar, and then press ENTER.
    • If you changed the number format, press F2 and then press ENTER to store the value as a number displayed with the selected number format.
    • If the cell is blank, enter a zero in it.
  6. Repeat steps 2 through 5 for each out-of-sequence row.
  7. Repeat the sort operation. The rows should now be in the correct order.

Sorting a large list

If you have a large amount of data that contains numbers stored as text, such as a column in an external data range that you refresh periodically, it might be time-consuming to find and retype all of the numbers. You can use the following method to sort the data:

  1. In a blank column that's formatted as General or a number format, enter a formula using the VALUE function to extract the value of the first cell in the column you're correcting.

For example, you could use column C to extract the values in column B:

Using VALUE to prepare data to sort

  1. Use the fill handle to copy the formula down the column to extract all of the values.
  2. Sort the data by the column that contains the VALUE formulas. The rows should now be in the correct order.
  3. If you won't need to sort the data again, delete the column of VALUE formulas. If you want to keep this column, you can convert the formulas to values: select the column, click Copy on the Edit menu, click Paste Special on the Edit menu, and then click Values.

Correcting values in a large list

If you want to correct numbers stored as text, try the following alternative method:

  1. In a blank cell that's formatted with the General number format or another number format (not text), type 1 and press ENTER.
  2. Click the cell containing 1 and click Copy on the Edit menu.
  3. Select the cells in the column you're correcting.
  4. On the Edit menu, click Paste Special, and then select Multiply. This operation multiplies the value in each cell by 1, which causes Excel to store the values as numbers.

More information

  • For information about how to sort data in Excel, type sort in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search.
  • For more information about the sort orders Excel uses, type default sort order in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search.
  • For more information about the VALUE worksheet function, type value function in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search.