Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

Sorting alphanumeric text as numeric values


View products that this article applies to.

This article was previously published under Q214282

↑ Back to the top


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.

↑ Back to the top


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.

↑ Back to the top


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
    				

↑ Back to the top


Keywords: KB214282, kbprb, kbdtacode

↑ Back to the top

Article Info
Article ID : 214282
Revision : 7
Created on : 1/24/2007
Published on : 1/24/2007
Exists online : False
Views : 41