Converting Fractional inches and feet-inches to decimal inches

[Nanaia]

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?

 

[DataBlake]

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]CN[/TD]
[TD]CO[/TD]
[/TR]
[TR]
[TD]1 5/7[/TD]
[TD]20 4/7[/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]CN[/TD]
[TD]CO[/TD]
[/TR]
[TR]
[TD]1 5/7[/TD]
[TD]20.57[/TD]
[/TR]
</tbody>[/TABLE]

you can format the other way around as well

 

[Nanaia]

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.

 

[DataBlake]

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]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

ft & in​

[/td][td="bgcolor:#F3F3F3"]

dec in​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]10' 2 3/4"[/td][td="bgcolor:#CCFFCC"]

122.75​

[/td][td="bgcolor:#CCFFCC"]B2: =IFERROR(LEFT(A2, FIND("'", A2) - 1), 0) * 12 + SUBSTITUTE(IFERROR(MID(A2, FIND("'", A2) + 1, 8), A2), """", "")[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]1' 0 63/64"[/td][td="bgcolor:#CCFFCC"]

12.984375​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]1 1/2"[/td][td="bgcolor:#CCFFCC"]

1.5​

[/td][td][/td][/tr]
[/table]

 

[Nanaia]

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]

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)...

[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
------------------------------------------------------------

10' 2-3/4"
1' 0 63/64"
    1   -    1    /     2    "
1 3/4'
3'
3 "
1 - 2 / 3 '    3 - 1 / 2 "