Excel Sorting Issue

doyler442

Hi

I'm just wondering if anyone has a solution to this problem I have:

I have an excel sheet as follows:

Code | Reference
A1 Some Text
A143 Some Text
A2 Some Text
A123 Some Text

I want to sort these based on there numeric value, so it would be:

Code | Reference
A1 Some Text
A2 Some Text
A123 Some Text
A143 Some Text

However when I sort, it sorts like this:

Code | Reference
A1 Some Text
A123 Some Text
A143 Some Text
A2 Some Text

Is there a way for me to sort so I get the result in the second example?


Cheers

Plates

You need to make sure the Codes (A1, A13 etc.) are formatted as Text:

http://support.microsoft.com/kb/322067

Hope that helps

seamus

Insert a new column in between the existing two. Then select column A and go to "Text to Columns"*

Choose "fixed width" and then insert the column break between the letter and the numbers (as in the screenshot).

Now you can sort the data based on the second column and it will be numerical.



*In excel 2007, this is in the "Data" tab. In Excel 2003, afaik this is under the "Data" menu, though I could be wrong.

marcus2000

doyler442 wrote: »

Hi


I want to sort these based on there numeric value, so it would be:

Code | Reference
A1 Some Text
A2 Some Text
A123 Some Text
A143 Some Text

Cheers

if you add an extra column at the start and add the formula "=Len(A1)" where A1 is the Code cell......then if you sort by the new column and by the Code column (in that order), you MAY get what you need.

basically, it will sort by the length , so anything that is just one character long will appear as A-Z, then two characters will be A1, then A2 etc etc etc...then after Z9, it will revert to A11

It works in the small example you showed above and i think the logic is right, but ya never know!!!!

chavezychavez

Or assuming that the value will always be after the first character (i.e. not longer than 1), you could put this formula in the 3rd column, which will pull out just the number on which you could sort

=RIGHT(A2,LEN(A2)-1)

doyler442

Thanks for the very quick replies, I'll have a look at these when I get a chance in awhile.


Cheers