The challenge is that each of the two numbers is embedded in text.

The formula can be divided into two parts.

In the first part of the formula, feet are extracted and converted to inches.

Excel formula: Convert inches to feet and inches

In the second part, inches are extracted and added to the result.

In B5, SUBSTITUTE returns " 4" as text.

To guard against this problem, we hand off the number extracted for inches to theABS function.

Excel formula: Split dimensions into three parts

If a hyphenispresent, ABS interprets the value as negative and flips the value to a positive number.

If a hyphenis notpresent, ABS returns the number unchanged.

ABS also coerces the text value to a number.

Excel formula: Split text string at specific character

For example, =LEFT(“apple”,3) returns “app”.

When the text is not found, FIND returns a #VALUE error.

For example, =MID(“apple”,2,3) returns “ppl”.

Excel formula: Strip non-numeric characters

SUBSTITUTE Function

The Excel SUBSTITUTE function replaces text in a given string by matching.

Excel LEFT function

Excel FIND function

Excel MID function

Excel SUBSTITUTE function

Article image