Rank string ignoring blank cells and o value cells

Using formula, how can rank the following in a column which can have blanks and 0s? "A" ranks higher than "C", and 002 ranks higher than 123.

A.6.0.00023
A.5.0.00024
COM123 .1.0.00025
COM123 .2.0.00026
COM123 .1.0.00027
COM002 .1.0.00028
COM003 .2.0.00029
COM003 .1.0.0003
COM003 .2.0.00031
COM003 .2.0.00032
COM123 .2.0.00033
COM003 .2.0.00034
A.2.0.00035
COM003 .1.0.00036
COM123 .1.0.00037

Hi Bob,

This is probably NOT what you want, but I would hate to think how big and confusing a formula would have to be for you to achieve what you want. Even a UDF would be quite complex.

I did however, use Text to Columns (in two steps) to break up the codes, and then a fairly convoluted sort to achieve what I think you want.

See attached.

Is this of any assistance?

David


When you reply please make it clear WHO you are responding to by mentioning their name.

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Hi David

Thanks for your assistance. It's not what I was after (because it would have to be manual intervention) but I suspect you are 100% right.

kind regards

Hi,

How about this. I thought I posted this yesterday, but apparently I forgot lol.

I hope this helps