Convert Ft'-In Fr" (5'-6 3/4") to Ft.dec (5.56)

[sammy]

Hi All,
Is there anyway to convert an architectural units text with feet, inches and
fractions of an inch, like (5'-6 3/4") to feet and decimals of a foot, like
(5.56)?
TIA,
Sal

 

[Bernard Liengme]

With this text: 5'-6 3/4")
in A1, the formula =--LEFT(A1,FIND("'",A1)-1) returns the numeric value 5
The double negation converts text to number

The formula
=--LEFT(MID(A1,FIND("'",A1)+2,256),LEN(MID(A1,FIND("'",A1)+2,256))-1)
returns 6.75 also as a number

Combining them as
=ROUND(LEFT(A1,FIND("'",A1)-1)+LEFT(MID(A1,FIND("'",A1)+2,256),LEN(MID(A1,FIND("'",A1)+2,256))-1)/12,2)
gives 5.56

I tested the formula with 12'-5 1/8" and got the correct value of 12.43
But please do more testing
best wishes

 

[sammy]

Thank you Bernard,

This is exactly what i wanted and i have done some testing with know values
and it works as expected.

Thanks again.
Sammy

 

[Bernard Liengme]

Good to know.
Thanks for the feedback.

 

[jmer]

Now is there a way to reverse that? If I have 5.56 can I convert it to read
5'-6 3/4"?

 

[Dave Peterson]

Take a look at David McRitchie's site:
http://mvps.org/dmcritchie/excel/formula.htm#carpentry

Especially the formula after this portion:
"This formula from a posting by Bernie Dietrick 2000-08-04 will round feet ..."

=IF(A1>=1,INT(A1)&"' ","") & TEXT(MOD(A1,1)*12,"- 0"&IF(ABS(MOD(A1,1)
*12-ROUND(MOD(A1,1)*12,0))>1/32," 0/"&CHOOSE(ROUND(MOD(MOD(A1,1)*12,1)*16,0),
16,8,16,4,16,8,16,2,16,8,16,4,16,8,16),"")) &""""

 

[jmer]

That's it! Thanks so much!

Take a look at David McRitchie's site:
http://mvps.org/dmcritchie/excel/formula.htm#carpentry

Especially the formula after this portion:
"This formula from a posting by Bernie Dietrick 2000-08-04 will round feet ..."

=IF(A1>=1,INT(A1)&"' ","") & TEXT(MOD(A1,1)*12,"- 0"&IF(ABS(MOD(A1,1)
*12-ROUND(MOD(A1,1)*12,0))>1/32," 0/"&CHOOSE(ROUND(MOD(MOD(A1,1)*12,1)*16,0),
16,8,16,4,16,8,16,2,16,8,16,4,16,8,16),"")) &""""