CODEWITHSUNDEEP
excelsortingfilterexcel-formula
Andrei Ion
Andrei Ion

Reputation: 1827

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 Thanks a lot!

Upvotes: 2

Views: 5242

Answers (3)

momobo
momobo

Reputation: 1775

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.

Upvotes: 3

Ian P
Ian P

Reputation: 1724

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

Upvotes: 1

Shanty
Shanty

Reputation: 284

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 :)

Upvotes: 0

Related Questions