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.
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.
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.
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”.
SUBSTITUTE Function
The Excel SUBSTITUTE function replaces text in a given string by matching.