Find Max value in Alphanumeric that has multiple numbers

[Thornell]

Hi,

I've got some data that I'm trying to get a max value of in this one column called B-Tier.

The column has both numbers and text within it, but also has multiple copies of this (my organisation is annoying for this).

An example of what is included in a cell within this column would be (numbered bullet list there just to help with asking question):

         B-Tier

1.    3
2.    4
3.    <DACH Live: 3>,<Spain Live: 2>
4.    5
5.    <Brazil Live: 4>,<Canada Live: 2>,<DACH Live: 3>,<Japan Live: 3>

Is there a way to just find the highest value within those with text?

So for 3., keep only the highest value which is 3

and for 5., keep only the highest value of 4?

Cheers

[Gordonlilj · Accepted solution]

Hi,

You could try and follow the excel power query guide here Extract Largest Number (Power Query & Flash Fill solutions) and see if it gives you the result you need.

Create custom columns in power query and follow the steps in the post or you could use the code below perhaps

List.Max(
List.Transform(
Text.Split(
Text.Combine(
List.Transform(
Text.ToList([#"B-Tier"]),each if Value.FromText( _ ) is text then " " else _ ))," "),each Value.FromText(_)*1))

[Thornell]

You are a god-send my friend.

Been trying so many different things but to no avail.

Still fairly new to all this as well, but I'm the only one in our department that has any sort of experience with it so it all falls to me.

Thank you very much.

Saved me loads of time and horrible workarounds.