# Converting

#### Sydney288

##### New Member
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

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### mopp1

##### Board Regular
=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

##### New Member
=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

what was in A1?

#### Sydney288

##### New Member
what was in A1?

I had 1-09-04 in A1

#### mopp1

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

#### Rick Rothstein

##### MrExcel MVP
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'")

Last edited:

#### Sydney288

##### New Member
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

##### Board Regular
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

##### New Member
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 :/

Threads
1,102,265
Messages
5,485,758
Members
407,512
Latest member
PearceK

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
• Please i need your help to create formula
I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
• Got error while adding column and filter
Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...