How to find out the max number within a text column?

[LawrenceMa]

Hello,

I have a table named "Data Table", and a column called [Item Number] as below.

10001

10002

K0001

K0002

20001

20002

30001

I am trying to find out the max value which is starting with "2". The answer should be 20002 here.

However my formula as below result in 0:

MAX(LIST(0) + SELECT(Data Table[Item Number], left(Data Table[Item Number],1)="2"))

May I know how can I amend my formula to find out the max value which is starting with "2" in a text column. Thank you.

[WillowMobileSys]

---

The syntax of your expression is wrong.  It needs to be like this:

MAX(SELECT(Data Table[Item Number], left([Item Number],1)="2"))

There is also a STARTSWITH()  function so you could do this:

MAX(SELECT(Data Table[Item Number], StartsWith([Item Number], "2")))

[LawrenceMa]

Hello WillowMobileSys,

But I got error message as below:

The inputs for function 'MAX' should be a list of numeric values

Just becasue the type of Data Table[Item Number] is Text. Any thoughts please?

[Aurelien]

Hi @LawrenceMa 

I would add a virtual column for getting a numeric value first.

For example, let's name this virtual column "itemValue". Based in @WillowMobileSys 's suggestion, it would be:

IF(STARTSWITH([Item Number], "2"),
EXTRACTNUMBERS([Item Number]),
0
)

Then, your expression would be:

MAX(table[itemValue])

For reference:

EXTRACTNUMBERS() - AppSheet Help

[LawrenceMa]

Hello Aurelien,

I got error message when creating first given formula:

EXTRACT has invalid inputs

Any thoughts please?

[Aurelien]

My bad.

Can you try instead:

IF(STARTSWITH([Item Number], "2"),
ANY(EXTRACTNUMBERS([Item Number])),
0
)

EDIT:

---

@LawrenceMa wrote:

I got error message when creating first given formula:

EXTRACT has invalid inputs

---

If you keep having this error, can you share a screenshot of your table/column type? Thanks

[LawrenceMa]

Hello Aurelien,

Thank you for your reply.

The error still occured. No matter I change this virtual column to Text / Number / List.

Thank you.

[Aurelien]

You need to remove Data Table from your expression 

[LawrenceMa]

However I am doing it with another table. I need to compose this expression with specific table(namely Data Table)

[Aurelien · Accepted solution]

Hi @LawrenceMa

There are two steps, in two different tables.

1) In your table Data Table, you should have a virtual column "digitNumber" with this expression:

IF(STARTSWITH([5-digit Item Number], "2"),
ANY(EXTRACTNUMBERS([5-digit Item Number])),
0
)

 2) in your other table, where you wish to get the max value over all the data set of Data Table, you can use this expression:

MAX(Data Table[digitNumber])

[LawrenceMa]

Great! It works!

Thank you so much.

[Aurelien]

Great! 😀