# Quick Excel 2003 question. **URL:** \ **Category:** Factual Questions\ **Created:** [May 23, 2015, 3:42am UTC](https://boards.straightdope.com/t/quick-excel-2003-question/720635 "2015-05-23T03:42:29Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![racer72](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/racer72/32/3075_2.png) [@racer72](https://boards.straightdope.com/u/racer72)\ **Post date:** [May 23, 2015, 3:42am UTC](https://boards.straightdope.com/t/quick-excel-2003-question/720635/1 "2015-05-23T03:42:29Z")
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? ---
**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\ **Post date:** [May 23, 2015, 6:07am UTC](https://boards.straightdope.com/t/quick-excel-2003-question/720635/2 "2015-05-23T06:07:21Z")
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: ```auto =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](https://onedrive.live.com/redir?page=view&resid=9867ED46F373003E!4068&authkey=!ADFnoh9fTbAC1gk). ---
**Author:** ![j\_sum1](https://avatars.discourse-cdn.com/v4/letter/j/8baadc/32.png) [@j\_sum1](https://boards.straightdope.com/u/j_sum1)\ **Post date:** [May 23, 2015, 11:55am UTC](https://boards.straightdope.com/t/quick-excel-2003-question/720635/3 "2015-05-23T11:55:39Z")
I don’t believe that “&” is supported by Excel 2003. Use CONCATENATE instead. ---
**Author:** ![don\_t\_ask](https://avatars.discourse-cdn.com/v4/letter/d/e68b1a/32.png) [@don\_t\_ask](https://boards.straightdope.com/u/don_t_ask)\ **Post date:** [May 23, 2015, 12:24pm UTC](https://boards.straightdope.com/t/quick-excel-2003-question/720635/4 "2015-05-23T12:24:55Z")
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. ---
**Author:** ![racer72](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/racer72/32/3075_2.png) [@racer72](https://boards.straightdope.com/u/racer72)\ **Post date:** [May 23, 2015, 4:33pm UTC](https://boards.straightdope.com/t/quick-excel-2003-question/720635/5 "2015-05-23T16:33:53Z")
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. ---
**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\ **Post date:** [May 23, 2015, 10:37pm UTC](https://boards.straightdope.com/t/quick-excel-2003-question/720635/6 "2015-05-23T22:37:36Z")
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](http://textmechanic.com/Sort-Text-Lines.html) 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. ---
**Author:** ![racer72](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/racer72/32/3075_2.png) [@racer72](https://boards.straightdope.com/u/racer72)\ **Post date:** [May 24, 2015, 3:07pm UTC](https://boards.straightdope.com/t/quick-excel-2003-question/720635/7 "2015-05-24T15:07:19Z")
> [@Reply](#): > > 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](http://textmechanic.com/Sort-Text-Lines.html) 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. 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.