convert feet-inches-sixtennths to feet standard dimensions

Hello,

in the shop we use feet-inches-16ths as measurements.

For example.
13-8-3= 13' 8 3/16"
14-0-12 = 14' 3/4"

Can anybody think of a formula to convert this?

A bit cumbersome, but with your text string in A1 (prefix it with an apostrophe to stop Excel seeing it as a date):

=CONVERT((LEFT(A1,FIND("-",A1)-1)*12)+SUBSTITUTE(MID(A1,FIND("-",A1)+1,2),"-","")+(SUBSTITUTE(RIGHT(A1,2),"-","")/16)*100,"in","cm")

Or, just for 'decimal' inches:

=(LEFT(A1,FIND("-",A1)-1)*12)+SUBSTITUTE(MID(A1,FIND("-",A1)+1,2),"-","")+(SUBSTITUTE(RIGHT(A1,2),"-","")/16)*100

Or feet:

=((LEFT(A1,FIND("-",A1)-1)*12)+SUBSTITUTE(MID(A1,FIND("-",A1)+1,2),"-","")+(SUBSTITUTE(RIGHT(A1,2),"-","")/16)*100)/12

I haven't been able to put it into one formula but here is how I would approach it, I'd take the values (I'm assuming) in one cell each, use the text to columns function and with delimited selected, go next and click other then put - in the box and hit finish.
Then I'd try to recombine them with something like this...
=A1&"' "&IF(B1=0,"",B1)&" "&C1&"/"&16&"''"
The problem would be that it will return 12/16 of an inch rather than 3/4 of an inch.

this looks like it gets you there (mostly, again it doesn't treat the 12/16 as 3/4) but it is close...

=LEFT(A1,SEARCH("-",A1)-1)&"' "&IF(SUBSTITUTE(MID(A1,SEARCH("-",A1)*1,3),"-","")*1=0,"",SUBSTITUTE(MID(A1,SEARCH("-",A1)*1,3),"-",""))&" "&RIGHT(A1,LEN(A1)-SEARCH("-",A1,1)-2)&"/"&16&"''"

If you want 12/16 to display as 3/4, you can use this:

Formula:

Please Login or Register  to view this content.

It could probably be tidied up a bit, but it works.