Tanjim Reza
When working with numbers and calculations, it is a common thing to get fractions or decimals as results. Sometimes it happens that, the result is shown as a fraction, but we need the result in decimal. At this time, we need to know how to convert fraction numbers to decimals in Excel. This article will show you 2 quick tricks to convert fraction to decimal in Excel.
Download Practice Workbook
You can download and practice from our workbook here.
What Is a Fraction?
A fraction number is a number that is not an integer but expressed as a quotient. The upper part of the quotient is called the Numerator and the lower part of the fraction is called the Denominator.
For example, ¼ is a fraction number where 1 is the numerator and 4 is the denominator.
What Is a Decimal Number?
A decimal number is a number that is not an integer but not expressed as a fraction as well. It is a number situated between two integers. And it is comprised of an integer and a decimal part where the decimal part is the fractional value. In addition to these, the decimal number represents that the base of the number is 10. This means there can be 10 unique digits in a number.
2 Easy Approaches to Convert Fraction to Decimal in Excel
In the following dataset, we have 6 random fraction numbers, which we want to convert to decimal numbers. Follow any of these handy approaches below to convert fractions to decimals.
1. Use the Format Cells Command to Convert Fraction to Decimal in Excel
You can convert fractions to decimals in Excel by accessing the Format Cells dialog box. You can do this by changing the category of cells in two ways. Follow any of these as per your requirements.
1.1 Converting into General Format
You can convert your fraction number to decimal by changing the category into the General category. Follow the steps below to accomplish this. 👇
Steps:
- At first, copy the fraction numbers and paste them into the cells where you want to convert to decimal. Subsequently, select all the cells and press the right-click button. Consequently, click on the Format Cells… option from the context menu.
- Now, the Format Cells window will appear. Now, go to the Number tab. Subsequently, select the General option from the Category list. Consequently, click on the OK button.
Thus, all the fractions will be converted to decimals. But, in this way, you can not choose any formatting for the decimal results. Finally, the result sheet will look like this. 👇
1.2 Converting into Number Format
Besides, you can convert your fraction number to decimal by changing the category into the Number category. Follow the steps below to accomplish this. 👇
Steps:
- Just like the first method, copy the fraction numbers and paste them into the cells where you want to convert to decimal at first. Subsequently, select all the cells and press the right-click button. Consequently, click on the Format Cells… option from the context menu.
- Now, the Format Cells window will appear. Now, go to the Number tab. Subsequently, select the Number option from the Category list. Consequently, click on the OK button.
- You can also choose the formatting for the decimal numbers here. You can choose decimal places, negative numbers format, use of thousands separator, etc in this category.
Thus, all the fractions will be converted to decimals. And, in this way, you can choose your desired formatting for the decimal results. Finally, the result sheet will look like this. 👇
Read More: How to Convert Decimal to Fraction in Excel (4 Smooth Approaches)
2. Use the Number Format Icon
You can convert your fraction number to decimal by using the number format icon. Follow the steps below to accomplish this. 👇
Steps:
- Like the first method, copy the fraction numbers and paste them into the cells where you want to convert to decimal at first. Subsequently, select all the cells and go to the Home tab. Next, go to the Number group. Subsequently, click on the Number Format icon situated at the bottom-right corner of the group.
- At this time, the Format Cells window will appear. Now, follow step 2 from the 1.1 method or follow steps 2 and 3 from the 1.2 method as per your requirements.
Finally, you will get your fractions converted into decimals.
Read More: How to Make Fractions Smaller in Excel (2 Possible Ways)
Convert Fraction in Text Format to Decimal in Excel
Now, if you gather data from the internet that contains fractions, and the fractions lose their format, rather than appearing in text format; the formulas will help you in converting them to decimal. We will discuss them in two cases.
Case 1: If the Fractions Are Proper
A proper fraction is a fraction where the numerator is less than the denominator. For converting this type of fraction to decimal, follow the steps below. 👇
Steps:
- Select the cell where you want to get your converted decimal result. Say, we want to convert the B6 cell fraction data converted to decimal.
- Now, put the following formula in the result cell.
=LEFT(B6,SEARCH("/",B6)-1)/MID(B6,SEARCH("/",B6)+1,99)Subsequently, press the Enter button. And, you can also drag the fill handle downward to copy the formula.
Thus, you can convert fractions to decimals from a text file.
Case 2: If the Fractions Are Mixed
A mixed fraction is a fraction where there is a natural number and a fraction together. For converting this type of fraction to decimal, follow the steps below. 👇
Steps:
- Select the cell where you want to get your converted decimal result. Say, we want to convert the B5 cell mixed fraction data converted to decimal.
- Now, put the following formula in the result cell.
=IF(ISERROR(SEARCH(" ",B5)),LEFT(B5,SEARCH("/",B5)-1)/MID(B5,SEARCH("/",B5)+1,99),LEFT(B5,SEARCH(" ",B5)-1)+MID(B5,SEARCH(" ",B5)+1,SEARCH("/",B5)-1-SEARCH(" ",B5))/MID(B5,SEARCH("/",B5)+1,99))
Subsequently, press the Enter button. And, you can also drag the fill handle downward to copy the formula.
Thus, you can convert fractions to decimals from a text file. And, the result will look like this. 👇
Read More: How to Write a Fraction in Excel (4 Methods)
Conclusion
So, in this article, I have shown you the 2 easiest tricks to convert fractions to decimals in Excel along with converting fractions from a text file. Use any of these ways to accomplish the result in this regard. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to contact me. And, visit ExcelDemy for many more articles like this. Thank you!
Sometimes mixed fractions are written with the ASCII 160 (non-breaking space) character instead of the usual ASCII 32 character that Excel recognizes as ” “. If you get a *VALUE! error, you need to use “CHAR(160)” instead of ” ” in the formula, or clean up your text with a find and replace operation before applying the formula.
Hi, ERIC!
Thank you for your valuable feedback.
You have pointed out a nice thing. When the #VALUE! error happens in this regard, it is better to use the find and replace feature to replace ” ” with CHAR(160) before applying the formula.
Regards,
Tanjim Reza
Good day, this is indeed awesome tips. Can you help me getting the correct formula for time worked when people clock in and out but only get paid for set hours? e.g, clock in 06:20 – clock out 16:25,but only get paid from 07:00 to 16:00,less 15min for lunch? (they get 45min lunch, but they wanted 1 hour lunch so they sacrifice 15min).thus they get paid only for 8.75hours. My challenge is that it must be displayed as 8.75 and not 8,45.
Second challenge :
My hours must work as quarters example
15min must show as 0.25
30min must show as 0.5
45min must show as 0.75
I followed various tries but it does not work the way I need it to work.
If a person’s total time spend is 8hours and 17 min I need a formula yo show it as 8.25 (17min as a fraction of. 25)
If he is Kate and clocks in at 09:22 and clock out at 16:00 his hours to be paid wil be ((16:00-09:22)less 15min) displayed as 6.23, but I need to get it to the closest fraction display as 6.25.(the nearest quarter on the clock)
If it was a weekend this hours will be 6.38 but must be displayed as 6.75 (the nearest quarter on the clock).
Is the above possible with formula for weekdays and different formula for weekends where there is no deduction of 15min for lunch time? Thanking you in advance for your help and time.
Hi RINA,
Thanks for your comment. Per your comment, we have tried implementing the conditions in our dataset. You can see the result below:
Here, we have used the formula below in Cell I2:
=IFS(H2=15,CONCAT(HOUR(F2),".25"),H2=30,CONCAT(HOUR(F2),".5"),H2=45,CONCAT(HOUR(F2),".75"))According to one of your conditions (in case of Kate), you wanted 6:23 to be displayed as 6.25. But if you want to display time to the nearest quarter on the clock, it gives 6.5. You can check out the Excel file from the link below:
Answer.xlsx
It will be easier for us to give a proper solution if you send us an Excel file with the desired outputs. You can send the Excel file to my email: [email protected]. Please let us know if you have any other queries.
Regards
Mursalin,
Exceldemy.