sorting an alphanumeric list - please someone help!

J

Joanne

I posted this yesterday and got no responses. Is there someone who could
help me?
_____________________________________________
would like to sort some text which is formatted in column one as:
1'
100
1000'
2
2'
and so on. Any number that does not have an "'" at the end should come
before any number that does and all the 1s, 2s, 3s, etc. should be grouped
together. Is this possible? The result would look like:
1
100
1000'
2
2'

Boy, would I appreciate some help
 
B

Bernie Deitrick

Joanne,

If your values start in cell A1, enter this formula in cell B1:

=VALUE(LEFT(A1,1)) +
IF(ISERROR(VALUE(A1)),VALUE(LEFT(A1,LEN(A1)-1))+50000,VALUE(A1))/100000

and copy down to match your data in column A. Then select columns A and B,
and sort based on column B.

Note: if your values can be higher than 50,000 you will need to change the
50,000 and 100,000 in the formula to a higher value, like 500,000 and
1,000,000

HTH,
Bernie
MS Excel MVP
 
Top