Wrong answer with "feet" function

[BogeyGolfer]

Good evening everyone,

I'm having an issue with the code results using the function "feet" that I found on this board. I can usually come up with standard formulas and I'm great with "cut and paste"! But for the life of me, I can't make heads or tails out of these codes. I have a Windows 10 system with Excel 2013.

Here are the results I'm getting and it appears that the issue may be with the foot portion of the code as A6, which is only inches, came out correctly:

~

FEET & INCHES CALCULATOR
DIM	DEC FT	DEC TL	DIM TOTAL
112' 02 3/4''	933.5625
25' 07 1/2''	208.9583333	1142.520833	1142' 6 1/4"
' 2 1/2''	0.208333333	1142.729167	1142' 8 3/4"
11' 05 1/8''	92.09375	1234.822917	1234' 9 7/8"
	0	0	0' 0"

<tbody>
</tbody>

Here is the code that I had copied:

~Public Function feet(LenString As String)
Dim FootSign As Integer
Dim InchSign As Integer
Dim SpaceSign As Integer
Dim FracSign As Integer
Dim InchString As String
Dim Word2 As String
' Copyright 1999, 2005 MrExcel.com
LenString = Application.WorksheetFunction.Trim(LenString)
'The find function returns an error when the target is not found
'Resume Next will prevent VBA from halting execution.
On Error Resume Next
FootSign = Application.WorksheetFunction.Find("'", LenString)
If IsEmpty(FootSign) Or FootSign = 0 Then
' There are no feet in this expression
feet = 0
FootSign = 0
Else
feet = Val(Left(LenString, FootSign - 1))
End If

' Handle the case where the foot sign is the last character
If Len(LenString) = FootSign Then Exit Function
' Isolate the inch portion of the string
InchString = Application.WorksheetFunction.Trim(Mid(LenString, FootSign + 1))
' Strip off the inch sign, if there is one
InchSign = Application.WorksheetFunction.Find("""", InchString)
If Not IsEmpty(InchSign) Or InchSign = 0 Then
InchString = Application.WorksheetFunction.Trim(Left(InchString, InchSign - 1))
End If

' Do we have two words left, or one?
SpaceSign = Application.WorksheetFunction.Find(" ", InchString)
If IsEmpty(SpaceSign) Or SpaceSign = 0 Then
' There is only one word here. Is it inches or a fraction?
FracSign = Application.WorksheetFunction.Find("/", InchString)
If IsEmpty(FracSign) Or FracSign = 0 Then
'This word is inches
feet = feet + Val(InchString) / 12
Else
' This word is fractional inches
feet = feet + (Val(Left(InchString, FracSign - 1)) / Val(Mid(InchString, FracSign + 1))) / 12
End If
Else
' There are two words here. First word is inches
feet = feet + Val(Left(InchString, SpaceSign - 1)) / 12
' Second word is fractional inches
Word2 = Mid(InchString, SpaceSign + 1)
FracSign = Application.WorksheetFunction.Find("/", Word2)
If IsEmpty(FracSign) Or FracSign = 0 Then
' Return an error
feet = "VALUE!"
Else
If FracSign = 0 Then
feet = "VALUE!"
Else
feet = feet + (Val(Left(Word2, FracSign - 1)) / Val(Mid(Word2, FracSign + 1))) / 12
End If
End If
End If
End Function

I'm trying to set up a calculator to use with speech recognition software. I review Architectural drawings that have long dimension strings and it's a tedious process using the construction calculators that are available.

Any help would be appreciated.

Thanks

 

[mikerickson]

I'm guessing that DIM is the given dimention in Feet and inches (fractional representation)
Again, I'm guessing that DEC FT is supposed to be that value converted to decimal feet.
DEC TL is the running total DEC FT.
And the final column is a conversion from DEC TL to ft in (fractional) representation.

And that you are trying to use a UDF for the DIM to DEC FT conversion.

1) The data you posted has two apostrophes '' rather than a quote mark " to indicate inches.

2) You might like these UDF's

Function FeetInches2Feet(FeetInchesString As String) As Double
    Const ftMark As String = "'"
    Const inMark As String = """"
    Dim strFeetPart As String, strInchesPart As String
    
    FeetInchesString = Replace(FeetInchesString, ftMark & ftMark, inMark)
    
    If InStr(FeetInchesString, ftMark) + InStr(FeetInchesString, inMark) = 0 Then
        FeetInchesString = FeetInchesString & ftMark & " 0" & inMark
    End If
    If InStr(FeetInchesString, ftMark) = 0 Then FeetInchesString = "0" & ftMark & FeetInchesString
    If InStr(FeetInchesString, inMark) = 0 Then FeetInchesString = FeetInchesString = FeetInchesString & "0" & inMark
    
    strFeetPart = Split(FeetInchesString, ftMark)(0)
    strInchesPart = Replace(FeetInchesString, strFeetPart & ftMark, vbNullString)
    strInchesPart = Trim(Replace(strInchesPart, inMark, "", 1))
    
    FeetInches2Feet = Evaluate(strFeetPart) + (Evaluate(strInchesPart) / 12)
End Function

Function Feet2FeetInches(ByVal DecimalFeet As Double) As String
    Const ftMark As String = "'"
    Const inMark As String = """"
    
    Feet2FeetInches = CStr(Int(DecimalFeet))
    DecimalFeet = DecimalFeet - Val(Feet2FeetInches)
    Feet2FeetInches = Feet2FeetInches & ftMark & " " & WorksheetFunction.Text(DecimalFeet * 12, "0 ?/?") & inMark
End Function

 

[Rick Rothstein]

Function FeetInches2Feet(FeetInchesString As String) As Double
    Const ftMark As String = "'"
    Const inMark As String = """"
    Dim strFeetPart As String, strInchesPart As String
    
    FeetInchesString = Replace(FeetInchesString, ftMark & ftMark, inMark)
    
    If InStr(FeetInchesString, ftMark) + InStr(FeetInchesString, inMark) = 0 Then
        FeetInchesString = FeetInchesString & ftMark & " 0" & inMark
    End If
    If InStr(FeetInchesString, ftMark) = 0 Then FeetInchesString = "0" & ftMark & FeetInchesString
    If InStr(FeetInchesString, inMark) = 0 Then FeetInchesString = FeetInchesString = FeetInchesString & "0" & inMark
    
    strFeetPart = Split(FeetInchesString, ftMark)(0)
    strInchesPart = Replace(FeetInchesString, strFeetPart & ftMark, vbNullString)
    strInchesPart = Trim(Replace(strInchesPart, inMark, "", 1))
    
    FeetInches2Feet = Evaluate(strFeetPart) + (Evaluate(strInchesPart) / 12)
End Function

Not fully tested, but I believe the following UDF will do the same thing as your code quoted above...

Function FeetInches2Feet(FeetInchesString As String) As Double
  Dim FeetInch() As String
  FeetInch = Split(Replace(FeetInchesString, """", ""), "'")
  If FeetInchesString Like "*'*" Then
    FeetInches2Feet = Val(FeetInch(0)) + Evaluate(FeetInch(1)) / 12
  Else
    FeetInches2Feet = Val(Evaluate(FeetInch(0))) / 12
  End If
End Function

EDIT NOTE
------------------------
My code makes the assumption that if a number is entered with no apostrophe in it, that number is inches... your code appears to assume it is feet.

 

[Rick Rothstein]

EDIT NOTE
------------------------
My code makes the assumption that if a number is entered with no apostrophe in it, that number is inches... your code appears to assume it is feet.

Just to clarify my Edit Note a little bit... in order for my code to see a number as feet, that number must be followed by an apostrophe, otherwise it is assumed to be inches (whether there is an inch mark after it or not).

 

[BogeyGolfer]

Thanks Mike, you were right on your assumptions, sorry about not explaining myself better. Unfortunately, neither of these worked. Both returned the original text before conversion to the foot/inches text shown in the first column. Right now, I enter 11202.75 into the first cell and it is formatted to display 112' 02 3/4".

Here's a link to an explanation on how the first columns text is entered and formatted:
http://www.mrexcel.com/articles/excel-feet-to-inches.php

And here's a link for the original function used for the second column:
<strike></strike>http://www.mrexcel.com/forum/excel-questions/99542-feet-inches-fractions-cell-custom-format.html

Any other advice you could give me would be great. This is so close to working.

 

[BogeyGolfer]

Thanks Rick, your right about doing the same thing as my code above - it gave me the same incorrect answer! LOL! As an example the results for 112' 02 3/4" in the first row should be 112.229167 and I'm getting 933.5625 (933' 06 3/4"). Any other suggestions? In my answer to Mike above, I listed the links I had found originally to set this up.

Thanks again for your help.

 

[Rick Rothstein]

Thanks Rick, your right about doing the same thing as my code above - it gave me the same incorrect answer! LOL! As an example the results for 112' 02 3/4" in the first row should be 112.229167 and I'm getting 933.5625 (933' 06 3/4"). Any other suggestions? In my answer to Mike above, I listed the links I had found originally to set this up.

Did you change the double apostrophe ('') that you are using for the inch mark to a quote mark ("") which is the symbol that everyone uses for the inch mark? If you do that, the code should work correctly. If you want to continue using the incorrect double apostrophe symbol, then try this code instead...

Function FeetInches2Feet(FeetInchesString As String) As Double
  Dim FeetInch() As String
  FeetInch = Split(Replace(FeetInchesString, "''", ""), "'")
  If FeetInchesString Like "*'*" Then
    FeetInches2Feet = Val(FeetInch(0)) + Evaluate(FeetInch(1)) / 12
  Else
    FeetInches2Feet = Val(Evaluate(FeetInch(0))) / 12
  End If
End Function

 

[BogeyGolfer]

Yeah, I did change it to a quote mark. Now I'm thoroughly confused. On the first column I used a Custom Format of ##' ## ?/?". This allowed me to enter the numbers 1105.50 and get the displayed result of 11' 05 1/2". With both your function and the original one I had, I get the decimal result of 92.125 in the second column. What's weird is that if I physically enter the same dimensions in the first cell instead of using the custom format, I get the correct decimal result of 11.45833333.

Could this have something to do with the custom formatting in the first cells?

 

[Rick Rothstein]

Yeah, I did change it to a quote mark. Now I'm thoroughly confused. On the first column I used a Custom Format of ##' ## ?/?". This allowed me to enter the numbers 1105.50 and get the displayed result of 11' 05 1/2". With both your function and the original one I had, I get the decimal result of 92.125 in the second column. What's weird is that if I physically enter the same dimensions in the first cell instead of using the custom format, I get the correct decimal result of 11.45833333.

Could this have something to do with the custom formatting in the first cells?

Yes... you told us how you told Excel to show that value to you, not what the actual value in the cell was. A formatted value is just a display, it is not an actual value... the actual value is what you see in the Formula Bar. See if this formula gives you the results you want...

=INT(A1/100)+MOD(A1,100)/12

 

[BogeyGolfer]

I looked into the available functions in Excel and was able to use the Text function. Had to add one more column but the end result was what I was looking for. Thanks.