maximum number from a text range

C

Cumberland

Cells A1:A3 contain the following:

AZ1
AZ2
AZ3

I need a worksheet function that can give me the number 3 as th
maximum number from the range.

I would need to strip away the letters from the entry and convert th
"text" number to numeric using the MID and VALUE functions, and I kno
that using *VALUE(MID(A1,3,1))* would give me the answer 1 for jus
-that- particular cell, but can this be applied to a range, and can
get the maximum number from that said range?

So, in an ideal world, I would use *MAX(VALUE(MID(A1:A3,3,1)))* but
know that I can't - so, is there a suitable alternative?

Thanks in anticipation
 
D

Domenic

Try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER...

=MAX(IF(A1:A3<>"",MID(A1:A3,3,1024)+0))

Hope this helps!
 
Top