Master Convert from Fractional Feet and Inches to Decimal

Falko26

Board Regular
Joined
Oct 13, 2021
Messages
99
Office Version
  1. 365
Platform
  1. Windows
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,

1721672513311.png
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
I've seen some pretty amazing formulas in here but they're not my thing.
Probably doable with code though.
 
Upvote 0
Think this does the trick.

EXCEL
BCDEF
2Length FractionalFeetInchesFracTotal Inches
310 3/8"0100.37510.375
46 7/8"060.8756.875
52"0202
61'-11 13/16"1110.812523.8125
Sheet2
Cell Formulas
RangeFormula
C3:C6C3=IF(ISNUMBER(FIND("'",B3)),INT(LEFT(B3,FIND("'",B3)-1)),0)
D3:D6D3=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:E6E3=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:F6F3=C3*12+D3+E3
 
Upvote 0
Another option:

Prueba1.xlsx
ABC
1
2Length fractional <feet>'-<inches> <fractional inches>"Length decimal
33/8"0,375
45"5
52'24
61'-10"22
71'-13/16"12,8125
811 15/16"11,9375
910'-11 5/8"131,625
100'-0"0
110
Hoja1
Cell Formulas
RangeFormula
C3:C11C3=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 )
 
Upvote 0
Solution
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!

1721756227318.png
 
Upvote 0
Also, Felixstraube. Just saw your post.

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

Forum statistics

Threads
1,224,885
Messages
6,181,587
Members
453,055
Latest member
cope7895

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top