Dealing with feet and inches in Excel

The problem

As a structural engineer working in Canada, I have to endure the absurdity that is the imperial system of measurement. Having previously worked in New Zealand, I was initially sheltered from this gravely flawed mess. But here in Canada a lot of the components we work with are manufactured by our neighbor to the south, who is still flogging the dead horse that is the English system of measurement (hell, even the English themselves eventually gave it up in favor of the metric system, despite the fact that it was the creation of their historic nemesis – the French).

Why is this system absurd? Because it uses a plethora of units to represent the same type of measurement. Length, for example, is routinely expressed in Miles, Feet and Inches. This is further complicated by the use of inch fractions instead of using a decimal system that’s native to today’s software. This makes it a bloody nightmare to work with in Excel (or in any other program or capacity for that matter!) So we engineers are left to devise tools to try to manage this. And here’s mine…

The solution

There’s plenty of work-arounds that you can use in Excel, but none of them can simultaneously display the value correctly and conventionally, while still being recognized by Excel as a value. The solution is therefore to use a User Defined Function to convert a string of text (in the format 13′ 5 3/16″)  to a value (13.43229 feet in this case), and then back again as needed.

I achieve this with two User Defined Functions that convert to and from Feet and Inches expressed in the format 13′ 5 3/16″ using the functions StringToFt(String) and FtToString(Value) respectively.

The use of this is demonstrated here.

Download

You can download this Excel module containing the two functions here.

Installation

Refer to my earlier blog post where I demonstrate how I install this here.

Alternatively you can manually copy the code in yourself from below.

A deeper dive

Before writing these functions I did a lot of searching to see if anyone had already done this. I found one example by Bill Jelen from MrExcel.com here, which is actually what inspired me to write mine.

Also, this evening as I was writing this up I found a second very similar macro by John F. Lacher here.

Both of these are very good, but I think have room for improvement, which is why I wrote mine.

Most critically, both of these functions are inconsistent in the way that they round the inch fractions. Lacher’s uses the default Bankers Rounding that is native to VBA whereby numbers are rounded to the nearest even number (e.g. both 3.5 and 4.5 will round to 4.0). This is inconsistent with the conventional Arithmetic Rounding that is in conventionally used in measurement, whereby half-increments of a given level of precision are rounded up (e.g. 3.5 rounds to 4.0 and 4.5 rounds to 5.0).

Jelen’s behaves more erratically because of Floating Point Error that occurs when dealing with numbers stored in Binary in excel VBA. For example, with a default precision of ¹/₃₂”, it  correctly rounds ⁷/₆₄” to up to ¹/₈”, but will incorrectly round 1′ ⁷/₆₄” down to 1′ ³/₃₂”.

Below I’ve presented a comparison of the three methods showing how the values are rounded to the nearest ¹/₃₂” when the original data is given to the nearest ¹/₆₄”. Further information on rounding in VBA can be found here.

methodcomparison

A couple of other things I’ve incorporated, mainly for my personal needs and preferences:

  1. Added functionality to allow variable precision of inch-fractions, so the result can be rounded to the nearest ¹/₂”, ¹/₄”, ¹/₈” etc. by defining the desired denominator
    e.g. enter =FtToString(A1,16) to round to the value in cell A1 to the nearest ¹/₈” for example
    Note that Lachers function already had this functionality.
  2. Added functionality to recognize and maintain the sign convention of negative values (preceded by a -)
  3. Added ability to present the fractions using superscript/subscript text (this can be overridden by entering a 0 in the third variable input)

The Code

Option Explicit
‘Reference for shorthand variable type definitions used in this code:
‘ $ : String
‘ % : Integer (Int32)
‘ & : Long (Int64)
‘ ! : Single
‘ # : Double
‘ @ : Decimal

Public Function StringToFt(StringDim As String)
‘ Converts a string of text of Feet and Inches in to a decimal number in feet
‘ E.g. converts 15’ 5 3/16″ to 15.4322916666667

‘ The string must have the feet, inches and inch fractions separated by spaces.
‘ Strings beginning with a negative sign ‘-‘ are interpreted as negative, and the resultant value is correspondingly reported as negative.
‘ The code is also written to recognize superscript and subscript text in the inch fractions.

‘ Written by: Dan Ashby
‘ Contact: dan.t.ashby@gmail.com

‘ LAST UPDATED: 17 Feb 2017

‘ Remove inch sign, if present, and strip leading and trailing spaces
StringDim = Trim(Replace(StringDim, “”””, “”))
Dim Neg$: Neg = 1 ‘ If no leading negative sign is found, result will be assumed positive, and multiplied by Neg = 1
If Left(StringDim, 1) = “-” Then
Neg = -1
StringDim = Right(StringDim, Len(StringDim) – 1)
End If

‘ Generate array of Unicodes corresponding for the superscript and subscript numbers 0, 1, 2, 3…9
Dim i%
Dim SupCodes As Variant
Dim Subcodes As Variant
SupCodes = Array(8304, 185, 178, 179, 8308, 8309, 8310, 8311, 8312, 8313) ‘Superscript codes
Subcodes = Array(8320, 8321, 8322, 8323, 8324, 8325, 8326, 8327, 8328, 8329) ‘Subscript codes
For i = 0 To 9 ‘ Find and replace any superscript or subscript numbers
StringDim = Replace(StringDim, ChrW(SupCodes(i)), i)
StringDim = Replace(StringDim, ChrW(Subcodes(i)), i)
Next i

‘ Vet the string for illegal characters or unexpected format
Dim Chr$
For i = 1 To Len(StringDim)
Chr = Mid(StringDim, i, 1)
If Not (IsNumeric(Chr) Or Chr = “‘” Or Chr = ” ” Or Chr = “/”) Then
StringToFt = “Unexpected character: ‘” & Chr & “‘. Use format 15’ 5 3/16″””
Exit Function
End If
Next i

‘ Extract number of whole feet from the string
Dim Ft#
Dim FtSym%: FtSym = InStr(1, StringDim, “‘”)
If FtSym = 0 Then
Ft = 0
Else
Ft = Val(Left(StringDim, FtSym – 1)) ‘ Extract number of feet from the string
StringDim = Trim(Right(StringDim, Len(StringDim) – FtSym)) ‘ Trim feet characters from the string
End If

‘ Extract number of inches and inch fractions from the string
Dim Inch#
Dim Nmrtr#, Dnmtr#: Dnmtr = 1 ‘ Set denominator to 1 to avoid DIV0! error if there does not exist an inch fraction
Dim SpaceSym%: SpaceSym = InStr(1, StringDim, ” “) ‘ Locate space between inch and inch fraction, if present
Dim FracSym%: FracSym = InStr(1, StringDim, “/”) ‘ Locate inch fraction slash
If SpaceSym <> 0 Then ‘ String contains both whole inches and an inch fraction
Inch = Val(Left(StringDim, SpaceSym – 1))
Nmrtr = Val(Mid(StringDim, SpaceSym + 1, FracSym – SpaceSym))
Dnmtr = Val(Right(StringDim, Len(StringDim) – FracSym))
ElseIf FracSym <> 0 Then ‘ String contains only inch fractions – no whole inches
Inch = 0
Nmrtr = Val(Left(StringDim, FracSym – 1))
Dnmtr = Val(Right(StringDim, Len(StringDim) – FracSym))
Else ‘ String does not contain any inch fraction
Inch = Val(StringDim)
End If

StringToFt = Neg * (Ft + (Inch + Nmrtr / Dnmtr) / 12)

End Function

Public Function FtToString(Value As Variant, Optional Dnmtr# = 16, Optional ShowSupSub% = 1)
‘ This macro converts a numeric value (assumed to be in feet) in to a string in the format of feet and inches.
‘ E.g. 15.4322916666667 is displayed as 15’ 5 3/16″

‘ Optional Variable: Dnmtr (denominator) which allows user to manually define the precision of the inch fractions.
‘ E.g. enter 8 to round result to the nearest 1/8″. Enter 1 to give values to the nearest inch. Default precision is 1/16″.
‘ Optional Variable: ShowSupSub which allows user to display string using normal numeric characters, instead of the default which shows superscript/subscript characters.
‘ Enter 0 to return non-super/subscript fractions.

‘ Written by: Dan Ashby
‘ Contact: dan.t.ashby@gmail.com

‘ LAST UPDATED 17 Feb 2017

‘ Check input is numeric
If Not IsNumeric(Value) Then
FtToString = “Invalid input; not numeric”
Exit Function
End If

‘ Check denominator selected is a standard fraction used by convention in the imperial system;
‘ By convention inch fractions are reported as 1/2″, 1/4″, 1/8″ … therefore denominator must be a base-2 number
If Log(Dnmtr) / Log(2) <> Round(Log(Dnmtr) / Log(2)) Then
FtToString = “Invalid denominator; by convention must use 1, 2, 4, 16, 32 64…”
Exit Function
End If

‘ Introduce a variable to capture the sign convention of the original value
Dim Neg$: Neg = “” ‘ No sign convention is not displayed for positive numbers; this is the default behavior
If Value < 0 Then ‘ If the value is negative, then convert to positive value (makes coding easier), and store negative sign to display in the output
Value = Value * -1
Neg = “-”
End If

‘ Round value to ensure consistent behavior
Value = Fix(CDec(Value * 12 * Dnmtr) + 0.5) / 12 / Dnmtr
‘ The above line of code serves two functions:
‘ 1) Converts to Arithmetic Rounding (by default Visual Basic 6.0 uses Bankers Rounding)
‘ This isn’t desirable here as this would result in 1/32″ and 3/32″ both rounding to 1/16″
‘ 2) Corrects for floating point error that produces erratic rounding of results.
‘ E.g. floating point error would otherwise result in the following behavior:
‘ 5′ 4″ 1/32″ rounds to 5’ 4 1/16″ (i.e. the 1/32″ was rounded UP)
‘ 5′ 5″ 1/32″ rounds to 5’ 5″ (i.e. the 1/32″ was rounded DOWN)
‘ Note: The examples given above assume the default 1/16″ precision is used
‘ For further information refer: https://support.microsoft.com/en-us/help/196652/how-to-implement-custom-rounding-procedures

Dim Ft#, Inch#, Nmrtr# ‘Dim as Double variables
Ft = Int(Value)
Inch = Int((Value – Ft) * 12)
Nmrtr = Round(((Value – Ft) * 12 – Inch) * Dnmtr)
‘Nmrtr = Fix(((Value – Ft) * 12 – Inch) * Dnmtr + 0.5)

‘ If fraction rounds up to an inch, increment inch and set fraction to nul.
If Nmrtr = Dnmtr Then
Nmrtr = 0
Inch = Inch + 1
End If

‘ If inches round up to a foot for the precision specified, increment the foot value and set inch to nul.
If Inch = 12 Then
Inch = 0
Ft = Ft + 1
End If

‘ If there’s a fraction of an inch to report, this must first be simplified and formatted
If Nmrtr <> 0 Then
Do While Nmrtr Mod 2 = 0 ‘ Simplify fraction until numerator is an odd number
Nmrtr = Nmrtr / 2
Dnmtr = Dnmtr / 2
Loop

‘ Create fraction string to permit replacement of characters with superscript/subscript
Dim NmrtrStr$: NmrtrStr = Nmrtr
Dim DnmtrStr$: DnmtrStr = Dnmtr

‘ If ShowSupSub is True then convert the numerator to superscript and denominator to subscript
If ShowSupSub = 1 Then

‘ Generate array of Unicodes corresponding for the superscript and subscript numbers 0, 1, 2, 3…9
Dim SupCodes As Variant
Dim Subcodes As Variant
SupCodes = Array(8304, 185, 178, 179, 8308, 8309, 8310, 8311, 8312, 8313) ‘Superscript codes
Subcodes = Array(8320, 8321, 8322, 8323, 8324, 8325, 8326, 8327, 8328, 8329) ‘Subscript codes

‘ Convert numerator value to corresponding superscript characters
Dim i%
For i = 1 To Len(NmrtrStr)
If IsNumeric(Mid(NmrtrStr, i, 1)) Then
NmrtrStr = Replace(NmrtrStr, (Mid(NmrtrStr, i, 1)), ChrW(SupCodes(Mid(NmrtrStr, i, 1))))
End If
Next i

‘ Convert denominator value to corresponding subscript characters
For i = 1 To Len(DnmtrStr)
If IsNumeric(Mid(DnmtrStr, i, 1)) Then
DnmtrStr = Replace(DnmtrStr, Mid(DnmtrStr, i, 1), ChrW(Subcodes(Mid(DnmtrStr, i, 1))))
End If
Next i
End If ‘Exits if statement checking for superscript/subscript application
End If ‘Exits if statement checking for presence of a fraction

‘Format the string to include foot and inch symbols
FtToString = Neg & _
IIf(Ft = 0, “”, Ft & “‘”) & _
IIf(Ft <> 0 And (Inch <> 0 Or Nmrtr <> 0), ” “, “”) & _
IIf(Inch = 0 And (Ft <> 0 Or Nmrtr <> 0), “”, Inch) & _
IIf(Nmrtr <> 0 And Inch <> 0, ” “, “”) & _
IIf(Nmrtr <> 0, NmrtrStr & “/” & DnmtrStr, “”) & _
IIf(Ft <> 0 And Inch = 0 And Nmrtr = 0, “”, “”””)
‘ Line 1 displays a leading negative sign, if the original value was negative
‘ Line 2 displays feet and foot symbol if non-zero, otherwise nothing
‘ Line 3 displays a space between the feet and inches (or inch fraction), as applicable
‘ Line 4 displays a zero if all values are zero, otherwise nothing (if a zero value was entered, 0″ will be returned)
‘ Line 5 displays a space between the inches and inch fraction, as applicable
‘ Line 6 displays the inch fraction, as applicable
‘ Line 7 displays the inch symbol, unless the value is an even number of feet without any inches or inch fractions

End Function

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s