Sorting alphanumeric text as numeric values

In Microsoft Excel, a value formatted as a number will be sorted differently than a number formatted as text. Because of this difference, you may receive unexpected results when you mix numeric and text strings in a sort.

When Microsoft Excel sorts text, it does so one character at a time from left to right. For example, if you sort the values 1 and 1A, when these values are formatted as text, the text with the fewest number of characters (1) is at the top of the sorted values, while text with the greatest number of characters (1A) is at the bottom. Each character is then sorted from 0 to 9 and then from A to Z.

For example, suppose you have the following values in a worksheet: The expected sort result is 1, 1A1, 1A2, 2, 12. However, the actual result will be 1, 2, 12, 1A1, 1A2.

To achieve the expected sort result, use the TEXT function to create a second column as a sort key.

How to Use the TEXT Function

To use the TEXT function, follow these steps:

1. Type the following values in a worksheet:

      A1:     1
      A2:     12
      A3:     1A1
      A4:     1A2
      A5:     2
   				

2. Select the range A1:A5, and then click Sort on the Data menu.
3. Under My list has, click No Header Row. Under Sort By, click Ascending.
   
   The worksheet will be sorted as follows:

      A1:     1
      A2:     2
      A3:     12
      A4:     1A1
      A5:     1A2
   				

4. Type the following formula into cell B1: B1: =TEXT(A1,�@�)
5. With cell B1 selected, click Copy on the Edit menu.
6. Select cells B2:B5 and click Paste on the Edit menu.
   
   The worksheet should have the following information in it:

   A1: 1   B1: 1 
   A2: 2   B2: 2 
   A3: 12  B3: 12 
   A4: 1A1 B4: 1A1 
   A5: 1A2 B5: 1A2
      
   				

   Note Column B will be left-aligned.
7. Select the range A1:B5, and click Sort on the Data menu.
8. Under My list has, click No Header Row. Under Sort By, click Column B and Ascending.
9. Click OK.
10. In Excel 2002 and Excel 2003, in the Sort Warning dialog box, select Sort numbers and numbers stored as text separately and then click OK.
    
    The sort should return the following values:

      A1: 1   B1: 1 
      A2: 1A1 B2: 1A1 
      A3: 1A2 B3: 1A2 
      A4: 2   B4: 2 
      A5: 12  B5: 12