I copy a page from the Internet into my spread sheet. It's a table
and everything transposes OK, except fractions like 9/5. In my
spreadsheet it reads 39,330. Only the fractions do this. Here are
some others:

9/2 = 4.5 = 39,327
7/2 = 3.5 = 39,265
5/2 = 2.5 = 39,204
9/5 = 1.8 = 39,330
8/5 = 1.6 = 39,299
3/2 = 1.5 =
7/5 = 1.4 =
6/5 = 1.2 =
4/5 = 0.8 =
3/5 = 0.6 =
0 = 0.4 =


I don't have them all yet, but I'm sure there is a mathematical
corelation. As soon as I see the rest of them I'll have them. The
frist column is how they look when I copy them. The second column is
how I want them to look, and the third column is how they show up.

Anyway, when I paste them into my spreadsheet how can I make them show
up as 4.5, 3.5, 2.5, etc?

Thank you

For those fractions where the numbers match your system's local settings for
dates (minus the year), that is, the number in the month's position is
between 1 and 12 and the day's position is between 1 and 28 and 31
(depending on the number of days for the number in the month's position),
Excel thinks you are entering a date (minus the year) and "helps" you out by
converting the entry into a date for the current year. By the way, if the
fraction cannot be interpreted as a date, the entry will be entered as text
(for example, 14/5 will not convert to 2.8). To get your fractions as
numerical values, precede them with an equal sign (making them into a
formula) and you should get the results you are looking for.

Rick


"Peter" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I copy a page from the Internet into my spread sheet. It's a table
> and everything transposes OK, except fractions like 9/5. In my
> spreadsheet it reads 39,330. Only the fractions do this. Here are
> some others:
>
> 9/2 = 4.5 = 39,327
> 7/2 = 3.5 = 39,265
> 5/2 = 2.5 = 39,204
> 9/5 = 1.8 = 39,330
> 8/5 = 1.6 = 39,299
> 3/2 = 1.5 =
> 7/5 = 1.4 =
> 6/5 = 1.2 =
> 4/5 = 0.8 =
> 3/5 = 0.6 =
> 0 = 0.4 =
>
>
> I don't have them all yet, but I'm sure there is a mathematical
> corelation. As soon as I see the rest of them I'll have them. The
> frist column is how they look when I copy them. The second column is
> how I want them to look, and the third column is how they show up.
>
> Anyway, when I paste them into my spreadsheet how can I make them show
> up as 4.5, 3.5, 2.5, etc?
>
> Thank you

Hi

The problem is that Excel is seeing these as dates.
9/2 is 02 Sep and it is showing as the serial number of the date 39327

It would need to be =9/2 for Excel to interpret it in the way that you wish.
If you made the column a Text column, before copying data to it, then it
should come through as '9/2 and display as 9/2.

You can convert this to 4.5 if you do the following.
Suppose the column with the data is column C, and that column D is blank.
Insert>Name>Define> Name Evaluate >Refers to =EVALUATE($C1)

In D1 enter =Evaluate and copy down.
Then copy column D>place cursor on C1>Paste Special>Values to fix the data.
Column D can then be deleted


--

Regards
Roger Govier

"Peter" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I copy a page from the Internet into my spread sheet. It's a table
> and everything transposes OK, except fractions like 9/5. In my
> spreadsheet it reads 39,330. Only the fractions do this. Here are
> some others:
>
> 9/2 = 4.5 = 39,327
> 7/2 = 3.5 = 39,265
> 5/2 = 2.5 = 39,204
> 9/5 = 1.8 = 39,330
> 8/5 = 1.6 = 39,299
> 3/2 = 1.5 =
> 7/5 = 1.4 =
> 6/5 = 1.2 =
> 4/5 = 0.8 =
> 3/5 = 0.6 =
> 0 = 0.4 =
>
>
> I don't have them all yet, but I'm sure there is a mathematical
> corelation. As soon as I see the rest of them I'll have them. The
> frist column is how they look when I copy them. The second column is
> how I want them to look, and the third column is how they show up.
>
> Anyway, when I paste them into my spreadsheet how can I make them show
> up as 4.5, 3.5, 2.5, etc?
>
> Thank you

Hi, and thanks.

I'm using Excel 2000. I didn't see an "evaluate function." I don't
think I quite followed your instructions, or I did follow them and it
didn't work. Either way, I'm a little confused.

There are only about 11 fractions I need to be concerned with. I'm
wondering if I created a table and used Vlookup to convert the long
number string to the fraction I need--would work?

I'm sure there is an easy solution.

I appreciate the advice.

On Sun, 9 Dec 2007 09:19:16 -0000, "Roger Govier"
<roger@technology4unospamdotcodotuk> wrote:

>Hi
>
>The problem is that Excel is seeing these as dates.
>9/2 is 02 Sep and it is showing as the serial number of the date 39327
>
>It would need to be =9/2 for Excel to interpret it in the way that you wish.
>If you made the column a Text column, before copying data to it, then it
>should come through as '9/2 and display as 9/2.
>
>You can convert this to 4.5 if you do the following.
>Suppose the column with the data is column C, and that column D is blank.
>Insert>Name>Define> Name Evaluate >Refers to =EVALUATE($C1)
>
>In D1 enter =Evaluate and copy down.
>Then copy column D>place cursor on C1>Paste Special>Values to fix the data.
>Column D can then be deleted

Here is the full answer. Excel is seening 9/5 as the date 9/5/2007. The
39,000 number is trhe numeric value that excel uses for the date. It is the
count of the number of days that have past since Jan 1, 1900 with Jan 1, 1900
being 1.

The easy solution is to format the column as a fraction before you do the
paste. I think you need to do a pastespecial and paste value only so none of
the formating in the text changes the formating in excel.

"Peter" wrote:

> Hi, and thanks.
>
> I'm using Excel 2000. I didn't see an "evaluate function." I don't
> think I quite followed your instructions, or I did follow them and it
> didn't work. Either way, I'm a little confused.
>
> There are only about 11 fractions I need to be concerned with. I'm
> wondering if I created a table and used Vlookup to convert the long
> number string to the fraction I need--would work?
>
> I'm sure there is an easy solution.
>
> I appreciate the advice.
>
> On Sun, 9 Dec 2007 09:19:16 -0000, "Roger Govier"
> <roger@technology4unospamdotcodotuk> wrote:
>
> >Hi
> >
> >The problem is that Excel is seeing these as dates.
> >9/2 is 02 Sep and it is showing as the serial number of the date 39327
> >
> >It would need to be =9/2 for Excel to interpret it in the way that you wish.
> >If you made the column a Text column, before copying data to it, then it
> >should come through as '9/2 and display as 9/2.
> >
> >You can convert this to 4.5 if you do the following.
> >Suppose the column with the data is column C, and that column D is blank.
> >Insert>Name>Define> Name Evaluate >Refers to =EVALUATE($C1)
> >
> >In D1 enter =Evaluate and copy down.
> >Then copy column D>place cursor on C1>Paste Special>Values to fix the data.
> >Column D can then be deleted
>
>

Hi
You won't see an Evaluation function.
That is why I said to use Insert>Name>Define to create the the function.

It does not matter what name you give the defined name, it is the Refers to
part that is important.

Try again
Insert>Name>Define
Name Evaluate
Refers to =Evaluate($C1)

Change the column reference to whatever column is holding the fractions.
In column D (or anywhere else) type =Evaluate

--

Regards
Roger Govier

"Peter" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi, and thanks.
>
> I'm using Excel 2000. I didn't see an "evaluate function." I don't
> think I quite followed your instructions, or I did follow them and it
> didn't work. Either way, I'm a little confused.
>
> There are only about 11 fractions I need to be concerned with. I'm
> wondering if I created a table and used Vlookup to convert the long
> number string to the fraction I need--would work?
>
> I'm sure there is an easy solution.
>
> I appreciate the advice.
>
> On Sun, 9 Dec 2007 09:19:16 -0000, "Roger Govier"
> <roger@technology4unospamdotcodotuk> wrote:
>
>>Hi
>>
>>The problem is that Excel is seeing these as dates.
>>9/2 is 02 Sep and it is showing as the serial number of the date 39327
>>
>>It would need to be =9/2 for Excel to interpret it in the way that you
>>wish.
>>If you made the column a Text column, before copying data to it, then it
>>should come through as '9/2 and display as 9/2.
>>
>>You can convert this to 4.5 if you do the following.
>>Suppose the column with the data is column C, and that column D is blank.
>>Insert>Name>Define> Name Evaluate >Refers to =EVALUATE($C1)
>>
>>In D1 enter =Evaluate and copy down.
>>Then copy column D>place cursor on C1>Paste Special>Values to fix the
>>data.
>>Column D can then be deleted
>

On Sun, 9 Dec 2007 04:46:00 -0800, Joel
<(E-Mail Removed)> wrote:

>Here is the full answer. Excel is seening 9/5 as the date 9/5/2007. The
>39,000 number is trhe numeric value that excel uses for the date. It is the
>count of the number of days that have past since Jan 1, 1900 with Jan 1, 1900
>being 1.
>
>The easy solution is to format the column as a fraction before you do the
>paste. I think you need to do a pastespecial and paste value only so none of
>the formating in the text changes the formating in excel.


If you made the column a Text column, before copying data to it, then
it
should come through as '9/2 and display as 9/2.

You can convert this to 4.5 if you do the following.
Suppose the column with the data is column C, and that column D is
blank.
Insert>Name>Define> Name Evaluate >Refers to =EVALUATE($C1)

In D1 enter =Evaluate and copy down.
Then copy column D>place cursor on C1>Paste Special>Values to fix the
data.
Column D can then be deleted

I tried both of these solutions over and over to no avail. When I
copy and paste from the web site my only options under "paste special"
are text, unicode text, and html. I use text and paste it into a text
formatted cell. I now manually convert the numbers 39,237, etc. to
their equvalent fractions--it's a pain.

I'm tryng to understand the name define thing, but you're losing me.
I created a new sheet and did exactly as you said, but I couldn't get
it to work.

There's got to be an easy way to solve this. Thanks for all the help.

>
>"Peter" wrote:
>
>> Hi, and thanks.
>>
>> I'm using Excel 2000. I didn't see an "evaluate function." I don't
>> think I quite followed your instructions, or I did follow them and it
>> didn't work. Either way, I'm a little confused.
>>
>> There are only about 11 fractions I need to be concerned with. I'm
>> wondering if I created a table and used Vlookup to convert the long
>> number string to the fraction I need--would work?
>>
>> I'm sure there is an easy solution.
>>
>> I appreciate the advice.
>>
>> On Sun, 9 Dec 2007 09:19:16 -0000, "Roger Govier"
>> <roger@technology4unospamdotcodotuk> wrote:
>>
>> >Hi
>> >
>> >The problem is that Excel is seeing these as dates.
>> >9/2 is 02 Sep and it is showing as the serial number of the date 39327
>> >
>> >It would need to be =9/2 for Excel to interpret it in the way that you wish.
>> >If you made the column a Text column, before copying data to it, then it
>> >should come through as '9/2 and display as 9/2.
>> >
>> >You can convert this to 4.5 if you do the following.
>> >Suppose the column with the data is column C, and that column D is blank.
>> >Insert>Name>Define> Name Evaluate >Refers to =EVALUATE($C1)
>> >
>> >In D1 enter =Evaluate and copy down.
>> >Then copy column D>place cursor on C1>Paste Special>Values to fix the data.
>> >Column D can then be deleted
>>
>>

On Dec 9, 4:06 am, Peter <Peter...@Peterzx7.com> wrote:
> There are only about 11 fractions I need to be concerned with. I'm
> wondering if I created a table and used Vlookup to convert the long
> number string to the fraction I need--would work?

I must be missing something. If there are only 11 fractions, why not
type them in manually, preceding each with the equal sign? That is,
turn them into formulas as follows:

=9/2
=7/2
=5/2
=9/5
....etc...

If you would like a table that shows the fraction in one column and
its decimal value in another column, enter the first column as
follows:

'9/2
'7/2
'5/2
'9/5
....etc...

Note the apostrophe (') in front of each text.

Caveat emptor: If you have =9/5 in A4 and =3/5 in A10, don't expect
3*A10 to equal A4. But ROUND(3*A10,1) does equal A4 in this case.

PS: In your first posting, the last "fraction" is "0". How could
that evaluate to 0.4!? Perhaps you meant to write 2/5.

Hi,

When I paste the data into my spreadsheet I want it to just convert
and be done with it. The way things are now I have to manually change
the fractions that are in error, and sometimes I forget and that
causes a problem.

Here is how the table should look. I'm missing two, but they're not
important.

9/2 = 4.5 = 39,327
7/2 = 3.5 = 39,265
5/2 = 2.5 = 39,204
9/5 = 1.8 = 39,330
8/5 = 1.6 = 39,299
3/2 = 1.5 = 39,143
7/5 = 1.4 = 39,268
6/5 = 1.2 = 39,238
4/5 = 0.8 = 39,177
3/5 = 0.6 = 39,146
1/2 = 0.5 = 38,084
2/5 0.4
1/5 = 0.2 =
1/9 = .11 39,019


On Sun, 16 Dec 2007 07:48:57 -0800 (PST), joeu2004
<(E-Mail Removed)> wrote:

>On Dec 9, 4:06 am, Peter <Peter...@Peterzx7.com> wrote:
>> There are only about 11 fractions I need to be concerned with. I'm
>> wondering if I created a table and used Vlookup to convert the long
>> number string to the fraction I need--would work?
>
>I must be missing something. If there are only 11 fractions, why not
>type them in manually, preceding each with the equal sign? That is,
>turn them into formulas as follows:
>
>=9/2
>=7/2
>=5/2
>=9/5
>...etc...
>
>If you would like a table that shows the fraction in one column and
>its decimal value in another column, enter the first column as
>follows:
>
>'9/2
>'7/2
>'5/2
>'9/5
>...etc...
>
>Note the apostrophe (') in front of each text.
>
>Caveat emptor: If you have =9/5 in A4 and =3/5 in A10, don't expect
>3*A10 to equal A4. But ROUND(3*A10,1) does equal A4 in this case.
>
>PS: In your first posting, the last "fraction" is "0". How could
>that evaluate to 0.4!? Perhaps you meant to write 2/5.

Hi Peter

Mail me a copy of your sheet direct.
Send to
roger at technology4u dot co dot uk
Do the obvious with the dot and at.

--

Regards
Roger Govier

"Peter" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Sun, 9 Dec 2007 04:46:00 -0800, Joel
> <(E-Mail Removed)> wrote:
>
>>Here is the full answer. Excel is seening 9/5 as the date 9/5/2007. The
>>39,000 number is trhe numeric value that excel uses for the date. It is
>>the
>>count of the number of days that have past since Jan 1, 1900 with Jan 1,
>>1900
>>being 1.
>>
>>The easy solution is to format the column as a fraction before you do the
>>paste. I think you need to do a pastespecial and paste value only so none
>>of
>>the formating in the text changes the formating in excel.
>
>
> If you made the column a Text column, before copying data to it, then
> it
> should come through as '9/2 and display as 9/2.
>
> You can convert this to 4.5 if you do the following.
> Suppose the column with the data is column C, and that column D is
> blank.
> Insert>Name>Define> Name Evaluate >Refers to =EVALUATE($C1)
>
> In D1 enter =Evaluate and copy down.
> Then copy column D>place cursor on C1>Paste Special>Values to fix the
> data.
> Column D can then be deleted
>
> I tried both of these solutions over and over to no avail. When I
> copy and paste from the web site my only options under "paste special"
> are text, unicode text, and html. I use text and paste it into a text
> formatted cell. I now manually convert the numbers 39,237, etc. to
> their equvalent fractions--it's a pain.
>
> I'm tryng to understand the name define thing, but you're losing me.
> I created a new sheet and did exactly as you said, but I couldn't get
> it to work.
>
> There's got to be an easy way to solve this. Thanks for all the help.
>
>>
>>"Peter" wrote:
>>
>>> Hi, and thanks.
>>>
>>> I'm using Excel 2000. I didn't see an "evaluate function." I don't
>>> think I quite followed your instructions, or I did follow them and it
>>> didn't work. Either way, I'm a little confused.
>>>
>>> There are only about 11 fractions I need to be concerned with. I'm
>>> wondering if I created a table and used Vlookup to convert the long
>>> number string to the fraction I need--would work?
>>>
>>> I'm sure there is an easy solution.
>>>
>>> I appreciate the advice.
>>>
>>> On Sun, 9 Dec 2007 09:19:16 -0000, "Roger Govier"
>>> <roger@technology4unospamdotcodotuk> wrote:
>>>
>>> >Hi
>>> >
>>> >The problem is that Excel is seeing these as dates.
>>> >9/2 is 02 Sep and it is showing as the serial number of the date 39327
>>> >
>>> >It would need to be =9/2 for Excel to interpret it in the way that you
>>> >wish.
>>> >If you made the column a Text column, before copying data to it, then
>>> >it
>>> >should come through as '9/2 and display as 9/2.
>>> >
>>> >You can convert this to 4.5 if you do the following.
>>> >Suppose the column with the data is column C, and that column D is
>>> >blank.
>>> >Insert>Name>Define> Name Evaluate >Refers to =EVALUATE($C1)
>>> >
>>> >In D1 enter =Evaluate and copy down.
>>> >Then copy column D>place cursor on C1>Paste Special>Values to fix the
>>> >data.
>>> >Column D can then be deleted
>>>
>>>
>