### The problem* (Americans, mainly…)*

Having previously worked in New Zealand, I was initially sheltered from mess that is the imperial system of measurement, but here in Canada we share a border with the only developed nation left on the planet that’s still flogging this dead horse; the United States.

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, Yards, 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.

### 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 conventionally used in length measurements, 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.

I noticed that Lacher’s function already had this functionality. - Added functionality to recognize and maintain the sign convention of negative values, which are reported in parenthesis.
- Added ability to present the fractions using superscript/subscript text (this can be overridden by entering a 0 in the third variable input)
- UPDATE 5 DEC 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, and/or the Inch and Inch Fraction values.

By default, Feet and Inches are separated with a hyphen, and the Inch and Inch Fractions are separated with a space as below:

13′-5 3/16″

If you’d like to change this format, you need only modify the individual lines in the code where the Public Const variables**FtInchSeparator**and**InchInchFractionSeparator**are 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 separate all elements with a space, 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 inches to be expressed as 15’-5″ then enter “-” as the separator

Public Const FtInchSeparator As String = “-“‘ Define a character or series of characters to separate reported inches and inch fractions

‘ E.g. if you want 15 and 3/16 inches to be expressed as 5-0 3/16″ then enter “-” as the separator

Public Const InchInchFractionSeparator 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

‘

‘ 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: 1 December 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 FtSymLoc As Integer: FtSymLoc = InStr(1, StringDim, “‘”)

Dim FtInchSeparatorLoc As Integer: FtInchSeparatorLoc = InStr(1, StringDim, FtInchSeparator)

Dim InchInchFractionSeparatorLoc As Integer: InchInchFractionSeparatorLoc = InStr(1 + IIf(FtSymLoc = 0, 0, FtSymLoc + Len(FtInchSeparator)), StringDim, InchInchFractionSeparator)

For i = 1 To Len(StringDim)

If i = FtSymLoc Then ‘ Skip over the Ft symbol, and the FtInchSeparator characters

i = i + Len(FtInchSeparator)

ElseIf i = InchInchFractionSeparatorLoc Then ‘ Skip over the InchInchFractionSeparator characters

i = i + Len(InchInchFractionSeparator) – 1

Else

‘ Check the string, character-by-character, to ensure that the input matches the format expected

‘ Aside from the previously skipped Ft symbol, the FtInchSeparator and the InchInchFractionSeparator,

‘ the string should now only contain numbers, and potentially a fraction slash symbol.

Chr = Mid(StringDim, i, 1)

If Not (IsNumeric(Chr) Or Chr = “/”) Then

‘ If illegal characters are found, exit the function and report the offending character

StringToFt = “Unexpected character encountered: ‘” & Chr & “‘. Use format 15′” & FtInchSeparator & “5” & InchInchFractionSeparator & “3/16″””

Exit Function

End If

End If

Next i‘ Extract number of whole feet from the string

Dim Ft As Double

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” & InchInchFractionSeparator & “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 ‘ Foot symbol (‘) has been found

‘ 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 FtInchSeparator 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” & InchInchFractionSeparator & “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 IfStringDim = Trim(Right(StringDim, Len(StringDim) – FtSymLoc – Len(FtInchSeparator))) ‘ 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. Check this has 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” & InchInchFractionSeparator & “3/16″””

Exit Function

End If

End If‘ Extract number of inches and inch fractions from the string

‘ At this point, the preceding code has stripped out the feet, foot symbol, and the characters separating the feet and inches,

‘ therefore StringDim now contains only the characters reflecting the inch and inch fractions, if appliccable

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

InchInchFractionSeparatorLoc = InStr(1, StringDim, InchInchFractionSeparator)

If InchInchFractionSeparatorLoc <> 0 Then ‘ String contains both whole inches and an inch fraction

Inch = Val(Left(StringDim, InchInchFractionSeparatorLoc – 1)) ‘ Whole inches are contained within the string preceding the characters that separates the inches from the inch fraction

Nmrtr = Val(Mid(StringDim, InchInchFractionSeparatorLoc + Len(InchInchFractionSeparator), FracSymLoc – InchInchFractionSeparatorLoc)) ‘ Inch fraction numerator is between the end of the characters separating inches and inch fractions, 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 IfStringToFt = 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: 1 December 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-proceduresDim 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((Ft <> 0 Or Inch <> 0) And Nmrtr <> 0, InchInchFractionSeparator, “”) & _

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 the inches and inch fraction separator character(s), 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 negativeEnd Function

### Updates

#### 5 March 2017

First published

#### 16 July 2017

Update the code for the following changes:

- 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 hyphen).

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

**5 December 2017**

Updated the code to add the ability to separate both the Feet and Inches, and the Inch and Inch Fractions with preferred characters.