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

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

- 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. - Added functionality to recognize and maintain the sign convention of negative values (preceded by a -)
- 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

‘ @ : DecimalPublic 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 IfStringToFt = 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-proceduresDim 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 fractionsEnd Function