Feet and inches in Excel

The problem (Americans, mainly…)

imerialsystemAs a structural engineer working in Canada, I have to work with 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 it being 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. So here’s mine…

The solution

There’s plenty of work-arounds that you can use in Excel, but none of them can simultaneously display a measurement 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. Note that I recorded this video prior to making a change to the input format; the code now assumes that the format will have a hyphen between the feet and inch measurements – refer update history, below.

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 found they had 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 Lacher’s function already had this functionality.
  2. Added functionality to recognize and maintain the sign convention of negative values, which are reported in parenthesis.
  3. Added ability to present the fractions using superscript/subscript text (this can be overridden by entering a 0 in the third variable input)
  4. UPDATE 16 JULY 2017: Added the ability to quickly and easily change the desired format in which the measurements are reported, with the user being able to define a character or series of characters to separate the reported feet and inch values. By default these are separated with a space, a hyphen, then another space as below:
    13′ – 5 3/16″
    If you’d like to change this format, you need only modify a single line in the code where the Public Const variable FtInchSeparator is defined (right at the start of the code; I’ve highlighted this blue below, where I’ve shown the code).
    For example, if you wanted to remove the spaces to just use a single hyphen character, you could make the following change:
    Change: Public Const FtInchSeparator As String = ” – “
    To:          Public Const FtInchSeparator As String = “-“
    This will change the output as below:
    13′ – 5 3/16″ becomes;
    13′-5 3/16″

The Code

Option Explicit

‘ Define a character or series of characters to separate reported feet and inches
‘ E.g. if you want 15 feet, 5 and 3/16 inches to be expressed as 15’-5 3/16″ then enter “-” as the separator
Public Const FtInchSeparator As String = “-”

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

‘ Strings contained in parenthesis are interpreted as negative, and the resultant value is correspondingly reported as negative.
‘ E.g. (15’-5 3/16″) converts to -15.4322916666667

‘ The string must have the inches and inch fractions separated by a space.
‘ Feet and inches are separated by the Public Constant named ‘FtInchSeparator’, which has been defined above.

‘ The code is also written to recognize superscript and subscript characters in the inch fractions.

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

‘ Last Updated: 9 August 2017

‘ Check for presence of inch symbol
Dim InchSymLoc As Integer: InchSymLoc = InStr(1, StringDim, “”””)

‘ Remove inch sign, if present, and strip leading and trailing spaces
StringDim = Trim(Replace(StringDim, “”””, “”))

‘ Check for parenthesis, indicating a negative value
Dim Neg As Double: Neg = 1 ‘ Set default behavior, which assumes the input value is positive
If Left(StringDim, 1) = “(” And Right(StringDim, 1) = “)” Then ‘ Check for enclosing parenthesis
Neg = -1 ‘ If enclosing parenthesis are found, store negative unit value to change the sign of the final reported value
StringDim = Mid(StringDim, 2, Len(StringDim) – 2) ‘ Trim the leading and trailing characters (the parenthesis)
End If

‘ Generate array of Unicodes corresponding for the superscript and subscript numbers 0, 1, 2, 3…9
Dim i As Integer
Dim SupCodes As Variant
Dim Subcodes As Variant
SupCodes = Array(8304, 185, 178, 179, 8308, 8309, 8310, 8311, 8312, 8313) ‘Superscript Unicode character codes for numbers 1 through 9
Subcodes = Array(8320, 8321, 8322, 8323, 8324, 8325, 8326, 8327, 8328, 8329) ‘Subscript Unicode character codes for numbers 1 through 9
For i = 0 To 9 ‘ Find and replace any superscript or subscript characters with the corresponding integer
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 As String
Dim FtInchSeparatorLoc As Integer
FtInchSeparatorLoc = InStr(1, StringDim, FtInchSeparator)
For i = 1 To Len(StringDim)
‘ Skip over the FtInchSeparator characters, which may contain any characters desired
If i = FtInchSeparatorLoc Then
i = i + Len(FtInchSeparator)
End If
‘ Check the string, character-by-character, to ensure that the input matches the format expected
‘ Aside from the FtInchSeparator, the string should now only contain numbers, and potentially a foot symbol, a space, and a fraction slash symbol.
Chr = Mid(StringDim, i, 1)
If Not (IsNumeric(Chr) Or Chr = “‘” Or Chr = ” ” Or Chr = “/”) Then
‘ If illegal characters are found, exit the function and report the offending character
StringToFt = “Unexpected character: ‘” & Chr & “‘. Use format 15′” & FtInchSeparator & “5 3/16″””
Exit Function
End If
Next i

‘ Extract number of whole feet from the string
Dim Ft As Double
Dim FtSymLoc As Integer: FtSymLoc = InStr(1, StringDim, “‘”) ‘ Variable tracks the position of the Foot Symbol (‘) within the text string
If FtSymLoc = 0 Then
‘ Given foot symbol has not been found, double-check that inch units were provided
If InchSymLoc = 0 Then
‘ If neither foot nor inch symbols are found, then the input string is ambiguous, as it’s not clear if it ought to be interpreted as inches or feet
StringToFt = “Ambiguous input: Provide unit symbols indicating whether input is in feet or inches. Use format 15′” & FtInchSeparator & “5 3/16″””
Exit Function
End If
‘ If inch symbols are provided, but no foot symbol was found, then interpret this to mean dimension string was given in inches only.
Ft = 0
Else
‘ Given foot symbol has been found, check that – if an inch symbol is also within the string – the inches and feet are separated using the FtInchSeparatorLoc character(s)
If InchSymLoc <> 0 And FtInchSeparatorLoc = 0 Then
StringToFt = “Invalid input format: The character(s) used to separate the feet and inches is invalid. Use format 15′” & FtInchSeparator & “5 3/16″””
Exit Function
End If

‘ Extract number of feet from the string
Ft = Val(Left(StringDim, FtSymLoc – 1)) ‘ Extract number of feet from the string

‘ If the last character of the string was the foot symbol, then there are no inches or inch fractions to process, therefore report the whole number of feet contained in the string
If FtSymLoc = Len(StringDim) Then
StringToFt = IIf(Ft <> 0, Neg, 1) * Ft
Exit Function
End If

StringDim = Trim(Right(StringDim, Len(StringDim) – FtInchSeparatorLoc – Len(FtInchSeparator) + 1)) ‘ Trim characters preceding the inch and inch fraction parts of the string

‘ Given foot symbol has been found, anything remaining to the right of it must be in inches. This must have an inch symbol provided
If InchSymLoc = 0 And StringDim <> “” Then
StringToFt = “Invalid input format: The inch symbol is missing from the end of the string. Use format 15′” & FtInchSeparator & “5 3/16″””
Exit Function
End If
End If

‘ Extract number of inches and inch fractions from the string
Dim Inch As Double
Dim Nmrtr As Double, Dnmtr As Double: Dnmtr = 1 ‘ Set denominator to 1 to avoid DIV0! error if there does not exist an inch fraction
Dim SpaceSymLoc As Integer: SpaceSymLoc = InStr(1, StringDim, ” “) ‘ Locate space between inch and inch fraction, if present
Dim FracSymLoc As Integer: FracSymLoc = InStr(1, StringDim, “/”) ‘ Locate inch fraction slash
If SpaceSymLoc <> 0 Then ‘ String contains both whole inches and an inch fraction
Inch = Val(Left(StringDim, SpaceSymLoc – 1)) ‘ Whole inches are contained within the string preceding the space that separates them from the inch fraction
Nmrtr = Val(Mid(StringDim, SpaceSymLoc + 1, FracSymLoc – SpaceSymLoc)) ‘ Inch fraction numerator is between the space and the fraction slash symbol
Dnmtr = Val(Right(StringDim, Len(StringDim) – FracSymLoc)) ‘ Inch fraction denominator is whatever remains after the fraction slash symbol
ElseIf FracSymLoc <> 0 Then ‘ Check for the presence of a fraction slash symbol, indicating that there is an inch fraction within the string
Inch = 0 ‘ Given that the case where a space character was found has been previously handled, this ElseIf statement handles instances where no space was found, hence there are no whole inches in the string.
Nmrtr = Val(Left(StringDim, FracSymLoc – 1)) ‘ Inch fraction numerator is whatever lies to the left of the fraction slash symbol
Dnmtr = Val(Right(StringDim, Len(StringDim) – FracSymLoc)) ‘ Inch fraction denominator is whatever lies to the right of the fraction slash symbol
Else ‘ String does not contain any inch fraction
Inch = Val(StringDim) ‘ Remaining string contains only whole inches
End If

StringToFt = Ft + (Inch + Nmrtr / Dnmtr) / 12 ‘ Sum all components, dividing the inches by 12 to report resulting value in feet.
StringToFt = IIf(StringToFt <> 0, Neg, 1) * StringToFt ‘ Handle the case where the input is zero, to avoid the result being reported as negative zero.

End Function

Public Function FtToString(Value As Variant, Optional Dnmtr As Double = 16, Optional ShowSupSub As Integer = 1)
‘ 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.

‘ Strings contained in parenthesis are interpreted as negative, and the resultant value is correspondingly reported as negative.
‘ E.g. -15.4322916666667 is displayed as (15’-5 3/16″)

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

‘ Last Updated: 9 Aug 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 As Boolean: Neg = False ‘ No sign convention is displayed for positive numbers; this is the default behavior
If Value < 0 Then
Value = Value * -1 ‘ Convert to positive value (makes coding easier). The sign convention is conveyed at the end by enclosing the output string in parenthesis
Neg = True ‘ Make a note of sign convention, such that parenthesis can be subsequently added to notate negative value
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 As Double, Inch As Double, Nmrtr As Double ‘Dim as Double variables
Ft = Int(Value) ‘ Store feet as the integer value of in input (rounds input value down to nearest whole number)
Inch = Int((Value – Ft) * 12) ‘ Store the value of inches as a decimal, taken to be the input value, converted to inches, that remains after subtracting the whole number of feet
Nmrtr = Round(((Value – Ft) * 12 – Inch) * Dnmtr) ‘ Numerator is taken to be the nearest wole number of inch fractions that remain of the input value, converted to inches, after subtracting the whole number of feet and inches

‘ 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
‘ The numerator must always be an odd number.
‘ If it’s divisible by 2, then this indicates that the fraction may be further simplified. E.g. 6/16″ simplifies to 3/8″
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 As String: NmrtrStr = Nmrtr
Dim DnmtrStr As String: 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 As Integer
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 = IIf(Neg, “(“, “”) & _
IIf(Ft = 0, “”, Ft & “‘”) & _
IIf(Ft <> 0, FtInchSeparator, “”) & _
IIf(Inch = 0 And Ft = 0 And Nmrtr <> 0, “”, Inch) & _
IIf(Inch <> 0 And Nmrtr <> 0, ” “, “”) & _
IIf(Nmrtr <> 0, NmrtrStr & “/” & DnmtrStr, “”) & _
“””” & _
IIf(Neg, “)”, “”)
‘ Line 1 displays a open parenthesis, if the original value was negative
‘ Line 2 displays feet and foot symbol if non-zero, otherwise nothing
‘ Line 3 displays a character or characters that separate the feet and inches (or inch fraction), as applicable. This is set as a public constant, as it’s referenced by the modules that convert both to and from strings.
‘ 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
‘ Line 8 displace a closing parenthesis, if the original value was negative

End Function

Updates

5 March 2017

First published

16 July 2017

Update the code for the following changes:

  1. Changed the default format of the string from 13′ 5 3/16″ to 13′ – 5 3/16″
    I.e. feet and inches are now separated with a space, then a hyphen, and another space).
  2. Changed the format of negative values from –13′ 5 3/16″ to (13′ – 5 3/16″)
    I.e. negative numbers are now presented in parenthesis.
  3. Added additional error handling to check that the input format of strings matches the format given above.

16 September 2017

Uploaded a newer version of the code incorporating minor tweaks, and a new demonstration video with better audio.

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