Formula to get MAx value if the Columns comes with a string character before the value

[drom]

Hi and thanks in advance"


Formula to get MAx value if the Columns comes with a string character before the value
Please I do not want to use more columns, (Mandatory)

I have in a Column in a Table with the following values:

A22
A56
A89
A102
A1
A9
A21
​

I do know how to extract just the values in other column and get the max value of this new column.

But I do not want to use any other Column

So If my Values are in Column Z5:Z???


I woul like to create a Formula in Z2 to give me:
102 (In this eg, 102 will be the max value)

ps: the column Z in my Table, always comes with a Character plus a value on it

 

[Fluff]

Assuming you will only have 1 alpha at the start of the string
How about

Excel 2013/2016
	Z
3	102
4
5	A22
6	A56
7	A89
8	A102
9	A1
10	A9
11	A21
Sheet2

Cell Formulas
Range	Formula
Z3	{=MAX(RIGHT(Z5:Z11,LEN(Z5:Z11)-1)*1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[drom]

HI and Thanks again!

The Formula works Fine, But I did not say, (My Mistake) sometimes the cells are coming with no Value just the Character or Empty so In those cases I get Error (#Value!) within the Array

{5;#Value!:1;45;#Value!;#Value!}
ANy Help?

 

[Fluff]

How about

Excel 2013/2016
	Z
3	102
4
5	A22
6	A56
7
8	A102
9	A1
10	A
11	A21
Sheet2

Cell Formulas
Range	Formula
Z3	{=MAX(IF((Z5:Z11<>"")*(ISNUMBER(RIGHT(Z5:Z11,LEN(Z5:Z11)-1)*1)),RIGHT(Z5:Z11,LEN(Z5:Z11)-1)*1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[aqeelnokia99]

try this its good
MAX(SUBSTITUTE(B1:B9,"A",""))

 

[Rick Rothstein]

How about

Excel 2013/2016
	Z
3	102
4
5	A22
6	A56
7
8	A102
9	A1
10	A
11	A21
Sheet2

Cell Formulas
Range	Formula
Z3	{=MAX(IF((Z5:Z11<>"")*(ISNUMBER(RIGHT(Z5:Z11,LEN(Z5:Z11)-1)*1)),RIGHT(Z5:Z11,LEN(Z5:Z11)-1)*1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Assuming your setup, the following array-entered** formula also seems to work...

=MAX(IF(ISNUMBER(Z5:Z11),Z5:Z11,0+(0&MID(Z5:Z11,2,99))))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

Note: I assume that the maximum length of an entry in a cell is 99 characters.

 

[Marcelo Branco]

Another...

Regular formula
=AGGREGATE(14,6,--RIGHT(Z3:Z11,LEN(Z3:Z11)-1*NOT(ISNUMBER(--Z3:Z11))),1)

M.