Finding the Highest Value in alphanumeric strings

**Barking_Mad** · 10-28-2009, 11:06 AM

Hi,

I have a column of one to three digit numbers (I5:I34), some of which contain an asterisk after them. In I35 i want a formula to find the highest value from that column. Additionally, if two numbers in the column are the same value, but one contains an asterisk after it, it should use that value in I35, instead of the one without the asterisk.

Any help appreciated!

**jj72uk** · 10-28-2009, 11:14 AM

Would this work...

=max(I5:I35)

**Barking_Mad** · 10-28-2009, 11:22 AM

Originally Posted by jj72uk

Would this work...

=max(I5:I35)

The presence of the asterisk means this won't work. I think....

**DonkeyOte** · 10-28-2009, 12:12 PM

Perhaps

=SUBSTITUTE(MAX(--(0&SUBSTITUTE(I5:I35,"*",".1"))),".1","*")
confirmed with CTRL + SHIFT + ENTER

EDIT: should add for non-UK bods that the only reason I know the above will work is that all scores will be whole numbers (cricket - batting scores) hence use of .1

**Barking_Mad** · 10-28-2009, 02:34 PM

Originally Posted by DonkeyOte

Perhaps

=SUBSTITUTE(MAX(--(0&SUBSTITUTE(I5:I35,"*",".1"))),".1","*")
confirmed with CTRL + SHIFT + ENTER

EDIT: should add for non-UK bods that the only reason I know the above will work is that all scores will be whole numbers (cricket - batting scores) hence use of .1

Thanks for your help again, this kind of formula is way out of my comfort zone

The value it is showing is 0, even though it has scores of 70*, 30, 25

Any ideas?

**DonkeyOte** · 10-28-2009, 02:58 PM

Did you commit the formula with CTRL + SHIFT + ENTER as advised ? (ie not just enter as normal)

If the array is set correctly the formula will appear encased within { }