maximum number from a text range
 

Cells A1:A3 contain the following:

AZ1
AZ2
AZ3

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

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

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

Thanks in anticipation!


--
Cumberland
------------------------------------------------------------------------
Cumberland's Profile: http://www.excelforum.com/member.php...o&userid=33445
View this thread: http://www.excelforum.com/showthread...hreadid=536409

maximum number from a text range
 

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!

In article ,
Cumberland
wrote:

Cells A1:A3 contain the following:

AZ1
AZ2
AZ3

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

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

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

Thanks in anticipation!

maximum number from a text range
 

Thanks, it's worked!


--
Cumberland
------------------------------------------------------------------------
Cumberland's Profile: http://www.excelforum.com/member.php...o&userid=33445
View this thread: http://www.excelforum.com/showthread...hreadid=536409