Microsoft KB Archive/214282

= Sorting alphanumeric text as numeric values =

Article ID: 214282

Article Last Modified on 1/24/2007

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Office Excel 2003
 * Microsoft Excel 2004 for Mac
 * Microsoft Excel 98 for Macintosh
 * 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:   Type the following values in a worksheet:   A1:     1 A2:    12 A3:    1A1 A4:    1A2 A5:    2  Select the range A1:A5, and then click Sort on the Data menu.  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  Type the following formula into cell B1: B1: =TEXT(A1,”@”) </li> With cell B1 selected, click Copy on the Edit menu.</li>  Select cells B2:B5 and click Paste on the Edit menu.

The worksheet should have the following information in it: <pre class="fixed_text">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. </li> Select the range A1:B5, and click Sort on the Data menu.</li> Under My list has, click No Header Row. Under Sort By, click Column B and Ascending.</li> Click OK.</li>  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: <pre class="fixed_text"> A1: 1   B1: 1 A2: 1A1 B2: 1A1 A3: 1A2 B3: 1A2 A4: 2  B4: 2 A5: 12 B5: 12 </li></ol>

Additional query words: sort weird wrong incorrect alpha-numeric XL2000

Keywords: kbdtacode kbprb KB214282

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.