39,330 = 9/5

[Peter]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Roger Govier]

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

 

[Peter]

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.

 

[Joel]

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.

 

[Roger Govier]

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

 

[Peter]

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.

 

[joeu2004]

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.

 

[Peter]

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

 

[Roger Govier]

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.

 

[joeu2004]

When I paste the data into my spreadsheet I want it to just convert
and be done with it.

I understand. But you said "[t]here are only about 11 fractions I
need to be concerned with". I would simply dispense with cut-and-
paste in that case. But perhaps you meant to say: you are doing this
often, and there are 11 __different__ fractions each time. (And I
guess you meant to say 14 factions, not 11.)

Note: If you are doing it often, but it is always the same 14
fractions, I would simply set up a spreadsheet with the appropriate
"=fraction" formulas, and cut-and-pasteSpecial-value from there.

I use Excel 2003. If I wanted to cut-and-paste a table from a web
page, I would use either New Web Query or Import Data, both under
Data>Import External Data. If either or both are available in your
version of Excel and you need help using them, post back for
instructions.

To answer your question about VLOOKUP, I would populate the table
manually, and I would use vlookup(...,false) to do the lookup. If
VLOOKUP returns #N/A, that's your clue that you need to update the
table with one or more additional fractions.

Suppose you put your table into Sheet2!A1:B14. Then if A1 (on any
other sheet) contains the first of the fractions cut-and-pasted from a
web age, B1 (on that sheet) might have the formula:

=vlookup(A1, Sheet2!A1:B14, 2, false)

If you want to eliminate the VLOOKUP formulas, you could copy them,
then use Paste Special>Value to overwrite the text in column A.

(I put the lookup table on another sheet simply to get it out of the
way. Of course, you might choose to keep everything on the same
sheet.)

Note: I have had problems with cut-and-paste of text from web pages.
I have had to resort to pasting them into Notepad, saving the file,
then using Data>Import External Data>Import Data to put the desired
text into Excel. Hence my suggestion above to use one of the Import
External Data features.