Excel formula: Convert feet and inches to inches

Generic formula

=INT(A1/12)&"' "&MOD(A1)&""""

Summary

To convert a measurement in feet and inches to inches only (i.e. 4'5" to 53) you can use a formula based on several functions, including LEFT, FIND, MID, and SUBSTITUTE. In the example shown, the formula in D5 is:

=LEFT(B5,FIND("'",B5)-1)*12+SUBSTITUTE(MID(B5,FIND("'",B5)+1,LEN(B5)),"""","")

Explanation

In the first part of the formula, feet are extracted and converted LEFT and FIND like this:

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

Working from the inside out, FIND 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: LEFT will return text, but multiplying by 12 will covert the text a number.

In the second part of the formula, we extract the inches part of the text value 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 than exist after the single quote in the string. however, MID will simply extract all remaining characters. For B5, MID will return " 4""", which goes into the SUBSTITUTE function as text.

SUBSTITUTE is configured to look for a double quote ("""") and replace with nothing (""). In the case of B5, SUBSTITUTE returns " 4" as text, which is added to the feet number already determined. As before, the math operation converts the text a number, and Excel returns a final result of 100.

Other units

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

Author

Dave Bruns

Related formulas

Convert inches to feet and inches

This formula converts a numeric value in inches to text representing the same measurement in inches and feet. To get the value for feet, the INT function is used like this: = INT ( B5 / 12 ) & "' " Inside INT, the value in B5 is...

Split dimensions into three parts

To split dimensions that like 100x50x25 into three separate parts, you can use some rather complicated formulas that use LEFT, MID, RIGHT, FIND, LEN, and SUBSTITUTE. Note: you can also use Flash Fill in Excel 2013 and above, and the "text to columns...

Split text string at specific character

The first formula uses the FIND function to locate the underscore(_) in the text, then we subtract 1 to move back to the "character before the special character". FIND ( "_" , B5 ) - 1 In this example , FIND returns 7, so we end up with 6...

Related functions

Excel LEFT Function

The Excel LEFT function extracts a given number of characters from the left side of a supplied text string. For example, LEFT("apple",3) returns "app".

Excel FIND Function

The Excel FIND function returns the position (as a number) of one text string inside another. When the text is not found, FIND returns a #VALUE error.

Excel MID Function

The Excel MID function extracts a given number of characters from the middle of a supplied text string. For example, =MID("apple",2,3) returns "ppl".

Excel SUBSTITUTE Function

The Excel SUBSTITUTE function replaces text in a given string by matching. For example =SUBSTITUTE("952-455-7865","-","") returns "9524557865"; the dash is stripped. SUBSTITUTE is case-sensitive and does not support wildcards.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.