How can I sort by number when I have letters in front?

I have a a list with some names of some products... Usually the names are something like this IR123456 or AR123456... if I'll sort them I'll have all the names that contain AR ahead that the ones with IR. The problem is that this are pairs of produts and I want them one below another... something like AR123456 IR123456 AR123457 IR123457 instead of AR123456 AR123457 IR123456 IR123457 Than开发者_如何学Pythonks a lot!

Suppose the names are in the A column. Create a sort column (in this example on B column). In the sort column use only the numbers.

Product     Sort
AR123456    =MID(A2,3,100)
IR123456    (drag or copy the formula above)
...         ...
IR123457    (drag or copy the formula above)

Then sort on the B column.

You can use the substitute function (nested) to replace any non numeric characters with an empty string "" and this will leave you with the numberic part

A1 = "abc dog"
A2=SUBSTITUTE(A1,"a","") 
contents "bc dog"

which you can then sort against

One way is to extract the substring out of each string where the substring contains only numerical part. maintain an associative array (key=>value) to store the the numerical part and the actual string. Sort the array based on the numerical part (either based on key or based on value) and u'll get the original string sorted :)