Master Convert from Fractional Feet and Inches to Decimal

[Falko26]

Hey Team,

I know this topic has been covered a lot but I have yet to find a signal formula to handle all of the possible situations. I am trying to convert fraction feet and inches to decimal feet (Or inches whatever is easier). The possible situations include Inches with fractions B3, whole Inches B5, Feet and Fractional Inches B6, Feet and Whole Inches B8. (also with the "-" between feet and inches.

Has anyone ever came up with a solution to this problem.

Thanks for your Time,

 

[Micron]

I've seen some pretty amazing formulas in here but they're not my thing.
Probably doable with code though.

 

[lrobbo314]

Think this does the trick.

EXCEL
	B	C	D	E	F
2	Length Fractional	Feet	Inches	Frac	Total Inches
3	10 3/8"	0	10	0.375	10.375
4	6 7/8"	0	6	0.875	6.875
5	2"	0	2	0	2
6	1'-11 13/16"	1	11	0.8125	23.8125
Sheet2

Cell Formulas
Range	Formula
C3:C6	C3	=IF(ISNUMBER(FIND("'",B3)),INT(LEFT(B3,FIND("'",B3)-1)),0)
D3:D6	D3	=INT(IF(ISNUMBER(FIND("-",B3)),MID(B3,FIND("-",B3)+1,FIND(" ",B3)-FIND("-",B3)),IF(ISNUMBER(FIND(" ",B3)),LEFT(B3,FIND(" ",B3)-1),LEFT(B3,FIND("""",B3)-1))))
E3:E6	E3	=LET(f,IF(ISNUMBER(FIND(" ",B3)),MID(B3,FIND(" ",B3)+1,FIND("""",B3)-FIND(" ",B3)-1),0),s,TEXTSPLIT(f,,"/"),IF(ROWS(s)>1,INDEX(s,1)/INDEX(s,2),s))+0
F3:F6	F3	=C3*12+D3+E3

 

[felixstraube]

Another option:

Prueba1.xlsx
	A	B	C
1
2		Length fractional <feet>'-<inches> <fractional inches>"	Length decimal
3		3/8"	0,375
4		5"	5
5		2'	24
6		1'-10"	22
7		1'-13/16"	12,8125
8		11 15/16"	11,9375
9		10'-11 5/8"	131,625
10		0'-0"	0
11			0
Hoja1

Cell Formulas
Range	Formula
C3:C11	C3	=LET(d,B3, df,IF(ISNUMBER(FIND("'",d)),d,"0'-"&d), dfi,SUBSTITUTE(IF(ISNUMBER(FIND("""",df)),df,df&"-0"""),"--","-"), feet, TEXTBEFORE(dfi,"'"), i, TEXTBEFORE(TEXTAFTER(dfi,"-"),""""), ic, IF(ISNUMBER(FIND(" ", TRIM(i))), i, IF(ISNUMBER(FIND("/",i)),"0 "&i, i&" 0/1")), inches, TEXTBEFORE(ic, " "), fic, TEXTAFTER(ic," "), fracIn, TEXTBEFORE(fic,"/")/TEXTAFTER(fic,"/"), feet*12 + inches + fracIn )

 

[Falko26]

Thanks you Irobbo!

This almost nails it, way closer than before. Only found two instances where it seems to be off. If it is simply just feet and inches I get an error. and if its just a fraction it reports a value that is way to big. Is there a way to adjust this?

Again thanks so much for your help!

 

[Falko26]

Also, Felixstraube. Just saw your post.

This Seems to work perfectly for what I am trying to accomplish! Thanks so much for your feedback !

 

[felixstraube]

Thanks for the feedback. Glad we could help!