Convert feet and inches to inches

In this example the goal is to parse feet and inches out the text strings shown in column B, and create a single numeric value for total inches. The challenge is that each of the two numbers is embedded in text. The formula can divided into two parts. In the first part of the formula, feet are extracted and converted to inches like this:

=LEFT(B5,FIND("'",B5)-1)*12

Working from the inside out, the FIND function is used to locate the position of the single quote (') in the string. This number (minus one) goes into the LEFT function as the number of characters to extract. For cell B5, LEFT returns 8, which is then multiplied by 12 to get inches. Note that the LEFT function will return text, but the math operation of multiplying by 12 will automatically convert the text a number.

In the second part of the formula, we extract the value for inches from the text with this:

SUBSTITUTE(MID(B5,FIND("'",B5)+1,LEN(B5)),"""","")

Here we again locate the position of the single quote (') in the string with FIND. This time however we add one, and feed the result into the MID function as the starting point for extracting characters.

For the number of characters to extract, we cheat and use the LEN function. LEN will return the total characters in B5, which is a larger number of characters than we want to extract from the string. However, MID will simply extract all remaining characters without complaint. For B5, MID will return " 4""", which goes into the SUBSTITUTE function as text.

The next step is to use the SUBSTITUTE function to remove the double quote ("). SUBSTITUTE is configured to look for a double quote, and replace with an empty string (""). In B5, SUBSTITUTE returns " 4" as text. At this point, we could just add this result directly to the inches already calculated in part 1. As before, the math operation will convert text value to a number automatically.

However, to handle the measurements in B12:B13, which include a hyphen (-) between feet and inches, we instead hand off the result to the ABS function. Without this step, the the hyphen will cause Excel to interpret the extracted inch value as a negative number, which will cause the formula to return an incorrect result. Giving the value to ABS guards against this problem. If a hyphen is present, ABS interprets the value as negative, and flips the value to a positive number. If a hyphen is not present, ABS returns the number unchanged. This allows the same formula to handle both cases.

Other units

Once you have a numeric measurement in inches, you can use the CONVERT function to convert to other units of measure.