Max Value of Array Of Substrings...

Hi,

I'vd got a table with a column called key that contains a single letter and then a number of any length.

Capture.JPG

I want to get the maximum number that exists in the table column, ignoring the letter prefix.

I've tried array formulas;

{=MAX(RIGHT(Table1[@Key],LEN(Table1[@Key])-1))} returns 1
{=MAX(RIGHT(Table1[Key],LEN(Table1[Key])-1))} returns 0

and non array formulas

=MAX(RIGHT(Table1[@Key],LEN(Table1[@Key])-1)) returns 1
=MAX(RIGHT(Table1[Key],LEN(Table1[Key])-1)) returns 1

I want it to return 6, as this is the maximum...

Thanks

Dave

Try this...

Formula:

Please Login or Register  to view this content.

Great - had to be entered as an array formula and I replaced the cell references with a table column, and had to amend the "right" formula, but great...

Please Login or Register  to view this content.

It is a non array formula, so normal enter is enough

@ sixthsense

It is a non array formula, so normal enter is enough

Are you sure, I'd check that out ...

@ David

... that contains a single letter and then a number of any length.

If the number can vary in length you might be safer with this array formula

Formula:

Please Login or Register  to view this content.

or this non CSE alternative

Formula:

Please Login or Register  to view this content.

Hi david.nicholls

For Excel 2010, try the aggregate function:

Formula:

Please Login or Register  to view this content.

Enter as usual.