I am trying to convert fractions to decimals

Sure, I can help you with that! Excel has a built-in feature that allows you to easily convert fractions to decimals. Here's how you can do it:

1. First, enter the fraction you want to convert into a cell in Excel. For example, you might enter
   Code:

   "1/2"

   into cell A1.
2. Next, select the cell that you just entered the fraction into.
3. Now, go to the Home tab in the Excel ribbon and look for the Number group. In this group, you'll see a drop-down menu labeled General. Click on this drop-down menu and select Fraction from the list of options.
4. Once you've selected Fraction, Excel will automatically convert the fraction in the selected cell to a decimal. In our example, "1/2" would be converted to "0.5".

That's it! Excel will automatically convert any fractions you enter into decimals using this method. If you have a large number of fractions you need to convert, you can use this method to quickly convert them all at once by selecting a range of cells and following the same steps.

Can you give examples of how you need to deal with the fractions?
Are you typing them?
Are they in data that was imported?
Are you referring to them in another cell?

What have you tried?
What were you hoping for?
What were the actual results?

***********
Regards,
Ron

XL2002, WinXP


"pgail" wrote:

I am trying to convert fractions to decimals

Hi

If the cell is formatted as fraction, just FormatCellsGeneral
or if you want to retain both, assuming the fraction is in A1 then in
another cell =A1 and format that cell as General.

If the fraction has been entered as text in a cell then
=LEFT(A1,FIND("/",A1)-1)/MID(A1,FIND("/",A1)+1,255)
--
Regards

Roger Govier


"pgail" wrote in message
...
I am trying to convert fractions to decimals

I am typing in measuments of packages - i.e. 16 3/8 X 5 1/8, I want excel to
convert to decimal. I have tried format cells, and convert, neither have
this option available.

"Ron Coderre" wrote:

Can you give examples of how you need to deal with the fractions?
Are you typing them?
Are they in data that was imported?
Are you referring to them in another cell?

What have you tried?
What were you hoping for?
What were the actual results?

***********
Regards,
Ron

XL2002, WinXP


"pgail" wrote:

I am trying to convert fractions to decimals

Having seen your example, I would modify your entry so that you put the
first measurement in A1 and the second in B1 then modify my original
posting to
=MID(A1,FIND(" ",A1)+1,find("/",A1),1)/MID(A1,FIND("/",A1)+1,255)
&" x " &
MID(B1,FIND(" ",B1)+1,find("/",B1),1)/MID(B1,FIND("/",B1)+1,255)

--
Regards

Roger Govier


"pgail" wrote in message
...
I am typing in measuments of packages - i.e. 16 3/8 X 5 1/8, I want
excel to
convert to decimal. I have tried format cells, and convert, neither
have
this option available.

"Ron Coderre" wrote:

Can you give examples of how you need to deal with the fractions?
Are you typing them?
Are they in data that was imported?
Are you referring to them in another cell?

What have you tried?
What were you hoping for?
What were the actual results?

***********
Regards,
Ron

XL2002, WinXP


"pgail" wrote:

I am trying to convert fractions to decimals

Sorry, I missed out the leading integers.

=LEFT(A1,FIND(" ",A1)-1) &"."& MID(A1,FIND(" ",A1)+1,find("/",A1),1)
/MID(A1,FIND("/",A1)+1,255)
&" x " &
LEFT(B1,FIND(" ",B1)-1) &"."&MID(B1,FIND(" ",B1)+1,find("/",B1),1)
/MID(B1,FIND("/",B1)+1,255)


--
Regards

Roger Govier


"pgail" wrote in message
...
I am typing in measuments of packages - i.e. 16 3/8 X 5 1/8, I want
excel to
convert to decimal. I have tried format cells, and convert, neither
have
this option available.

"Ron Coderre" wrote:

Can you give examples of how you need to deal with the fractions?
Are you typing them?
Are they in data that was imported?
Are you referring to them in another cell?

What have you tried?
What were you hoping for?
What were the actual results?

***********
Regards,
Ron

XL2002, WinXP


"pgail" wrote:

I am trying to convert fractions to decimals

Perhaps one of these:

Using
A1: 16 3/8 X 5 1/8

Translated to decimal:
B1: =(--TRIM(LEFT(A1,SEARCH("X",A1)-1)))&" X
"&(--TRIM(MID(A1,SEARCH("X",A1)+1,255)))
returns 16.375 X 5.125

Or...
B1: =--TRIM(LEFT(A1,SEARCH("X",A1)-1))
returns 16.375

B2: =--TRIM(MID(A1,SEARCH("X",A1)+1,255))
returns 5.125

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"pgail" wrote:

I am typing in measuments of packages - i.e. 16 3/8 X 5 1/8, I want excel to
convert to decimal. I have tried format cells, and convert, neither have
this option available.

"Ron Coderre" wrote:

Can you give examples of how you need to deal with the fractions?
Are you typing them?
Are they in data that was imported?
Are you referring to them in another cell?

What have you tried?
What were you hoping for?
What were the actual results?

***********
Regards,
Ron

XL2002, WinXP


"pgail" wrote:

I am trying to convert fractions to decimals

Roger and Ron,

I learn a couple of things. Thanks. I was hoping that Text to Column could do some magic. But no luck.

The best it can do is to split 16 3/8 X 5 1/8 in A1 into

A1: 16 3/8
B1: 5 1/8

After formatting to general

A1: 16.375
B1: 5.125

C1: =A1&" X "&B1 returns 16.375 X 5.125

You know I had this "crazy" idea that there might be some hidden features in Text to Column that would convert 16 3/8 X 5 1/8 to 16.375 X 5.125 in one shot. I guess no other features will do this either. I am over imaginative.

By the way, I really like the fact that the double unary coerces the fraction to decimal.

Thanks for listening.

Epinn

"Roger Govier" wrote in message ...
Hi

If the cell is formatted as fraction, just FormatCellsGeneral
or if you want to retain both, assuming the fraction is in A1 then in
another cell =A1 and format that cell as General.

If the fraction has been entered as text in a cell then
=LEFT(A1,FIND("/",A1)-1)/MID(A1,FIND("/",A1)+1,255)
--
Regards

Roger Govier


"pgail" wrote in message
...
I am trying to convert fractions to decimals

Text to Column only works when there is an integer to the left of the fraction, e.g. 11 1/4.
If it is a fraction on its own, say 1/4, Text to Column will treat it as a date.

Wonder if there is a way to get around this.

Epinn

"Epinn" wrote in message ...
Roger and Ron,

I learn a couple of things. Thanks. I was hoping that Text to Column could do some magic. But no luck.

The best it can do is to split 16 3/8 X 5 1/8 in A1 into

A1: 16 3/8
B1: 5 1/8

After formatting to general

A1: 16.375
B1: 5.125

C1: =A1&" X "&B1 returns 16.375 X 5.125

You know I had this "crazy" idea that there might be some hidden features in Text to Column that would convert 16 3/8 X 5 1/8 to 16.375 X 5.125 in one shot. I guess no other features will do this either. I am over imaginative.

By the way, I really like the fact that the double unary coerces the fraction to decimal.

Thanks for listening.

Epinn

"Roger Govier" wrote in message ...
Hi

If the cell is formatted as fraction, just FormatCellsGeneral
or if you want to retain both, assuming the fraction is in A1 then in
another cell =A1 and format that cell as General.

If the fraction has been entered as text in a cell then
=LEFT(A1,FIND("/",A1)-1)/MID(A1,FIND("/",A1)+1,255)
--
Regards

Roger Govier


"pgail" wrote in message
...
I am trying to convert fractions to decimals

Here's another option

With
A1: 16 3/8 X 5 1/8

Then
1)Format cellls A1 and B1 as Category: Number

2)<data<text-to-columns
Delimited
Check: Other and use: X
Click the [finish] button

A1 becomes 16.375
B1: becomes 5.125
(the type coercions are automatic when the number format is NOT General)

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Perhaps one of these:

Using
A1: 16 3/8 X 5 1/8

Translated to decimal:
B1: =(--TRIM(LEFT(A1,SEARCH("X",A1)-1)))&" X
"&(--TRIM(MID(A1,SEARCH("X",A1)+1,255)))
returns 16.375 X 5.125

Or...
B1: =--TRIM(LEFT(A1,SEARCH("X",A1)-1))
returns 16.375

B2: =--TRIM(MID(A1,SEARCH("X",A1)+1,255))
returns 5.125

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"pgail" wrote:

I am typing in measuments of packages - i.e. 16 3/8 X 5 1/8, I want excel to
convert to decimal. I have tried format cells, and convert, neither have
this option available.

"Ron Coderre" wrote:

Can you give examples of how you need to deal with the fractions?
Are you typing them?
Are they in data that was imported?
Are you referring to them in another cell?

What have you tried?
What were you hoping for?
What were the actual results?

***********
Regards,
Ron

XL2002, WinXP


"pgail" wrote:

I am trying to convert fractions to decimals

Ron,

Thank you for making my Text to Columns idea work better by suggesting formatting the columns to number first. This is fascinating.

(the type coercions are automatic when the number format is NOT General) <<

As indicated in my previous post, Text to Columns won't work with fractions without integers.

A1: 1/4 X 1/4

Text to Columns returns

A1: 38721.00
B1: 1/4

Don't understand why.

Epinn


"Ron Coderre" wrote in message ...
Here's another option

With
A1: 16 3/8 X 5 1/8

Then
1)Format cellls A1 and B1 as Category: Number

2)<data<text-to-columns
Delimited
Check: Other and use: X
Click the [finish] button

A1 becomes 16.375
B1: becomes 5.125
(the type coercions are automatic when the number format is NOT General)

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Perhaps one of these:

Using
A1: 16 3/8 X 5 1/8

Translated to decimal:
B1: =(--TRIM(LEFT(A1,SEARCH("X",A1)-1)))&" X
"&(--TRIM(MID(A1,SEARCH("X",A1)+1,255)))
returns 16.375 X 5.125

Or...
B1: =--TRIM(LEFT(A1,SEARCH("X",A1)-1))
returns 16.375

B2: =--TRIM(MID(A1,SEARCH("X",A1)+1,255))
returns 5.125

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"pgail" wrote:

I am typing in measuments of packages - i.e. 16 3/8 X 5 1/8, I want excel to
convert to decimal. I have tried format cells, and convert, neither have
this option available.

"Ron Coderre" wrote:

Can you give examples of how you need to deal with the fractions?
Are you typing them?
Are they in data that was imported?
Are you referring to them in another cell?

What have you tried?
What were you hoping for?
What were the actual results?

***********
Regards,
Ron

XL2002, WinXP


"pgail" wrote:

I am trying to convert fractions to decimals

Very nice, Ron!

Much easier solution. I hadn't realised the fraction could be coerced to
decimal in this way.

--
Regards

Roger Govier


"Ron Coderre" wrote in message
...
Perhaps one of these:

Using
A1: 16 3/8 X 5 1/8

Translated to decimal:
B1: =(--TRIM(LEFT(A1,SEARCH("X",A1)-1)))&" X
"&(--TRIM(MID(A1,SEARCH("X",A1)+1,255)))
returns 16.375 X 5.125

Or...
B1: =--TRIM(LEFT(A1,SEARCH("X",A1)-1))
returns 16.375

B2: =--TRIM(MID(A1,SEARCH("X",A1)+1,255))
returns 5.125

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"pgail" wrote:

I am typing in measuments of packages - i.e. 16 3/8 X 5 1/8, I want
excel to
convert to decimal. I have tried format cells, and convert, neither
have
this option available.

"Ron Coderre" wrote:

Can you give examples of how you need to deal with the fractions?
Are you typing them?
Are they in data that was imported?
Are you referring to them in another cell?

What have you tried?
What were you hoping for?
What were the actual results?

***********
Regards,
Ron

XL2002, WinXP


"pgail" wrote:

I am trying to convert fractions to decimals

You're right. Excel needs a way to differentiate a date from a fraction.....
a leading number followed by a space suffices.

A1: 0 1/4 X 0 1/4
Perform text-to-columns (with "X" as the delimiter)

Here's the result:
A1: 1/4
B1: 1/4
(both are decimal numbers displayed as fractions)

I assume that the overwhelming majority of user who enter 1/4, intend that
to be 01-Jan-2006 versus 0.25, hence the leading number requirement for
fractions.
***********
Regards,
Ron

XL2002, WinXP


"Epinn" wrote:

Ron,

Thank you for making my Text to Columns idea work better by suggesting formatting the columns to number first. This is fascinating.

(the type coercions are automatic when the number format is NOT General) <<

As indicated in my previous post, Text to Columns won't work with fractions without integers.

A1: 1/4 X 1/4

Text to Columns returns

A1: 38721.00
B1: 1/4

Don't understand why.

Epinn


"Ron Coderre" wrote in message ...
Here's another option

With
A1: 16 3/8 X 5 1/8

Then
1)Format cellls A1 and B1 as Category: Number

2)<data<text-to-columns
Delimited
Check: Other and use: X
Click the [finish] button

A1 becomes 16.375
B1: becomes 5.125
(the type coercions are automatic when the number format is NOT General)

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Perhaps one of these:

Using
A1: 16 3/8 X 5 1/8

Translated to decimal:
B1: =(--TRIM(LEFT(A1,SEARCH("X",A1)-1)))&" X
"&(--TRIM(MID(A1,SEARCH("X",A1)+1,255)))
returns 16.375 X 5.125

Or...
B1: =--TRIM(LEFT(A1,SEARCH("X",A1)-1))
returns 16.375

B2: =--TRIM(MID(A1,SEARCH("X",A1)+1,255))
returns 5.125

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"pgail" wrote:

I am typing in measuments of packages - i.e. 16 3/8 X 5 1/8, I want excel to
convert to decimal. I have tried format cells, and convert, neither have
this option available.

"Ron Coderre" wrote:

Can you give examples of how you need to deal with the fractions?
Are you typing them?
Are they in data that was imported?
Are you referring to them in another cell?

What have you tried?
What were you hoping for?
What were the actual results?

***********
Regards,
Ron

XL2002, WinXP


"pgail" wrote:

I am trying to convert fractions to decimals

Ummmm.....obvious typo:

intend that to be 01-Jan-2006

Should be:
intend that to be 04-Jan-2006



"Ron Coderre" wrote in message
...
You're right. Excel needs a way to differentiate a date from a
fraction.....
a leading number followed by a space suffices.

A1: 0 1/4 X 0 1/4
Perform text-to-columns (with "X" as the delimiter)

Here's the result:
A1: 1/4
B1: 1/4
(both are decimal numbers displayed as fractions)

I assume that the overwhelming majority of user who enter 1/4, intend that
to be 01-Jan-2006 versus 0.25, hence the leading number requirement for
fractions.
***********
Regards,
Ron

XL2002, WinXP


"Epinn" wrote:

Ron,

Thank you for making my Text to Columns idea work better by suggesting
formatting the columns to number first. This is fascinating.

(the type coercions are automatic when the number format is NOT
General) <<

As indicated in my previous post, Text to Columns won't work with
fractions without integers.

A1: 1/4 X 1/4

Text to Columns returns

A1: 38721.00
B1: 1/4

Don't understand why.

Epinn


"Ron Coderre" wrote in message
...
Here's another option

With
A1: 16 3/8 X 5 1/8

Then
1)Format cellls A1 and B1 as Category: Number

2)<data<text-to-columns
Delimited
Check: Other and use: X
Click the [finish] button

A1 becomes 16.375
B1: becomes 5.125
(the type coercions are automatic when the number format is NOT General)

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Perhaps one of these:

Using
A1: 16 3/8 X 5 1/8

Translated to decimal:
B1: =(--TRIM(LEFT(A1,SEARCH("X",A1)-1)))&" X
"&(--TRIM(MID(A1,SEARCH("X",A1)+1,255)))
returns 16.375 X 5.125

Or...
B1: =--TRIM(LEFT(A1,SEARCH("X",A1)-1))
returns 16.375

B2: =--TRIM(MID(A1,SEARCH("X",A1)+1,255))
returns 5.125

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"pgail" wrote:

I am typing in measuments of packages - i.e. 16 3/8 X 5 1/8, I want
excel to
convert to decimal. I have tried format cells, and convert, neither
have
this option available.

"Ron Coderre" wrote:

Can you give examples of how you need to deal with the fractions?
Are you typing them?
Are they in data that was imported?
Are you referring to them in another cell?

What have you tried?
What were you hoping for?
What were the actual results?

***********
Regards,
Ron

XL2002, WinXP


"pgail" wrote:

I am trying to convert fractions to decimals

I have used some formulas I found here to manipulate some fractions but i need to combine them in order to save space
this is for getting decimal values for inches and their fractions
I have read that just changing the format of the cells is enough but that is not working for me, it does not do it for all the cells

here is my structure
--------------------------------------…
G8 | H8 | I8 | J8 | K8 |
--------------------------------------…
27 1/2 | 27 | 1/2 | 0.5 | 27.5 |
--------------------------------------…
Here are the formulas im using
in H8 - =TRIM(LEFT(G8;SEARCH(" ";G8)-1)) gets 27
in I8 - =TRIM(RIGHT(G8;SEARCH(" ";G8)+1)); gets 1/2
in J8 - =LEFT(I8;FIND("/";I8)-1)/MID(I8;FIND("/"… gets 0.5
in K8 - =SUM(H8+J8) gets 27.5 which is what I was after

however I dont know enough excel to combine all of the formulas into one and get the results in H8
so it looks like
--------------------------
G8 | H8 |
--------------------------
27 1/2 | 27.5 |
--------------------------
having H8 contain the combined formula without the other steps as I have other steps for which I need the space
hopefully someone out there knows the answer
Thanks in advance