Quick Excel 2003 question.

Factual Questions
1

I have a list in alphanumeric format. Excel sort follows the alpha part just fine but not the numeric.

a1
a2
a11
a12
a33
a44
a103

The above is what I want. What I get is:

a1
a11
a12
a103
a2
a33
a44

Is there a simple way to get what I want?

2

Assuming:

  • Your data is in A2, A3, etc.
  • Each cell has exactly one letter (no more or fewer) before the number

Make a second column whose formula is:


=VALUE(CODE(A2) & MID(A2,2,999))

And sort it by that. This causes Excel to first convert the letter to its ASCII code value (A=97, B=98, etc.), prepend that to the rest of the number, and convert the result into a sortable number.

Example file here.

3

I don’t believe that “&” is supported by Excel 2003. Use CONCATENATE instead.

4

If there are a variable number of letters before the numbers and/or no data validation, such that letters could be both upper and lower case, a better approach may be:

In column C labelled say Letters:

=LEFT(A2,MIN(IF(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},A2)),FIND({1,2,3,4,5,6,7,8,9,0},A2)))-1)

In column D labelled say Numbers:

=VALUE(MID(A2,MIN(IF(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},A2)),FIND({1,2,3,4,5,6,7,8,9,0},A2))),999))

Then sort by C then D.

5

I was hoping for something simple using the sort function. Guess I simplified my question too much. I have a list of Crayola crayon products and as received the items were in alphanumeric order. I added some info, instead of adding rows, I added the information at the bottom then used the sort function.

Crayola No 48
Crayola No 48 Crayola No 48
Crayola No 48 - A Room full of Fun
Crayola No 480
Crayola No 480
Crayola No 48P
Crayola No 49
Crayola No 51
Crayola No 53
Crayola No 54
Crayola No 55
Crayola No 562
Crayola No 57
Crayola No 6

This is a sample of what I have now. When I started, Crayola No 6 was first, 48P, 49 and 51 were above 480 and 562 was last. Guess I can live with it the way it is.

6

Hey racer72, I don’t know exactly what you’re trying to do, but if all you want to do is sort a long list of Crayolas in order, Excel probably isn’t the right tool for the job. You can just copy and paste that whole list into a sorter like this one and click “natural” and it’ll come out the way you expect. In the computing world, this is called “natural sorting” – sorting the way humans would expect, not the way computers like to do it. Excel doesn’t do a very good job at it out of the box, but many other utilities do it fine.

If you can live with it, that’s fine, but if not, feel free to give us a more concrete example (or the actual spreadsheet you’re working on) we can help further.

7

The site worked good for one column, I have 10 columns of info. But it did what I wanted for that one column and a half an hour of copy and pasting gave me the results I needed. Thanks.