# Converting Fractional inches and feet-inches to decimal inches

#### Nanaia

##### Board Regular
I've scoured the archives looking for a way to convert fractional inches or feet-inches into decimal inches and couldn't find a solution. I have column CO looking at column CN as a reference to convert the entry from inches or feet-inches (the data varies) to decimal inches and can't get it to work. I've tried CONVERT(CN3,"ft","in") which works fine if there is a foot value involved but not if the value is fractional inches. Do you have any recommendations?

#### BlakeSkate

##### Well-known Member
I've tried CONVERT(CN3,"ft","in") which works fine if there is a foot value involved but not if the value is fractional inches. Do you have any recommendations?
seems to work for me?
if i have your formula in CO3

 CN CO 1 5/7 20 4/7

<tbody>
</tbody>

and then you right click > format as number with .00 decimal

 CN CO 1 5/7 20.57

<tbody>
</tbody>

you can format the other way around as well

Last edited:

#### Nanaia

##### Board Regular
I seem to have worded things poorly. (Gotta love Excel...) Column CO is looking at column CN and converting the data contained in CN. CN is pulling the data from another sheet that includes the ' for foot and the " for inches and changing the formatting of the column has no affect.

#### BlakeSkate

##### Well-known Member
I seem to have worded things poorly. (Gotta love Excel...) Column CO is looking at column CN and converting the data contained in CN. CN is pulling the data from another sheet that includes the ' for foot and the " for inches and changing the formatting of the column has no affect.
AH i understand now
want to give me the 2 sheet names, the formula in cn, and any example numbers/numbers?
I prefer to work with what you are working with if possible

#### shg

##### MrExcel MVP
 A​ B​ C​ 1​ ft & in​ dec in​ 2​ 10' 2 3/4" 122.75​ B2: =IFERROR(LEFT(A2, FIND("'", A2) - 1), 0) * 12 + SUBSTITUTE(IFERROR(MID(A2, FIND("'", A2) + 1, 8), A2), """", "") 3​ 1' 0 63/64" 12.984375​ 4​ 1 1/2" 1.5​

#### Nanaia

##### Board Regular
The only column I'm concerned with is CO. CN can contain a wide variety of information (it changes with each line and job). I only want to have CO look at CN and give me a decimal number. The trouble I'm running into is that CO can be 10", 10 1/2", 1'-9", 7'-4", etc. because it varies. I want CO to look at a number like 7'-4" and convert it to 88.000 and look at 10 1/2" and convert it to 10.000.

#### Rick Rothstein

##### MrExcel MVP
Here is a UDF (user defined function) which, assuming fractions are offset with a space or dash only, should be able to handle almost thing you throw at it (see below for some samples)...
Code:
``````[table="width: 500"]
[tr]
[td]Function Inches(ByVal FeetInch As String) As Variant
Dim FootMark As Long, Foot As String, Inch As String, Temp As Variant
FeetInch = Application.Trim(Replace(Replace(Replace(Application.Trim(FeetInch), "-", " "), "/ ", "/"), " /", "/"))
FootMark = InStr(FeetInch, "'")
If FootMark Then
Foot = Split(FeetInch, "'")(0)
Inch = Replace(Split(FeetInch, "'")(1), """", "")
Else
Inch = Trim(Replace(FeetInch, """", ""))
End If
If InStr(Foot, " ") Then
Temp = Split(Application.Trim(Foot))
Foot = (Temp(0) + Evaluate(Temp(1)))
End If
If InStr(Inch, " ") Then
Temp = Split(Trim(Inch) & " ")
Inch = (Temp(0) + Evaluate(Temp(1)))
End If
Inches = 12 * Val(Foot) + Val(Inch)
End Function[/td]
[/tr]
[/table]``````
HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Inches just like it was a built-in Excel function. For example,

=Inches(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Sample Values That Function Can Handle
------------------------------------------------------------
Code:
``````10' 2-3/4"
1' 0 63/64"
1   -    1    /     2    "
1 3/4'
3'
3 "
1 - 2 / 3 '    3 - 1 / 2 "``````

Last edited:

1,082,269
Messages
5,364,148
Members
400,783
Latest member
sambills

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...