Converting

[Sydney288]

Hi everyone!

I'm new to this forum so i'm hoping you guys can help me out. I'm trying to make a form that will be able to convert a measurement that is in carpenters form to a normal FT'In' Fraction Format.

Ex. 9'6 1/2" is 09-06-08

the front 09-XX-XX stands for Ft, XX-06-XX stands for In, and XX-XX-08 stands for the 16ths of an Inch. so 8/16" is 1/2" Etc.

I'm trying to make a formula that will do this when i load it into Excel.

Please help me

 

[mopp1]

=left(a1,2)*1&"'"&mid(a1,4,2)*1&if(right(a1,2)*1," ","")&choose(right(a1,2)*1+1,"","1/16","1/8","3/16","1/4","5/16","3/8","7/16","1/2","9/16","5/8","11/16","3/4","13/16","7/8","15/16")&""""

 

[Sydney288]

=left(a1,2)*1&"'"&mid(a1,4,2)*1&if(right(a1,2)*1," ","")&choose(right(a1,2)*1+1,"","1/16","1/8","3/16","1/4","5/16","3/8","7/16","1/2","9/16","5/8","11/16","3/4","13/16","7/8","15/16")&""""

I tried this and I only got a #Value ! Issue

 

[mopp1]

what was in A1?

 

[Sydney288]

what was in A1?

I had 1-09-04 in A1

 

[mopp1]

Make sure you format the carpenter's form cells as text so excel doesn't convert them to dates

 

[Rick Rothstein]

Assuming the carpenter's form cells (Column A) are formatted as TEXT which the values you show them to have, put this in a cell formatted as General (note that for some reason, every time I did something in the formula cell, Excel changed the cell format to Text which made me have to deliberately format it as General and re-enter the formula)...

=LEFT(A1,2)+0&TEXT(MID(A1,4,2)+RIGHT(A1,2)/16,"' 0 0/#0'")

 

[Sydney288]

I'm still having issues with this. I have the whole thing, 1-09-04, in cell A1. I then place the formula in B1. I still get the Value Issue. Hopefully i'm not messing this up somewhere :/ i'm not great with excel.

 

[mopp1]

I had 1-09-04 in A1

for the formula to work as written each quantity (feet, inches, sixteenths) has to be exactly two digits.

 

[Sydney288]

for the formula to work as written each quantity (feet, inches, sixteenths) has to be exactly two digits.

I got it to work! thank you! Now if i were to want to multiply that by just the number 3, how would i do that with a formula? I tried taking what the formula spat out and multiplying it by 3 but that didn't work :/