Table of Contents
Do you need to convert a fraction into a decimal in Excel? This tutorial will guide you step-by-step on how to use Excel to convert a fraction into a decimal. We’ll discuss some of the different methods for converting fractions to decimals, such as using the in-built function in Excel or manually entering the fraction into the cell. Whether you’re a beginner or an Excel expert, you’ll find this tutorial helpful. Let’s get started!
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.
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.