39,330 = 9/5
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.
InsertNameDefine Name Evaluate Refers to =EVALUATE($C1)
In D1 enter =Evaluate and copy down.
Then copy column Dplace cursor on C1Paste SpecialValues to fix the data.
Column D can then be deleted
|