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

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

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

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

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

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

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

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

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