Find MAX value when numbers have hyphen

[Robert_G]

I have a simple list from A1:A10. I need a formula in B1 that will return the MAX value instead of 0

18-1001
18-1002
18-1003
18-1004
18-1005
18-1006
18-1007
18-1008
18-1009
18-1010

 

[dreid1011]

Is it based on just the right 4 digits or by combining both numbers together? IE: 1001 or 181001?

Try this based on the just the 4 right digits:

=MAX(RIGHT(A1:A10,4)*1)

Or this if combining both numbers:

=MAX(SUBSTITUTE(A1:A10,"-","")*1)

This is an ARRAY formula ENTERED with CTRL+SHIFT+ENTER, not just ENTER.

Both are array formulas.

 

[Norie]

Try this array formula which needs to be confirmed with CTRL+SHIFT+ENTER.

=MAX(MID(A1:A10, FIND("-", A1:A10)+1,255)+0)

 

[Ron Coderre]

With your sample data in A1:A10
This regular (NON-array) formula returns the max value of the digits after the dash:

B1: =MAX(INDEX(--("0"&MID(A1:A15,4,10)),0))

Note that the formula references the blank cells under the data without returning errors.


Is that something you can work with?

 

[ldonkers]

You can always add a helper column B also and find the max of that. In Column B: =NUMBERVALUE(RIGHT(A1,4)) and then use the max function: =MAX(B1:B10)

LD

 

[Norie]

Inspired by Ron, here's a non-array version of the formula I posted.

=MAX(INDEX(MID(A1:A10, FIND("-", A1:A10)+1,255)+0,,1))