Find max value of hyphenated numbers in a row

[mlbillow]

I am a track coach and we record the distances thrown for shotput and discus and currently enter it as "feet-inches" so a shotput throw of 45 feet 6 inches is recorded at 45-06 and a throw of 46 feet 2 1/4 inch is recorded 46-0225. We track every throw from every meet in a spreadsheet throughout the season. We need to find the highest value and return that value in the same format (45-06). Below is a sample of what our data looks like:

Name	Season Best	Meet #1	Meet #2	Meet #3	Meet #4
Parker		45-06	44-11	46-0225	45-07
Dean		44-04	44-0975	43-01	47-00

Is there an easy way to find the max value and return it in the same format? Or is there an easier way to enter the distance in feet & inches that would make it easier to use? I don't want to have to convert their distance to inches as we need it in Feet & Inches.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[etaf]

what version of excel do you have -
had a play with substitute - but i lose the 00 - need to look at that - but i think as you report inches could just substture the inch number as a decimal
=SUBSTITUTE(MAX(SUBSTITUTE(C2:F2,"-",".")*1),".","-")

 

[mlbillow]

Currently using Excel for Microsoft 365.

 

[Fluff]

Thanks for that.
How about

Fluff.xlsm
	A	B	C	D	E	F
1	Name	Season Best	Meet #1	Meet #2	Meet #3	Meet #4
2	Parker	46-0225	45-06	44-11	46-0225	45-07
3	Dean	47-00	44-04	44-0975	43-01	47-00
Sheet5

Cell Formulas
Range	Formula
B2:B3	B2	=LET(s,--SUBSTITUTE(C2:F2,"-","."),INDEX(C2:F2,XMATCH(MAX(s),s)))

 

[etaf]

edit - posted as fluff posted hes
=LET(s,--SUBSTITUTE(C2:F2,"-","."),INDEX(C2:F2,XMATCH(MAX(s),s)))

i'm sure fluff will be back with a better solution
=SUBSTITUTE(TEXT(MAX(SUBSTITUTE(C2:F2,"-",".")*1),"0.0000"),".","-")

Book1
	A	B	C	D	E	F	G	H
1	Name	Season Best	Meet #1	Meet #2	Meet #3	Meet #4
2	Parker		45-06	44-11	46-0225	45-07		46-0225
3	Dean		44-04	44-0975	43-01	47-00		47-0000
Sheet1

Cell Formulas
Range	Formula
H2:H3	H2	=SUBSTITUTE(TEXT(MAX(SUBSTITUTE(C2:F2,"-",".")*1),"0.0000"),".","-")

 

[mlbillow]

This is great! Thanks guys!

 

[Fluff]

Glad we could help & thanks for the feedback.

Please don't forget to update your profile to show what version of Excel you're using, as it saves people from having to ask.

 

[mlbillow]

What if we changed the values for 46 feet 2 1/4 inch from 46-0225 to 46-02.25? One of the other coaches would prefer the inches in decimel format instead.

 

[Fluff]

How about

=LET(s,--SUBSTITUTE(SUBSTITUTE(C2:F2,".",""),"-","."),INDEX(C2:F2,XMATCH(MAX(s),s)))