Get Max Value from a list of scores within a single cell

I've tried searching online and on this forum and can't seem to find a formula that works

I have text within a single cell in the following format:

Score A [1]
Score B [2]
Score C [7]

It has multiple text lines within a cell with a number in square brackets. I want the cell next to this one to return the maximum score from that string of text.

Is there a formula that will work for this?

Here is an example sheet, scores are in cell E7 and I want to return the max in E8

forum example.xlsx

Can you please update your profile with both your Excel version and Location as answers do depend on knowing this information.

And see yellow banner of how to attach a sample workbook.

Hi John,

Thanks for the message, I've updated my profile and added an example sheet

Originally Posted by Phuocam

Try this

=MAX(SPLIT(SUBSTITUTE(E7;"[";"]");"]"))

I get a "function not valid" error when I try this

with Power Query
tailored to your example

Score	Max
Emissions Failure [9] Gas Leak [8] Component wear [5]	9

// Max
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Score", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Score"),
    TBTD = Table.AddColumn(Split, "Max", each Text.BetweenDelimiters([Score], "[", "]"), type text),
    Number = List.Max(Table.TransformColumnTypes(TBTD,{{"Max", Int64.Type}})[Max])
in
    Number

Originally Posted by sandy666

with Power Query
tailored to your example

Score	Max
Emissions Failure [9] Gas Leak [8] Component wear [5]	9

// Max
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Score", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Score"),
    TBTD = Table.AddColumn(Split, "Max", each Text.BetweenDelimiters([Score], "[", "]"), type text),
    Number = List.Max(Table.TransformColumnTypes(TBTD,{{"Max", Int64.Type}})[Max])
in
    Number

Thanks or the reply, I'm trying not to use a UDF and I can't change the format of the table so trying to contain it within a formula if that's possible

This is Power Query, M not vba

look here


I didn't change your source table

Originally Posted by sandy666

This is Power Query, M not vba

look here


I didn't change your source table

Ah ok sorry, Power Query is new to me... I'll do some reading!

Originally Posted by Fluff13

How about

Formula:

=MAX(IFERROR(--(TEXTSPLIT(E7,"[","]")),0))

This returns a value of 0

Originally Posted by Fluff13

It returns 9 for me

That's weird, I pasted the formula into excel and it returned a 0... but when I copy and pasted from your sheet it worked!

Thanks for sending the attachment!