#Custom formatting excel subtraction install#

I found one example by Bill Jelen from here, which is actually what inspired me to write mine.Īlso, this evening as I was writing this up I found a second very similar macro by John F. A deeper diveīefore writing these functions I did a lot of searching to see if anyone had already done this.

#Custom formatting excel subtraction install#

Refer to my earlier blog post where I demonstrate how I install this here.Īlternatively you can manually copy the code in yourself from below. You can download this Excel module containing the two functions here. 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 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. 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. 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. This is further complicated by the use of inch fractions instead of using a decimal system that’s native to today’s software. Length, for example, is routinely expressed in Miles, Yards, Feet and Inches.

Why is this system absurd? Because it uses a plethora of units to represent the same type of measurement. 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.