A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

How do I have excel convert fractions to decimals?



 
 
Thread Tools Display Modes
  #1  
Old November 18th 06, 10:50 PM posted to microsoft.public.excel.worksheet.functions
pgail
external usenet poster
 
Posts: 2
Default How do I have excel convert fractions to decimals?

I am trying to convert fractions to decimals
Ads
  #2  
Old November 18th 06, 11:01 PM posted to microsoft.public.excel.worksheet.functions
Ron Coderre
external usenet poster
 
Posts: 2,118
Default How do I have excel convert fractions to decimals?

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

  #3  
Old November 18th 06, 11:10 PM posted to microsoft.public.excel.worksheet.functions
Roger Govier
external usenet poster
 
Posts: 2,886
Default How do I have excel convert fractions to decimals?

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)
--
Regards

Roger Govier


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



  #4  
Old November 18th 06, 11:25 PM posted to microsoft.public.excel.worksheet.functions
pgail
external usenet poster
 
Posts: 2
Default How do I have excel 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

  #5  
Old November 18th 06, 11:37 PM posted to microsoft.public.excel.worksheet.functions
Roger Govier
external usenet poster
 
Posts: 2,886
Default How do I have excel 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



  #6  
Old November 18th 06, 11:44 PM posted to microsoft.public.excel.worksheet.functions
Roger Govier
external usenet poster
 
Posts: 2,886
Default How do I have excel 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



  #7  
Old November 19th 06, 12:54 AM posted to microsoft.public.excel.worksheet.functions
Ron Coderre
external usenet poster
 
Posts: 2,118
Default How do I have excel 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

  #8  
Old November 19th 06, 03:39 AM posted to microsoft.public.excel.worksheet.functions
Epinn
external usenet poster
 
Posts: 605
Default How do I have excel 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 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)
--
Regards

Roger Govier


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




  #9  
Old November 19th 06, 05:55 AM posted to microsoft.public.excel.worksheet.functions
Epinn
external usenet poster
 
Posts: 605
Default How do I have excel 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 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)
--
Regards

Roger Govier


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





  #10  
Old November 19th 06, 06:50 AM posted to microsoft.public.excel.worksheet.functions
Ron Coderre
external usenet poster
 
Posts: 2,118
Default How do I have excel 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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert CSV File Into Formatted Excel Document Andy Excel Discussion (Misc queries) 3 April 28th 06 08:08 PM
WordPad file convert in Excel Tarique Excel Discussion (Misc queries) 1 April 21st 06 02:48 PM
How can I convert an Excel file to an Adobe .pdf file by using Ex. pammoore2000 Excel Discussion (Misc queries) 5 March 15th 05 04:51 AM
how to convert GETPIVOTDATA from excel 2000 to excel 2002... Need_help_on_excel Excel Worksheet Functions 1 March 15th 05 02:08 AM
How do I convert decimal inches to fractions in excel JTZ Excel Discussion (Misc queries) 2 February 7th 05 10:31 PM


All times are GMT +1. The time now is 11:57 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.