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

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

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

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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"sammy" wrote in message
...
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

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

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

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

Good to know.
Thanks for the feedback.
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
"sammy" wrote in message
...
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

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

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

"Bernard Liengme" wrote:

Good to know.
Thanks for the feedback.
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
"sammy" wrote in message
...
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

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

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 wrote:

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

"Bernard Liengme" wrote:

Good to know.
Thanks for the feedback.
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
"sammy" wrote in message
...
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



--

Dave Peterson

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

That's it! Thanks so much!

"Dave Peterson" wrote:

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 wrote:

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

"Bernard Liengme" wrote:

Good to know.
Thanks for the feedback.
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
"sammy" wrote in message
...
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



--

Dave Peterson