numbers to dates

[Guest]

When I paste a set of numbers into EXCEL using the "Export to Microsoft
Excel" function from IE, the numbers are changed to dates. Their original
format is:
4-8
8-18
etc.

They get switched to:
8-Apr
18-Aug

Does anyone know how to turn this autoformatting off? I've tried formatting
the cells back to numbers, text and general but then the numbers get changed
to different numbers. 4-18 to 38085 etc.

Thanks

 

[Fred Smith]

"4-8" is not a number. So Excel must make an assumption as to what it is. It
could be a date, it could be text, or it could be a formula. Unfortunately,
Excel assumes the entry is a date, which is not what you want. You need to
tell Excel what it is prior to entering it in the cell. So format your cells
as text first, then do the paste.

 

[Guest]

I've tried preformatting the cells, but it does the exact same thing.

 

[Guest]

Hi
try pasting the values with 'Edit - Paste Special - Values'

 

[David McRitchie]

Hi _______,

Source: an unspecified web page, copied (ctrl+c) using IE

Excel will attempt to format anything that resembles a date as a date,
which is not too bad for manual entry, since you can format the cells as text
ahead of time.

It may not be clear in each of the following, but format your cells ahead of time
as text.

Manual entry:
You have to format the cells as text *before* you enter data into a cell.

Reading a file:
When you Export data, save the file as .txt instead of .csv then
when you import the file you will be given a choice of your formatting --
choose text for such columns. The "file import wizard" and the
"text to columns" work exactly the same see use of text to columns later.

Cut and Paste: from HTML that was not generated from Excel or MS Office
Don't think you can do anything, even though you format your Excel sheet
as text ahead of time. Excel will attempt to format many numbers with two
or less hyphens as dates.

You might with some data be able to paste to notepad, then from notepad
cut and paste to Excel. It might paste as wanted. OR it might paste all
to one column.

If it pastes all to one column then use Data, Text to columns,
choose delimited,
indicate delimiters and treat multiple spaces as one;
choose which columns are text.
But in my test today it propagated to each cell using the tab character
that was retained in notepad.

Avoid Notepad as a middle step:
If you have to do this often you can convert what you copied to text within the
clipboard using Pure Text http://www.stevemiller.net/puretext/
free to use not to distribute http://www.stevemiller.net and worth every penny.
My options are exactly as shown on the web page.

Feedback::
Maybe I missed something because you did use the word "paste", but
it did not seem like you were pasting anything.

Tell us what happened even if it worked. If it worked, which method.
And if it is a public page, tell us what page you are working with.

My system is Windows 2000, my Excel is Excel 2000.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

 

[David McRitchie]

I have attempted to make this clearer by including this on my
Paste / Pasting. Cut & Paste
Pasting Data from a webpage (#webpage)
http://www.mvps.org/dmcritchie/excel/paste.htm#webpage
along with some test data and what you might see once pasted
into Excel. You probably won't see exactly the same result
since my Regional date setting for short date was set to yyyy-mm-dd

Thought this was more important than what I already had on the page
so put it at the top and modified the title of the page to include
Cut and Paste in the title.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

 

[Guest]

First off I'd like to thank everybody for there help. Much appreciated.

Okay, I've tried to copy and paste using the suggested methods (and using
the Pure Text program), but to no avail. Perhaps I should explain more what
I'm trying to do. Basically I'm trying to import NBA game statistics to
excel using IE's "Export to Microsoft Excel" option. A sample of what I'm
working with can be found here:
http://www.nba.com/games/20041110/PORBOS/boxscore.html

When I tried the copy paste method you discussed the data was imported as
one column. I then used text to columns and got the same changed to date
result.

Thanks again for your time,
Galen

Hi _______,

Source: an unspecified web page, copied (ctrl+c) using IE

Excel will attempt to format anything that resembles a date as a date,
which is not too bad for manual entry, since you can format the cells as text
ahead of time.

It may not be clear in each of the following, but format your cells ahead of time
as text.

Manual entry:
You have to format the cells as text *before* you enter data into a cell.

Reading a file:
When you Export data, save the file as .txt instead of .csv then
when you import the file you will be given a choice of your formatting --
choose text for such columns. The "file import wizard" and the
"text to columns" work exactly the same see use of text to columns later.

Cut and Paste: from HTML that was not generated from Excel or MS Office
Don't think you can do anything, even though you format your Excel sheet
as text ahead of time. Excel will attempt to format many numbers with two
or less hyphens as dates.

You might with some data be able to paste to notepad, then from notepad
cut and paste to Excel. It might paste as wanted. OR it might paste all
to one column.

If it pastes all to one column then use Data, Text to columns,
choose delimited,
indicate delimiters and treat multiple spaces as one;
choose which columns are text.
But in my test today it propagated to each cell using the tab character
that was retained in notepad.

Avoid Notepad as a middle step:
If you have to do this often you can convert what you copied to text within the
clipboard using Pure Text http://www.stevemiller.net/puretext/
free to use not to distribute http://www.stevemiller.net and worth every penny.
My options are exactly as shown on the web page.

Feedback::
Maybe I missed something because you did use the word "paste", but
it did not seem like you were pasting anything.

Tell us what happened even if it worked. If it worked, which method.
And if it is a public page, tell us what page you are working with.

My system is Windows 2000, my Excel is Excel 2000.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

 

[David McRitchie]

Try again, one table at a time from your indicated webpage
http://www.nba.com/games/20041110/PORBOS/boxscore.html

Did you remember to format the worksheet as Text beforehand.

Format the entire worksheet as text
Ctrl+A, then Format, Cells, Text

Select the first table "Trail Blazers"
from the web page and copy with Ctrl+C

Select cell A1 and only cell A1 on the worksheet
Paste with Ctrl+V

If you try to select all the tables at once, it won't work.
Tried this from IE (Internet Explorer) as the browser you indicated,
then retested with Mozilla Firefox 1.0 browser, and though it complained
about the formats being different, even Firefox worked (via Pure Text).

My web page again was
http://www.mvps.org/dmcritchie/excel/paste.htm#webpage

If this does not work for you what version of Excel and what operating system.
Mine is Excel 2000, Windows 2000, Short date format yyyy-mm-dd

 

[David McRitchie]

Hi ____, (email only, please continue in thread)
Would be nicer if you included your name at the bottom of the page
much more friendly.

If you used Text to Columns then you definitely did not chose
Text for each of the columns during the dialog.

Anyway see my posted response about formatting the worksheet
as text beforehand, and of doing only one table at a time.

First off I'd like to thank everybody for there help. Much appreciated.

Okay, I've tried to copy and paste using the suggested methods (and using
the Pure Text program), but to no avail. Perhaps I should explain more what
I'm trying to do. Basically I'm trying to import NBA game statistics to
excel using IE's "Export to Microsoft Excel" option. A sample of what I'm
working with can be found here:
http://www.nba.com/games/20041110/PORBOS/boxscore.html

When I tried the copy paste method you discussed the data was imported as
one column. I then used text to columns and got the same changed to date
result.

Thanks again for your time,
Galen

Hi _______,

Source: an unspecified web page, copied (ctrl+c) using IE

Excel will attempt to format anything that resembles a date as a date,
which is not too bad for manual entry, since you can format the cells as text
ahead of time.

It may not be clear in each of the following, but format your cells ahead of time
as text.

Manual entry:
You have to format the cells as text *before* you enter data into a cell.

Reading a file:
When you Export data, save the file as .txt instead of .csv then
when you import the file you will be given a choice of your formatting --
choose text for such columns. The "file import wizard" and the
"text to columns" work exactly the same see use of text to columns later.

Cut and Paste: from HTML that was not generated from Excel or MS Office
Don't think you can do anything, even though you format your Excel sheet
as text ahead of time. Excel will attempt to format many numbers with two
or less hyphens as dates.

You might with some data be able to paste to notepad, then from notepad
cut and paste to Excel. It might paste as wanted. OR it might paste all
to one column.

If it pastes all to one column then use Data, Text to columns,
choose delimited,
indicate delimiters and treat multiple spaces as one;
choose which columns are text.
But in my test today it propagated to each cell using the tab character
that was retained in notepad.

Avoid Notepad as a middle step:
If you have to do this often you can convert what you copied to text within the
clipboard using Pure Text http://www.stevemiller.net/puretext/
free to use not to distribute http://www.stevemiller.net and worth every penny.
My options are exactly as shown on the web page.

Feedback::
Maybe I missed something because you did use the word "paste", but
it did not seem like you were pasting anything.

Tell us what happened even if it worked. If it worked, which method.
And if it is a public page, tell us what page you are working with.

My system is Windows 2000, my Excel is Excel 2000.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

 

[David McRitchie]

Messed that up and email would not have worked anyway to
one of those anonymous postings anyway.