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

S

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
 
B

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
 
S

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
 
J

jmer

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

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),"")) &""""
 
J

jmer

That's it! Thanks so much!

Dave Peterson said:
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),"")) &""""
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top