In Excel, working with decimals is often easier than fractions. That’s why you might want to convert fractions to decimals—it just makes things simpler.
I will show you how to convert fractions to decimals in Excel by changing the cell format from Fraction to General and using formulas.
Method #1: Changing Cell Format from Fraction to General
You can convert fractions to decimals by changing the cell format from Fraction to General.
Suppose you have the following data with fractions from an experiment.
You can change the fractions to decimals using the steps below.
- Select the dataset.
- On the Home tab, open the Number Format drop-down list on the Number group, and select the General option.
The fraction data is converted to decimals as shown below.
Also read: How to Convert Decimal to Fraction in Excel
Method #2: Convert Fractions to Decimals Using Formulas
You can use formulas in Excel to convert fractions to decimals. I will show you two examples.
Example #1: Using VALUE and FIXED Functions
You can use a formula combining the VALUE and FIXED functions to convert fractions to decimals.
The FIXED function rounds a number to a specified number of decimal places (defaulting to 2 if not specified) and returns the result as text. The VALUE function then converts the text result back to a number.
Suppose you have the following data with fractions from an experiment.
You can change the fractions to decimals as shown below.
- Enter the formula below in column B.
=VALUE(FIXED(A2))
Example #2: Using TRIM, SUBSTITUTE, and CHAR Functions
You can use a formula combining TRIM, SUBSTITUTE, and CHAR functions to convert fractions to decimals in Excel.
This is especially helpful when working with fraction data imported from other systems, which often includes extra spaces.
Let’s say you have the experimental data below with fractions.
You can change the fractions to decimals as shown below.
- Enter the formula below in column B.
=0+TRIM(SUBSTITUTE(A2,CHAR(160)," "))
This is how the formula works:
- SUBSTITUTE(A2,CHAR(160),” “) – This segment of the formula replaces all instances of CHAR(160), non-breaking spaces, with normal spaces.
- TRIM(SUBSTITUTE(A2,CHAR(160),” “)) – The TRIM function removes all leading, trailing, and extra spaces from the target data.
- 0+TRIM(SUBSTITUTE(A2,CHAR(160),” “)) – The 0+ forces Excel to convert the target cleaned text data to a decimal number.
I have shown you how to convert fractions to decimals by converting the cell format from Fraction to General and using formulas.
I hope you found the tutorial helpful.
Other Excel articles you may also like:
