Convert Feet & Inches to Decimal Value

[nickmarrcleveland]

How do I convert a feet and inches value to a decimal value?
Ex: 5'-2 7/16" converted to 5.203125
My data does not have the same number of characters in each string.
Is there a formula or formulas that can be used for a data set with varying numbers of characters?

 

[Micron]

Not quite clear. 5.203125 would be just over 5.2 in feet but that is 62.4375 in inches. What units do you want the decimal to represent? I'm asking to help someone else ID what you want out of a formula. I can usually do vba code but am mostly lost when it comes to formulas.

 

[nickmarrcleveland]

Yes, 5.2 feet.
Sorry for not including that!

 

[Cubist]

Perhaps this.

Book1
	A	B
1	5'-2 7/16"	5.203125
2	5'-11 7/8"	5.989583
3	1'-2 11/16"	1.223958
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=LET(ts,IFERROR(--TEXTSPLIT(A1,,{"'","-"," ","/",""""}),0),inc,INDEX(ts,3)+INDEX(ts,4)/INDEX(ts,5),INDEX(ts,1)+inc/12)

 

[nickmarrcleveland]

This works for the given values but not when a value is 0" and a fraction.

Ex 1' - 0 5/8"

 

[Cubist]

This works for the given values but not when a value is 0" and a fraction.

Ex 1' - 0 5/8"

Are there spaces before and after the hyphen? The above formula works when there are no spaces before and after the hyphen as provided example in the first post. If they do have spaces then this should work.

Book2
	A	B
1	5' - 2 7/16"	5.203125
2	5' - 11 7/8"	5.989583333
3	1' - 2 11/16"	1.223958333
4	1' - 0 5/8"	1.052083333
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=LET(ts,IFERROR(--TEXTSPLIT(A1,,{"'"," - "," ","/",""""}),0),inc,INDEX(ts,3)+INDEX(ts,4)/INDEX(ts,5),INDEX(ts,1)+inc/12)

 

[nickmarrcleveland]

Yes, there is a space before and after the hypen.

 

[Phuynh]

Unlike convert from decimal to imperial feet-inch, which has only one format decimal to deal with. However when convert from imperial to decimal, think of how many imperial formats such as:

Ex:

10'-6"
10' - 6 1/2"
120"
120 3/8"
15/16"
15'
15.8333'
-1/4"
-21/64"
...
...
Etc...

To deal with, these really cumbersome! And depict all of above the hardest part is to prevent Excel from automatic converts fraction to date serial! Therefor I abandon it and work with excel VBA (dated back few year ago since excel 2010)
In recent Excel version, it is possible with these LAMBDA() & LET() functions

Convert Imperial to decimal inches

=LET(si,IF(LEFT(A1,1)="-",-1,1),
     ft,IFERROR(ABS(VALUE(LEFT(A1,(FIND("'",A1)-1)))),0),
     in,TRIM(SUBSTITUTE(SUBSTITUTE(IFERROR(RIGHT(A1,LEN(A1)-FIND("'",A1)),A1),"-",""),"""","")),
     si*(ft*12+VALUE(IF(ISERR(AND(FIND(" ",in),FIND("/",in))),IFERROR(VALUE("0 "&in),in),in)))
)

Or use with Lambda function

=LAMBDA(imperial,
LET(si,IF(LEFT(imperial,1)="-",-1,1),
    ft,IFERROR(ABS(VALUE(LEFT(imperial,(FIND("'",imperial)-1)))),0),
    in,TRIM(SUBSTITUTE(SUBSTITUTE(IFERROR(RIGHT(imperial,LEN(imperial)-FIND("'",imperial)),imperial),"-",""),"""","")),
    si*(ft*12+VALUE(IF(ISERR(AND(FIND(" ",in),FIND("/",in))),IFERROR(VALUE("0 "&in),in),in))))
)

Some test:

Let me know if there any error or I need to improve!

Give me thumb-up if you like!,

Phh

 

[Micron]

According to post 3, the result should be in fractional feet? So 5' 2 - 7/16" should be converted to 5.203125 feet, not 62.4375 inches. That's how I interpret post 3 and that was the clarification I sought in post 2. If OP wants to consider a VBA solution I might be able to provide one, but given that there has been no activity on this for a while, I suspect it has been answered elsewhere.

 

[Rick Rothstein]

Give this formula a try...

=LET(t,TEXTSPLIT(A1,{"'",""""}),TAKE(t,,1)-CHOOSECOLS(t,2)/12)