Find MAX value when numbers have hyphen

Robert_G

New Member
Joined
Jul 13, 2018
Messages
48
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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.
 
Last edited:
Upvote 0
Try this array formula which needs to be confirmed with CTRL+SHIFT+ENTER.

=MAX(MID(A1:A10, FIND("-", A1:A10)+1,255)+0)
 
Upvote 0
With your sample data in A1:A10
This regular (NON-array) formula returns the max value of the digits after the dash:
Code:
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?
 
Upvote 0
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
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,223,368
Messages
6,171,682
Members
452,416
Latest member
johnog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top