It depends on what the numeric portion of the string is.
This array formula** will work on your posted sample data.
=INDEX(A1
1,MATCH(TRUE,COUNTIF(A1
1,">"&A1
1)=0,0))
** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
However, if this was your data:
AA100
AA08
AA07
AA30
The formula would still evaluate AA30 as being the "max" value in the range.
If you want to see how Excel evaluates this just sort the data in ascending
order and you'll get:
AA07
AA08
AA100
AA30
If you only want to evaluate the numeric portion of the string like this:
7
8
30
100
Then it get's kind of complicated!