Sort by Numeric value from Alphanumeric column

[onenessboy]

Dear experts,

I want to sort data by numeric part in alphanumeric values...


Col A
AB12343
ABCD23434
GBD453456

Now i need to sort only based on numeric value in above list...

can somebody suggest me a way in macro or someother workaround please..

[Ace_XL]

Separate the numeric values from Column A and list them in Column B by using the below. Then sort all data based on column 'B'

=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000))))

Courtesy: oldchippy
http://www.excelforum.com/excel-work...ic-string.html

[rproudfit]

You can also use this formula to extract the numeric portion of the string:

=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))

There are two important things to note in order for this formula to work properly. First, this is an array formula so you need to press CTRL+SHIFT+ENTER. Secondly, the 9 in this formula represents the maximum number of characters to evaluate. If you have a 10 character string with this formula it will truncate the last number.

Source:
http://office.microsoft.com/en-us/ex...001154901.aspx

In order to quickly find the maximum number of characters in your data, use the following array formula (this one I actually came up with):

=MAX(LEN(A:A))

Ryan

[rproudfit]

You can also use this formula to extract the numeric portion of the string:

=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))

There are two important things to note in order for this formula to work properly. First, this is an array formula so you need to press CTRL+SHIFT+ENTER. Secondly, the 9 in this formula represents the maximum number of characters to evaluate. If you have a 10 character string with this formula it will truncate the last number.

Source:
http://office.microsoft.com/en-us/ex...001154901.aspx

In order to quickly find the maximum number of characters in your data, use the following array formula (this one I actually came up with):

=MAX(LEN(A:A))

Ryan