Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
stebro
 
Posts: n/a
Default pasting data from a website changes text to date

I did a google usenet search and found several instances similar to this
problem going back to 1999 with no real answers so there may not be, but here
goes.

I'm copying a table from a website and pasting into an Excel 2003
spreadsheet. One of the columns contains "3-0", and when I paste it, as soon
as xl sees this data it stores it as a date. I want it to stay as 3-0.
Here's what I have tried and have learned doesn't work...

* I have preset the cells, columns, and even the entire worksheet in
different attempts to text and to "custom" with "@" - no help. Data still
gets displayed as a date. when I try to change from a date format to a text
format I get the number 36586; "3-0" is lost forever.

* I have seen suggestions to do a paste special using only "values"; no
good. Since the paste is from a website the only paste special options are
"html, unicode text, & text".

* I have even seen attempts at viewing & tweaking the web site's html code,
but it's beyond me what you could do there to make the paste work differently.

Is anyone aware of any other methods to defeat Excel's insistent desire to
cheat me out of my desired cell format?

Thanks,
Steve
  #2   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

Import it through a web query, under advanced uncheck date recognition


Regards,

Peo Sjoblom

"stebro" wrote:

I did a google usenet search and found several instances similar to this
problem going back to 1999 with no real answers so there may not be, but here
goes.

I'm copying a table from a website and pasting into an Excel 2003
spreadsheet. One of the columns contains "3-0", and when I paste it, as soon
as xl sees this data it stores it as a date. I want it to stay as 3-0.
Here's what I have tried and have learned doesn't work...

* I have preset the cells, columns, and even the entire worksheet in
different attempts to text and to "custom" with "@" - no help. Data still
gets displayed as a date. when I try to change from a date format to a text
format I get the number 36586; "3-0" is lost forever.

* I have seen suggestions to do a paste special using only "values"; no
good. Since the paste is from a website the only paste special options are
"html, unicode text, & text".

* I have even seen attempts at viewing & tweaking the web site's html code,
but it's beyond me what you could do there to make the paste work differently.

Is anyone aware of any other methods to defeat Excel's insistent desire to
cheat me out of my desired cell format?

Thanks,
Steve

  #3   Report Post  
stebro
 
Posts: n/a
Default

Wow - that was cool; I've never tried that before, but it still produced the
same results. In fact when I went to the advanced tab the date recognition
box was already null.

"Peo Sjoblom" wrote:

Import it through a web query, under advanced uncheck date recognition


Regards,

Peo Sjoblom

"stebro" wrote:

I did a google usenet search and found several instances similar to this
problem going back to 1999 with no real answers so there may not be, but here
goes.

I'm copying a table from a website and pasting into an Excel 2003
spreadsheet. One of the columns contains "3-0", and when I paste it, as soon
as xl sees this data it stores it as a date. I want it to stay as 3-0.
Here's what I have tried and have learned doesn't work...

* I have preset the cells, columns, and even the entire worksheet in
different attempts to text and to "custom" with "@" - no help. Data still
gets displayed as a date. when I try to change from a date format to a text
format I get the number 36586; "3-0" is lost forever.

* I have seen suggestions to do a paste special using only "values"; no
good. Since the paste is from a website the only paste special options are
"html, unicode text, & text".

* I have even seen attempts at viewing & tweaking the web site's html code,
but it's beyond me what you could do there to make the paste work differently.

Is anyone aware of any other methods to defeat Excel's insistent desire to
cheat me out of my desired cell format?

Thanks,
Steve

  #4   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

I hardly do these things, is this some kind of sport results? Anyway, what
happens if you preformat the excel ranges as text and then use paste special
as text?
Finally you can paste the 3-0 etc into notepad, save it as text file, then
open it though fileopen (view all file types), that will trigger the text
import wizard, select the column with 3-0 etc, click next twice and under
column data format select text
and click finish.
(I know for sure the last method works and I also entered some numbers in a
text program, preformatted excel to text and then used paste special as text
and it worked, the 3-0 stayed 3-0 and not converted to March something)

Regards,

Peo Sjoblom

"stebro" wrote:

Wow - that was cool; I've never tried that before, but it still produced the
same results. In fact when I went to the advanced tab the date recognition
box was already null.

"Peo Sjoblom" wrote:

Import it through a web query, under advanced uncheck date recognition


Regards,

Peo Sjoblom

"stebro" wrote:

I did a google usenet search and found several instances similar to this
problem going back to 1999 with no real answers so there may not be, but here
goes.

I'm copying a table from a website and pasting into an Excel 2003
spreadsheet. One of the columns contains "3-0", and when I paste it, as soon
as xl sees this data it stores it as a date. I want it to stay as 3-0.
Here's what I have tried and have learned doesn't work...

* I have preset the cells, columns, and even the entire worksheet in
different attempts to text and to "custom" with "@" - no help. Data still
gets displayed as a date. when I try to change from a date format to a text
format I get the number 36586; "3-0" is lost forever.

* I have seen suggestions to do a paste special using only "values"; no
good. Since the paste is from a website the only paste special options are
"html, unicode text, & text".

* I have even seen attempts at viewing & tweaking the web site's html code,
but it's beyond me what you could do there to make the paste work differently.

Is anyone aware of any other methods to defeat Excel's insistent desire to
cheat me out of my desired cell format?

Thanks,
Steve

  #5   Report Post  
stebro
 
Posts: n/a
Default

So embarrased... I tried your web query import again and it worked; I
needed to CHECK the box, not uncheck it. That solved the problem.

Your other suggestions below solved the "3-0" issue, but is dumped all of
the data into column A (columns became indistinguishable).

Anyway, you solved what no one else could in the 5+years of google group
posts I had to wade thru. Thanks and Congrats - you know your stuff!

Steve


--------

"Peo Sjoblom" wrote:

I hardly do these things, is this some kind of sport results? Anyway, what
happens if you preformat the excel ranges as text and then use paste special
as text?
Finally you can paste the 3-0 etc into notepad, save it as text file, then
open it though fileopen (view all file types), that will trigger the text
import wizard, select the column with 3-0 etc, click next twice and under
column data format select text
and click finish.
(I know for sure the last method works and I also entered some numbers in a
text program, preformatted excel to text and then used paste special as text
and it worked, the 3-0 stayed 3-0 and not converted to March something)

Regards,

Peo Sjoblom

"stebro" wrote:

Wow - that was cool; I've never tried that before, but it still produced the
same results. In fact when I went to the advanced tab the date recognition
box was already null.

"Peo Sjoblom" wrote:

Import it through a web query, under advanced uncheck date recognition


Regards,

Peo Sjoblom

"stebro" wrote:

I did a google usenet search and found several instances similar to this
problem going back to 1999 with no real answers so there may not be, but here
goes.

I'm copying a table from a website and pasting into an Excel 2003
spreadsheet. One of the columns contains "3-0", and when I paste it, as soon
as xl sees this data it stores it as a date. I want it to stay as 3-0.
Here's what I have tried and have learned doesn't work...

* I have preset the cells, columns, and even the entire worksheet in
different attempts to text and to "custom" with "@" - no help. Data still
gets displayed as a date. when I try to change from a date format to a text
format I get the number 36586; "3-0" is lost forever.

* I have seen suggestions to do a paste special using only "values"; no
good. Since the paste is from a website the only paste special options are
"html, unicode text, & text".

* I have even seen attempts at viewing & tweaking the web site's html code,
but it's beyond me what you could do there to make the paste work differently.

Is anyone aware of any other methods to defeat Excel's insistent desire to
cheat me out of my desired cell format?

Thanks,
Steve



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 102
Default pasting data from a website changes text to date

stebro

find "option to prevent Excel changing 1-2-3 to a date" and agree with the
suggestion.

--
Vince


"stebro" wrote:

I did a google usenet search and found several instances similar to this
problem going back to 1999 with no real answers so there may not be, but here
goes.

I'm copying a table from a website and pasting into an Excel 2003
spreadsheet. One of the columns contains "3-0", and when I paste it, as soon
as xl sees this data it stores it as a date. I want it to stay as 3-0.
Here's what I have tried and have learned doesn't work...

* I have preset the cells, columns, and even the entire worksheet in
different attempts to text and to "custom" with "@" - no help. Data still
gets displayed as a date. when I try to change from a date format to a text
format I get the number 36586; "3-0" is lost forever.

* I have seen suggestions to do a paste special using only "values"; no
good. Since the paste is from a website the only paste special options are
"html, unicode text, & text".

* I have even seen attempts at viewing & tweaking the web site's html code,
but it's beyond me what you could do there to make the paste work differently.

Is anyone aware of any other methods to defeat Excel's insistent desire to
cheat me out of my desired cell format?

Thanks,
Steve

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default pasting data from a website changes text to date


Hi Vince

Did you try?
To stop Excel converting text to dates type in a single quote before
the text
as you enter it.

example in cell a1 type '3-1 hit enter

Thanks
Denis


--
jetted
------------------------------------------------------------------------
jetted's Profile: http://www.excelforum.com/member.php...o&userid=17532
View this thread: http://www.excelforum.com/showthread...hreadid=560989

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default pasting data from a website changes text to date


Another solution:
Format the cell(s) as Text prior to entering data into them and
Excel will leave them alone.

Thanks
Denis


--
jetted
------------------------------------------------------------------------
jetted's Profile: http://www.excelforum.com/member.php...o&userid=17532
View this thread: http://www.excelforum.com/showthread...hreadid=560989

  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default pasting data from a website changes text to date

I had the same issue but sorted it today.
rather than cut and paste the web data, I created a web query, and there is
an option that works.

from excel
dataimport external datanew web query

there is an button marked options in the top right of the dialogue window
that provides an option to disable date recognition.
The web query thing is good too.

wahoo!


S.

"jetted" wrote:


Another solution:
Format the cell(s) as Text prior to entering data into them and
Excel will leave them alone.

Thanks
Denis


--
jetted
------------------------------------------------------------------------
jetted's Profile: http://www.excelforum.com/member.php...o&userid=17532
View this thread: http://www.excelforum.com/showthread...hreadid=560989


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with data not getting plotted Scott Ehrlich Charts and Charting in Excel 1 January 23rd 05 06:15 PM
Split combined date time data Mark Ada Excel Discussion (Misc queries) 4 December 1st 04 07:47 PM
Split combined date time data Mark Ada Excel Discussion (Misc queries) 1 December 1st 04 06:55 AM
Split combined date time data Mark Ada Excel Discussion (Misc queries) 1 December 1st 04 04:52 AM
VBA Import of text file & Array parsing of that data Dennis Excel Discussion (Misc queries) 4 November 28th 04 11:20 PM


All times are GMT +1. The time now is 10:07 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"