copy/paste into excel changes numbers into date format

R

Rufus

I need to copy a lot of raw data from html format into
excel.

Most of the data consists of a min/max figure (eg. 31/45)

Excel (2003) always converts this number to date format
no matter what I format the cells as. I have been
unsuccesful in converting back to the same format.

Any help or advice is appreciated.
Thanks
 
G

Guest

Thanks for the reply, however even if I select the cells
and format as text, as soon as I paste the copied
information into excel it still converts into date format.
 
D

Dave Peterson

If you format the cell(s) as text, then instead of just pasting, try:

Edit|Paste special
choose Text

(You should see a different paste special dialog show up, since you're copying
from excel.)

If you're only doing one cell's worth of pasting:
format the cell as text
paste into the formula bar.

Both kept the Text format in minor testing (but I couldn't find HTML stuff that
looked like dates to copy--so it wasn't much of a test.)
 
G

Guest

Thank you for your reply. However, if I choose paste
special -> plain text (or unicode) it pastes about 8
columns worth of info into 1 cell, [which does not lend
itself to be saved as text and re-opened allowing you to
put in cell seperator lines]

an example of the format i need to copy

Item Current stock Min/Max
Item1 10 8/15
Item2 14 10/25

NOTE: IF the number does not make a valid date format
(over 31/31 for example) then it keeps the format intact.
 
D

Dave Peterson

But once the data is in a single cell, you can select that column and
do:

Data|text to columns

Make sure you don't leave it general (or choose date) for those things
that look like dates, but aren't.

Another suggestion may be to paste into notepad. Save as a .txt file.

Then use File|open and get the wizard for parsing your data there.


Thank you for your reply. However, if I choose paste
special -> plain text (or unicode) it pastes about 8
columns worth of info into 1 cell, [which does not lend
itself to be saved as text and re-opened allowing you to
put in cell seperator lines]

an example of the format i need to copy

Item Current stock Min/Max
Item1 10 8/15
Item2 14 10/25

NOTE: IF the number does not make a valid date format
(over 31/31 for example) then it keeps the format intact.
-----Original Message-----
If you format the cell(s) as text, then instead of just pasting, try:

Edit|Paste special
choose Text

(You should see a different paste special dialog show up, since you're copying
from excel.)

If you're only doing one cell's worth of pasting:
format the cell as text
paste into the formula bar.

Both kept the Text format in minor testing (but I couldn't find HTML stuff that
looked like dates to copy--so it wasn't much of a test.)



--

Dave Peterson
[email protected]
.
Click to expand...
Click to expand...
 
Top