G
Guest
I am trying to convert fractions to decimals
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Guest
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
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
R
Roger Govier
Hi
If the cell is formatted as fraction, just Format>Cells>General
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)
If the cell is formatted as fraction, just Format>Cells>General
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)
G
Guest
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.
convert to decimal. I have tried format cells, and convert, neither have
this option available.
R
Roger Govier
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)
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)
R
Roger Govier
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)
=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)
G
Guest
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
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
E
Epinn
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
Hi
If the cell is formatted as fraction, just Format>Cells>General
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)
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
Hi
If the cell is formatted as fraction, just Format>Cells>General
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)
E
Epinn
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
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
Hi
If the cell is formatted as fraction, just Format>Cells>General
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)
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
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
Hi
If the cell is formatted as fraction, just Format>Cells>General
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)
G
Guest
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
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
E
Epinn
Ron,
Thank you for making my Text to Columns idea work better by suggesting formatting the columns to number first. This is fascinating.
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
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
Thank you for making my Text to Columns idea work better by suggesting formatting the columns to number first. This is fascinating.
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
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
R
Roger Govier
Very nice, Ron!
Much easier solution. I hadn't realised the fraction could be coerced to
decimal in this way.
Much easier solution. I hadn't realised the fraction could be coerced to
decimal in this way.
G
Guest
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
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 said:Ron,
Thank you for making my Text to Columns idea work better by suggesting formatting the columns to number first. This is fascinating.
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
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 said: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
R
Ron Coderre
Ummmm.....obvious typo:
Should be:
intend that to be 04-Jan-2006
intend that to be 01-Jan-2006
Should be:
intend that to be 04-Jan-2006
Ron Coderre said: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 said: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
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 said: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
:
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.
:
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
:
I am trying to convert fractions to decimals
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.