You can use the following formula to convert a fraction to a decimal in Excel:
=IF(ISERROR(SEARCH(" ",A2)),LEFT(A2,SEARCH("/",A2)-1)/MID(A2,SEARCH("/",A2)+1,99),LEFT(A2,SEARCH(" ",A2)-1)+MID(A2,SEARCH(" ",A2)+1,SEARCH("/",A2)-1-SEARCH(" ",A2))/MID(A2,SEARCH("/",A2)+1,99))
This particular formula will convert the fraction in cell A2 to a decimal.
For example, if cell A2 contains 1/5 then this formula will return 0.2.
It’s worth noting that this formula works with all types of fractions including proper fractions, improper fractions, and mixed numbers.
The following example shows how to use this formula in practice.
Example: Convert Fractions to Decimals in Excel
Suppose we have the following list of fractions in Excel:
Suppose we would like to display each fraction in column A as a decimal in column B.
To do so, we can type the following formula into cell B2:
=IF(ISERROR(SEARCH(" ",A2)),LEFT(A2,SEARCH("/",A2)-1)/MID(A2,SEARCH("/",A2)+1,99),LEFT(A2,SEARCH(" ",A2)-1)+MID(A2,SEARCH(" ",A2)+1,SEARCH("/",A2)-1-SEARCH(" ",A2))/MID(A2,SEARCH("/",A2)+1,99))
We can then drag and fill this formula down to each remaining cell in column B:
Column B now displays each fraction in column A as a decimal.
For example:
- 1/5 as a decimal is 0.2.
- 3 4/5 as a decimal is 3.8.
- 5 11/12 as a decimal is 5.916667.
- 7/2 as a decimal is 3.5.
And so on.
Note that if you only have proper fractions (i.e. where the numerator is less than the denominator) then you can use the following formula that is much simpler:
=LEFT(A2,SEARCH("/",A2)-1)/MID(A2,SEARCH("/",A2)+1,99)
The following screenshot shows how to use this formula in practice:
Column B displays each proper fraction in column A as a decimal.
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
