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

↑ 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

## Applies to:

↑ 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