You can use the following formula to convert feet and inches into a decimal in Excel:
=TEXTBEFORE(A2,"'")+TEXTBEFORE(TEXTAFTER(A2,"'"),"""")/12
This will convert the feet and inches in cell A2 into a decimal.
For example, if cell A2 contains 6’5″ then this formula will convert this into 6.4167.
The following example shows how to use this formula in practice.
Example: Convert Feet and Inches to Decimal in Excel
Suppose we have the following dataset in Excel that shows the height of various athletes:
To convert each height from feet and inches into a decimal, we can type the following formula into cell B2:
=TEXTBEFORE(A2,"'")+TEXTBEFORE(TEXTAFTER(A2,"'"),"""")/12
We can then drag and fill this formula down to each remaining cell in column B:
Column B now displays the feet and inches in column A as a decimal.
For example:
- 6 feet and 2 inches is equal to 6.1667 feet.
- 6 feet and 5 inches is equal to 6.4167 feet.
- 5 feet and 10 inches is equal to 5.8333 feet.
And so on.
How This Formula Works
Recall the formula that we used to convert feet and inches into a decimal:
=TEXTBEFORE(A2,"'")+TEXTBEFORE(TEXTAFTER(A2,"'"),"""")/12
Here is how this formula works:
First, we use TEXTBEFORE(A2, “‘”) to extract the text before the first single quote in cell A2.
This extracts the value 6.
Next, we use TEXTBEFORE(TEXTAFTER(A2,”‘”),””””)/12 to extract the text between the single quote and the double quote and divide it by 12.
This results in 2 / 12 = 0.1667.
Lastly, we use a plus sign ( + ) to add these two values.
This results in 6 + 0.1667 = 6.1667.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: How to Convert Time Duration to Minutes
Excel: How to Convert Percentage to Ratio
Excel: How to Convert UNIX Timestamp to Date
