Sorting alphanumeric text as numeric values (214282)


The information in this article applies to:

  • Microsoft Excel 97 for Windows
  • Microsoft Excel 2000
  • Microsoft Excel 2002
  • Microsoft Office Excel 2003
  • Microsoft Excel for the Macintosh
  • Microsoft Excel 98 Macintosh Edition
  • Microsoft Excel 2001 for Mac
  • Microsoft Excel X for Mac
  • Microsoft Excel 2004 for Mac
This article was previously published under Q214282

SYMPTOMS

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.

CAUSE

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: 
   A1:     1
   A2:     12
   A3:     1A1
   A4:     1A2
   A5:     2
The expected sort result is 1, 1A1, 1A2, 2, 12. However, the actual result will be 1, 2, 12, 1A1, 1A2.

WORKAROUND

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
Modification Type:MajorLast Reviewed:1/20/2006
Keywords:kbdtacode kbprb KB214282
©2004 Microsoft Corporation. All rights reserved.