Get MAX value of column, where value has to be extracted first

[mrdoe]

Is there a way to get the max value in a column, where the actual number has to be extracted first (e.g. with LEFT())?
I want to get the max value for those lines where the initial character is Y for example:

X10
X14
Y21
Y23
Y4

I tried the following formula, but it doesn't work. It looks like it uses just the same row as the formula was entered and not the complete column range. The problem seems to be that LEFT and MID don't support ranges as input. I want to avoid having a separate column(s) just to prepare the input for the MAX function.
=MAX(VALUE(IF(LEFT(A:A;1)="Y";MID(A:A;2;10))))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[mrdoe]

The version is 2019

 

[Fluff]

Thanks for that.
How about

Fluff.xlsm
	A	B
1
2	X10	23
3	X14
4	Y21
5	Y23
6	Y4
Lists

Cell Formulas
Range	Formula
B2	B2	=MAX(IF(LEFT(A2:A6)="Y",MID(A2:A6,2,100)+0,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[mrdoe]

ok that would work, but it stills needs another column, right? I would like to have the max value in the one of header texts. In your example I couldn't move the formular from B2 to A1 for example.

 

[Fluff]

It can go in A1

Fluff.xlsm
	A
1	23
2	X10
3	X14
4	Y21
5	Y23
6	Y4
7
Lists

Cell Formulas
Range	Formula
A1	A1	=MAX(IF(LEFT(A2:A6)="Y",MID(A2:A6,2,100)+0,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[mrdoe]

Thanks a lot, that worked. Don't know what I did differently last time I tried, probably forgot CTRL+SHIFT+ENTER.

 

[Fluff]

You're welcome & thanks for the feedback.