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?

 

[Phuynh]

As state in my post "Convert Imperial to decimal inches", if want in decimal feet value just divide the result by 12 or
modifiy the line with:

...
si*(ft*12+VALUE(IF(ISERR(AND(FIND(" ",in),FIND("/",in))),IFERROR(VALUE("0 "&in),in),in))
)

to this:

...
(si*(ft*12+VALUE(IF(ISERR(AND(FIND(" ",in),FIND("/",in))),IFERROR(VALUE("0 "&in),in),in))))/12
)

Phh

 

[Phuynh]

An improve version of LAMBDA function for convert Imperial feet-inches (in various formats)
to decimal value, with optional argument conversion factor [cFact].

or define "Excel Name" to hold conversion factor such as:

in=1 (default inch if not specify)
ft=1/12
yd=1/36
mi=1/63360 (mile)
mm=25.4
cm=2.54
m=0.0254
...
...
...

=LAMBDA(imperial, [cFact],
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)))/IF(ISOMITTED(cFact),1,1/cFact))
)

Some test

Phh

 

[Phuynh]

It is possible that to convert from imperial feet-inches to decimal without using LAMBDA() and LET() functions with the older excel versions,
but the excel formula is ridiculous long and hard to read or trace error and it is impossible to have optional argument like convert to factor.

Experiment - Excel formula for convert imperial feet-inches to decimal inches
assuming cell A1 contains imperial feet-inches

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

For convert to other decimal unit, multiply the result with the convert factor by adding to the very end or new line of the formula, for example:
To decimal feet

*(1/12)

To decimal yard

*(1/36)

To decimal mile

*(1/63306)

To milimeter

*(25.4)

To centimeter

*(2.54)

To meter

*(0.0254)

Phh

 

[Rick Rothstein]

An improve version of LAMBDA function for convert Imperial feet-inches (in various formats)
to decimal value, with optional argument conversion factor [cFact].

or define "Excel Name" to hold conversion factor such as:

in=1 (default inch if not specify)
ft=1/12
yd=1/36
mi=1/63360 (mile)
mm=25.4
cm=2.54
m=0.0254
...
...
...

=LAMBDA(imperial, [cFact],
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)))/IF(ISOMITTED(cFact),1,1/cFact))
)

Some test
View attachment 112061

Phh

I have come up with an alternate LAMBDA that produces the same results as your LAMBDA but is about 3/4 its size....

=LAMBDA(imperial,[cFact],
LET(tx,TRIM(SUBSTITUTE(imperial,"-"," ")),
sgn,IF(LEFT(imperial)="-","-",""),
ft,12*TEXTBEFORE(tx,"'",,,,0),
in,IFERROR(0+IF(ISNUMBER(-tx),tx,(TEXTAFTER(SUBSTITUTE(" 0 "&tx,"""",""),{"'"," "},-2,,,0))),),
0+(sgn&(ft+in))/IF(ISOMITTED(cFact),1,1/cFact)))(A11:A22)

 

[Phuynh]

I have come up with an alternate LAMBDA that produces the same results as your LAMBDA but is about 3/4 its size....

=LAMBDA(imperial,[cFact],
LET(tx,TRIM(SUBSTITUTE(imperial,"-"," ")),
sgn,IF(LEFT(imperial)="-","-",""),
ft,12*TEXTBEFORE(tx,"'",,,,0),
in,IFERROR(0+IF(ISNUMBER(-tx),tx,(TEXTAFTER(SUBSTITUTE(" 0 "&tx,"""",""),{"'"," "},-2,,,0))),),
0+(sgn&(ft+in))/IF(ISOMITTED(cFact),1,1/cFact)))(A11:A22)

Thank you Rick, for your tips to shorten the formula

Phh