Converting Fractional inches and feet-inches to decimal inches

Nanaia

Board Regular
Joined
Jan 11, 2018
Messages
198
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
Joined
Jan 26, 2015
Messages
613
Office Version
2016
Platform
Windows
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

CNCO
1 5/720 4/7

<tbody>
</tbody>


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

CNCO
1 5/720.57

<tbody>
</tbody>

you can format the other way around as well
 
Last edited:

Nanaia

Board Regular
Joined
Jan 11, 2018
Messages
198
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
Joined
Jan 26, 2015
Messages
613
Office Version
2016
Platform
Windows
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
Joined
May 7, 2008
Messages
21,717
Office Version
2010
Platform
Windows
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
Joined
Jan 11, 2018
Messages
198
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
Joined
Apr 18, 2011
Messages
35,508
Office Version
2010
Platform
Windows
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:

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top