Max Value of Array Of Substrings...

[david.nicholls]

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

[:) Sixthsense :)]

Try this...

Please Login or Register  to view this content.

[david.nicholls]

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.

[:) Sixthsense :)]

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

[Marcol]

@ 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

Please Login or Register  to view this content.

or this non CSE alternative

Please Login or Register  to view this content.

[Kevin UK]

Hi david.nicholls

For Excel 2010, try the aggregate function:

Please Login or Register  to view this content.

Enter as usual.