How to correctly sort alphanumeric data in Excel (322067)

MORE INFORMATION

When you sort a column that contains alphanumeric characters, the sort may return unexpected results. Excel sorts the values left to right, character by character. For example, if a cell contains the text "A100," Excel places the cell after a cell that contains the entry "A1" and before a cell that contains the entry "A11."

Excel sorts data in the following order:

0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
				

Apostrophes (') and hyphens (-) are ignored, with one exception: If two text strings are the same except for a hyphen, the text with the hyphen is sorted last.

For example, if you use Excel to sort the following column of data

a1
a2
a3
1
1a
2
2a
3
3a

by default, the result of the sort is:

1
2
3
1a
2a
3a
a1
a2
a3
				

To correctly format and sort alphanumeric data

Format data by using the TEXT function

If you format data by using the TEXT function, Excel converts the values to text in a specific number format.

The syntax for the TEXT function is where value is a numeric value, or a formula that evaluates to a numeric value, or a reference to a cell that contains a numeric value; and format_text is a number format in text form from the Category box on the Number tab in the Format Cells dialog box.

NOTE: Formatting a cell with an option on the Number tab (Cells command, Format menu) changes only the format, not the value. Using the TEXT function converts a value to formatted text, and the result is no longer calculated as a number.

Examples of how to use the TEXT function are: For example, follow these steps to format the data in column A of your worksheet by using the TEXT function:

1. Insert a new column B into your worksheet.
2. In the cell B1, type the following formula:

   =TEXT(A1,"###")

   This formula copies the value in A1 to B1, and converts the value to formatted text. The result is no longer calculated as a number.
3. Copy this formula to the other cells in column B.
4. Click a cell in column B, and then click Sort Ascending or Sort Descending on the Standard toolbar.
   
   Excel sorts the data as follows:

   1
   1a
   2
   2a
   3
   3a
   a1
   a2
   a3

Troubleshoot sorting

To troubleshoot sorting problems, check the following points.

Check the default sort order rules

Excel sorts data according to specific sort order rules. In an ascending sort, Excel uses the following order. (In a descending sort, this sort order is reversed except for blank cells, which are always placed last.)

Make sure that numbers are in a numeric format

If Excel incorrectly sorts a cell that contains a value, the cell might be formatted as text and not as a number. For example, negative numbers from some accounting systems become text when the accounting data is imported into Excel. To convert a range of numbers that are stored as text to numbers, follow these steps:

1. In an empty cell, enter the number 1.
2. Select the cell, and then click Copy on the Edit menu.
3. Select the range of numbers that are stored as text that you want to convert.
4. On the Edit menu, click Paste Special.
5. Under Operation, click Multiply.
6. Click OK.
7. Delete the content of the cell that you entered in step 1.

Note Some accounting programs display negative values with the negative sign (-) to the right of the value. To convert the text strings to values, you must return all the characters of the text string except the right-most character (the negative sign), and then multiply the result by -1. For example, if the value in cell A2 is "156-" the formula =LEFT(A2,LEN(A2)-1)*-1 converts the text to the value -156.

Make sure that mixed data is formatted as text

If the column that you want to sort contains both numbers and numbers that include text characters (such as 100, 100a, 200, 200a), format them all as text. If you do not, Excel sorts the numbers first, and then sorts the numbers that include text characters.

To format a number as text, follow these steps:

1. Click the cell that contains the value that you want to format as text.
2. On the Format menu, click Cells, and then click the Number tab.
3. In the Category list, click Text, and then click OK.
4. Retype the value in the cell.

To type a number as text when you are entering new data, format the cell as text before you start typing.

Make sure that dates and times are formatted correctly

Excel treats dates and times as numbers. When you type a date or time that Excel recognizes, the cell's format changes from the General number format to a built-in date or time format.

For Excel to sort correctly, all dates and times in a column must use a date or time format. If Excel cannot recognize a value as a date, time, or number, the value is formatted as text.

To apply the correct formatting, follow these steps:

1. Click the cell that you want to reformat.
2. On the Format menu, click Cells, and then click the Number tab.
3. If the cell is formatted as text, click either Date or Time, select the appropriate type, and then click OK.
4. Retype the value in the cell in the format that you selected.

Unhide rows and columns before you sort

Hidden rows are not moved when you sort rows, and hidden columns are not moved when you sort columns. However, when you sort rows, the data in hidden columns is sorted, and when you sort columns, the data in hidden rows is sorted. Before you sort the list, unhide the hidden rows and columns.

Remove any leading spaces

In some cases, data that was imported from another program contains leading spaces. Remove the leading spaces before you sort the data.

Enter column labels in only one row

If you need multiple line labels, wrap the text in the cell.

Check the settings for graphic objects

The objects' settings may have been changed so that the objects do not move with cells. To set objects so that they can be sorted with cells, follow these steps:

1. On the Drawing toolbar, click Select Objects.
2. Drag a dotted box over the objects that you want to change.
3. On the Format menu, click AutoShape, Picture, TextBox, WordArt, Control, or Object.
4. On the Properties tab, click Move but don't size with cells.