--
Vince

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Hi Vince,

Just precede your input with an apostrophe '
i.e. '1-2-3

The apostrophe forces your input to text format.
You can also preformat the cell(s) as text.

HTH
Martin

Hi Martin

Thank you for your reply.

In making my request to Microsoft, I gave details of why the change was
necessary. Unfortunately Microsoft did not provide any background details
to my request so here they a-

I am copying and pasting about eight fields of data at a time. Two of the
eight fields take the form of either a blank or two, three or four numbers
separated by - e.g. 3-4 or 10-7-20 or 15-1-24-2. The blank and the last
form are no problem but Excel changes the one dash and two dash forms to
dates. I can't include an apostrophe because I cant change the data that I
am copying.

I have searched Excel in vain for everything automatic in the hope of
turning this feature off. I have tried every type of preformat and paste.
I have read just about every post on this discussion group and it has taken
many hours. There are other posters with similar problems. Heather can't
change her input data. Caen(?) is copying and pasting like me. A reply to
his post says this Excel feature can't be turned off.

One post suggests setting up of a text file to be read into Excel including
a dash, - , as a delimiter. I have set up a text file using Notepad and
read it into Excel including - as a delimiter. This works but because there
are 0 to 3 dashes in a text record entry, there are 1 to 4 columns in the
corresponding Excel record. This is a random effect and the resulting Excel
spreadsheet is a mess of overlapping columns.

The answer is quite simple. Excel should accept any sequence of characters
as typed or pasted unless requested to do otherwise. It is very simple for
a user to change 1-2-3 to a date but it is impossible to change that date to
1-2-3. All of the Excel features are intended to be helpful but it should
be possible to turn them on or turn them off.

I have tried everything that I can think of. If anyone has more ideas, I
will try them. Meanwhile I think this is a programming problem in Excel and
needs a programming solution.

Kind regards

Vince

P.S. This is my fourth attempt to post. I keep getting error messages
and lose posts that take ages to write. After the first failed attempt, I
started in writing in Word so that I can keep copying and attempting to post
until successful. I find this discussion group system very difficult to use
compared to other discussion groups.

--
Vince


"MartinW" wrote:

Hi Vince,

Just precede your input with an apostrophe '
i.e. '1-2-3

The apostrophe forces your input to text format.
You can also preformat the cell(s) as text.

HTH
Martin

Vince

Where are you pasting the data from?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Vince" wrote in message
...
Hi Martin

Thank you for your reply.

In making my request to Microsoft, I gave details of why the change was
necessary. Unfortunately Microsoft did not provide any background
details
to my request so here they a-

I am copying and pasting about eight fields of data at a time. Two of
the
eight fields take the form of either a blank or two, three or four numbers
separated by - e.g. 3-4 or 10-7-20 or 15-1-24-2. The blank and the last
form are no problem but Excel changes the one dash and two dash forms to
dates. I can't include an apostrophe because I can't change the data
that I
am copying.

I have searched Excel in vain for everything automatic in the hope of
turning this feature off. I have tried every type of preformat and
paste.
I have read just about every post on this discussion group and it has
taken
many hours. There are other posters with similar problems. Heather
can't
change her input data. Caen(?) is copying and pasting like me. A reply
to
his post says this Excel feature can't be turned off.

One post suggests setting up of a text file to be read into Excel
including
a dash, - , as a delimiter. I have set up a text file using Notepad and
read it into Excel including - as a delimiter. This works but because
there
are 0 to 3 dashes in a text record entry, there are 1 to 4 columns in the
corresponding Excel record. This is a random effect and the resulting
Excel
spreadsheet is a mess of overlapping columns.

The answer is quite simple. Excel should accept any sequence of
characters
as typed or pasted unless requested to do otherwise. It is very simple
for
a user to change 1-2-3 to a date but it is impossible to change that date
to
1-2-3. All of the Excel features are intended to be helpful but it
should
be possible to turn them on or turn them off.

I have tried everything that I can think of. If anyone has more ideas, I
will try them. Meanwhile I think this is a programming problem in Excel
and
needs a programming solution.

Kind regards

Vince

P.S. This is my fourth attempt to post. I keep getting error messages
and lose posts that take ages to write. After the first failed attempt,
I
started in writing in Word so that I can keep copying and attempting to
post
until successful. I find this discussion group system very difficult to
use
compared to other discussion groups.

--
Vince


"MartinW" wrote:

Hi Vince,

Just precede your input with an apostrophe '
i.e. '1-2-3

The apostrophe forces your input to text format.
You can also preformat the cell(s) as text.

HTH
Martin

Nick

http://www.tabonline.com.au/cgi-bin/main.pl?
Now the cat is out of the bag everyone will start a spreadsheet to beat the
racing system, lol.
--
Vince


"Nick Hodge" wrote:

Vince

Where are you pasting the data from?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Vince" wrote in message
...
Hi Martin

Thank you for your reply.

In making my request to Microsoft, I gave details of why the change was
necessary. Unfortunately Microsoft did not provide any background
details
to my request so here they a-

I am copying and pasting about eight fields of data at a time. Two of
the
eight fields take the form of either a blank or two, three or four numbers
separated by - e.g. 3-4 or 10-7-20 or 15-1-24-2. The blank and the last
form are no problem but Excel changes the one dash and two dash forms to
dates. I can't include an apostrophe because I can't change the data
that I
am copying.

I have searched Excel in vain for everything automatic in the hope of
turning this feature off. I have tried every type of preformat and
paste.
I have read just about every post on this discussion group and it has
taken
many hours. There are other posters with similar problems. Heather
can't
change her input data. Caen(?) is copying and pasting like me. A reply
to
his post says this Excel feature can't be turned off.

One post suggests setting up of a text file to be read into Excel
including
a dash, - , as a delimiter. I have set up a text file using Notepad and
read it into Excel including - as a delimiter. This works but because
there
are 0 to 3 dashes in a text record entry, there are 1 to 4 columns in the
corresponding Excel record. This is a random effect and the resulting
Excel
spreadsheet is a mess of overlapping columns.

The answer is quite simple. Excel should accept any sequence of
characters
as typed or pasted unless requested to do otherwise. It is very simple
for
a user to change 1-2-3 to a date but it is impossible to change that date
to
1-2-3. All of the Excel features are intended to be helpful but it
should
be possible to turn them on or turn them off.

I have tried everything that I can think of. If anyone has more ideas, I
will try them. Meanwhile I think this is a programming problem in Excel
and
needs a programming solution.

Kind regards

Vince

P.S. This is my fourth attempt to post. I keep getting error messages
and lose posts that take ages to write. After the first failed attempt,
I
started in writing in Word so that I can keep copying and attempting to
post
until successful. I find this discussion group system very difficult to
use
compared to other discussion groups.

--
Vince


"MartinW" wrote:

Hi Vince,

Just precede your input with an apostrophe '
i.e. '1-2-3

The apostrophe forces your input to text format.
You can also preformat the cell(s) as text.

HTH
Martin

Vince

These have commas for me? Am I missing something?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Vince" wrote in message
...
Nick

http://www.tabonline.com.au/cgi-bin/main.pl?
Now the cat is out of the bag everyone will start a spreadsheet to beat
the
racing system, lol.
--
Vince


"Nick Hodge" wrote:

Vince

Where are you pasting the data from?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Vince" wrote in message
...
Hi Martin

Thank you for your reply.

In making my request to Microsoft, I gave details of why the change was
necessary. Unfortunately Microsoft did not provide any background
details
to my request so here they a-

I am copying and pasting about eight fields of data at a time. Two of
the
eight fields take the form of either a blank or two, three or four
numbers
separated by - e.g. 3-4 or 10-7-20 or 15-1-24-2. The blank and the
last
form are no problem but Excel changes the one dash and two dash forms
to
dates. I can't include an apostrophe because I can't change the data
that I
am copying.

I have searched Excel in vain for everything automatic in the hope of
turning this feature off. I have tried every type of preformat and
paste.
I have read just about every post on this discussion group and it has
taken
many hours. There are other posters with similar problems. Heather
can't
change her input data. Caen(?) is copying and pasting like me. A
reply
to
his post says this Excel feature can't be turned off.

One post suggests setting up of a text file to be read into Excel
including
a dash, - , as a delimiter. I have set up a text file using Notepad
and
read it into Excel including - as a delimiter. This works but because
there
are 0 to 3 dashes in a text record entry, there are 1 to 4 columns in
the
corresponding Excel record. This is a random effect and the resulting
Excel
spreadsheet is a mess of overlapping columns.

The answer is quite simple. Excel should accept any sequence of
characters
as typed or pasted unless requested to do otherwise. It is very
simple
for
a user to change 1-2-3 to a date but it is impossible to change that
date
to
1-2-3. All of the Excel features are intended to be helpful but it
should
be possible to turn them on or turn them off.

I have tried everything that I can think of. If anyone has more
ideas, I
will try them. Meanwhile I think this is a programming problem in
Excel
and
needs a programming solution.

Kind regards

Vince

P.S. This is my fourth attempt to post. I keep getting error
messages
and lose posts that take ages to write. After the first failed
attempt,
I
started in writing in Word so that I can keep copying and attempting to
post
until successful. I find this discussion group system very difficult
to
use
compared to other discussion groups.

--
Vince


"MartinW" wrote:

Hi Vince,

Just precede your input with an apostrophe '
i.e. '1-2-3

The apostrophe forces your input to text format.
You can also preformat the cell(s) as text.

HTH
Martin

Vince

The numbers are comma-delimited when I go to the site.

Have you tried DataImport External DataNew Web Query?

Select via the arrows, the tables you want to import. In Properties, checkmark
"preserve cell formatting" and OK.


Gord Dibben MS Excel MVP

On Sun, 9 Jul 2006 01:41:01 -0700, Vince
wrote:

http://www.tabonline.com.au/cgi-bin/main.pl?
Now the cat is out of the bag everyone will start a spreadsheet to beat the
racing system, lol.
--
Vince

Gord

That worked for me too. I am guessing Vince must be east of me as he didn't
com back

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Vince

The numbers are comma-delimited when I go to the site.

Have you tried DataImport External DataNew Web Query?

Select via the arrows, the tables you want to import. In Properties,
checkmark
"preserve cell formatting" and OK.


Gord Dibben MS Excel MVP

On Sun, 9 Jul 2006 01:41:01 -0700, Vince
wrote:

http://www.tabonline.com.au/cgi-bin/main.pl?
Now the cat is out of the bag everyone will start a spreadsheet to beat
the
racing system, lol.
--
Vince

Gordon

To get the data that I copy and paste

go to the site
Click on Racing
Click on results & search
Pick a date eg 8 jul 2006 Racing click on RESULTS
Click on a race eg EAGLE FARM BR1
one line of data data includes 9-2-4 10-7-2
Click on EAGLE FARM BR6
data includes 8-10-13-5 8-10-13-11
Click on GOLD COAST QR1
data includes 10-1-3 "blank"
Sometimes the data is limited to 10-1

I will check out your suggestion later today. The Tennis is just finished
and I must go to bed. just after 2am in Oz.

kind regards

--
Vince


"Vince" wrote:


--
Vince

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Vince

Having looked at it, I would lobby the website to change from frames...It's
ugly!

Gord's answer won't work as the frame you want to extract data from is not
even recognised by Excel, I guess as it's buried in many others

I think your best bet is to put this code in your personal.xls and select
the cells and it will change them to the correct format.

Excel has it's failings, but you can get around the 'sniffing' of data types
in many ways, just not when it's buried in ugly frames.

Here is the code that should work (Select the cell first)

Sub changeDateToOdds()
Dim first As String
Dim second As String
Dim third As String
first = Day(ActiveCell.Text)
second = Month(ActiveCell.Value)
third = Val(Right(Year(ActiveCell.Value), 2))
ActiveCell.NumberFormat = "@"
ActiveCell.Value = CStr(first & "-" & second & "-" & third)
End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Vince" wrote in message
...
Gordon

To get the data that I copy and paste

go to the site
Click on Racing
Click on results & search
Pick a date eg 8 jul 2006 Racing click on RESULTS
Click on a race eg EAGLE FARM BR1
one line of data data includes 9-2-4 10-7-2
Click on EAGLE FARM BR6
data includes 8-10-13-5 8-10-13-11
Click on GOLD COAST QR1
data includes 10-1-3 "blank"
Sometimes the data is limited to 10-1

I will check out your suggestion later today. The Tennis is just
finished
and I must go to bed. just after 2am in Oz.

kind regards

--
Vince


"Vince" wrote:


--
Vince

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the
"I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and
then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Vince

When you have selected the table you want, click on Options at top right of
Address bar and "Disable date recognition".

Although, I don't get any arrows to select a table when I drill down using your
instructions.


Gord Dibben MS Excel MVP


On Sun, 09 Jul 2006 08:59:59 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Vince

The numbers are comma-delimited when I go to the site.

Have you tried DataImport External DataNew Web Query?

Select via the arrows, the tables you want to import. In Properties, checkmark
"preserve cell formatting" and OK.


Gord Dibben MS Excel MVP

On Sun, 9 Jul 2006 01:41:01 -0700, Vince
wrote:

http://www.tabonline.com.au/cgi-bin/main.pl?
Now the cat is out of the bag everyone will start a spreadsheet to beat the
racing system, lol.
--
Vince

Nick and Gord

Thankyou for your help.
After copying and pasting many times, I have columns of data some of which
is in the correct format 1-2-3-4 (from 1-2-3-4 type pastes), some is in date
format 1-Feb (from 1-2 type pastes) and some is in date format 1/02/2003
(from 1-2-3 type pastes). The code suggested by Nick would presumably work
on some of these (1/02/2003) but what would happen to the others? Just to
confuse the issue the data is sometimes preceeded by an asterisk, *1-2,
*1-2-3, *1-2-3-4. I have no probem correcting this to another column
because this data is pasted unchanged.

Turning off the Excel feature seems to me to be a much better approach if
that is possible. Alternatively I could first make a text file and edit it
to include an apostrophe in all cases and then read it into Excel. This is
a lot of work.

Surely Excel should be designed with an option to accept any character
string without change.

Kind regards

Vince


The page is currently unavailable
Due to current high demand, the page you are looking for cannot be delivered
right now.
________________________________________
Please click the Refresh button, or try again later.

HTTP Error 408 / 409 - Not acceptable / Resource conflict
Internet Explorer

Fortunately I copied it to Word first
Second attempt at posting

--
Vince


"Nick Hodge" wrote:

Vince

Having looked at it, I would lobby the website to change from frames...It's
ugly!

Gord's answer won't work as the frame you want to extract data from is not
even recognised by Excel, I guess as it's buried in many others

I think your best bet is to put this code in your personal.xls and select
the cells and it will change them to the correct format.

Excel has it's failings, but you can get around the 'sniffing' of data types
in many ways, just not when it's buried in ugly frames.

Here is the code that should work (Select the cell first)

Sub changeDateToOdds()
Dim first As String
Dim second As String
Dim third As String
first = Day(ActiveCell.Text)
second = Month(ActiveCell.Value)
third = Val(Right(Year(ActiveCell.Value), 2))
ActiveCell.NumberFormat = "@"
ActiveCell.Value = CStr(first & "-" & second & "-" & third)
End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Vince" wrote in message
...
Gordon

To get the data that I copy and paste

go to the site
Click on Racing
Click on results & search
Pick a date eg 8 jul 2006 Racing click on RESULTS
Click on a race eg EAGLE FARM BR1
one line of data data includes 9-2-4 10-7-2
Click on EAGLE FARM BR6
data includes 8-10-13-5 8-10-13-11
Click on GOLD COAST QR1
data includes 10-1-3 "blank"
Sometimes the data is limited to 10-1

I will check out your suggestion later today. The Tennis is just
finished
and I must go to bed. just after 2am in Oz.

kind regards

--
Vince


"Vince" wrote:


--
Vince

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the
"I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and
then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

I agree that there should be an option to turn off the automatic Exel date
conversion.

Vince" wrote:

Nick and Gord

Thankyou for your help.
After copying and pasting many times, I have columns of data some of which
is in the correct format 1-2-3-4 (from 1-2-3-4 type pastes), some is in date
format 1-Feb (from 1-2 type pastes) and some is in date format 1/02/2003
(from 1-2-3 type pastes). The code suggested by Nick would presumably work
on some of these (1/02/2003) but what would happen to the others? Just to
confuse the issue the data is sometimes preceeded by an asterisk, *1-2,
*1-2-3, *1-2-3-4. I have no probem correcting this to another column
because this data is pasted unchanged.

Turning off the Excel feature seems to me to be a much better approach if
that is possible. Alternatively I could first make a text file and edit it
to include an apostrophe in all cases and then read it into Excel. This is
a lot of work.

Surely Excel should be designed with an option to accept any character
string without change.

Kind regards

Vince


The page is currently unavailable
Due to current high demand, the page you are looking for cannot be delivered
right now.
________________________________________
Please click the Refresh button, or try again later.

HTTP Error 408 / 409 - Not acceptable / Resource conflict
Internet Explorer

Fortunately I copied it to Word first
Second attempt at posting

--
Vince


"Nick Hodge" wrote:

Vince

Having looked at it, I would lobby the website to change from frames...It's
ugly!

Gord's answer won't work as the frame you want to extract data from is not
even recognised by Excel, I guess as it's buried in many others

I think your best bet is to put this code in your personal.xls and select
the cells and it will change them to the correct format.

Excel has it's failings, but you can get around the 'sniffing' of data types
in many ways, just not when it's buried in ugly frames.

Here is the code that should work (Select the cell first)

Sub changeDateToOdds()
Dim first As String
Dim second As String
Dim third As String
first = Day(ActiveCell.Text)
second = Month(ActiveCell.Value)
third = Val(Right(Year(ActiveCell.Value), 2))
ActiveCell.NumberFormat = "@"
ActiveCell.Value = CStr(first & "-" & second & "-" & third)
End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Vince" wrote in message
...
Gordon

To get the data that I copy and paste

go to the site
Click on Racing
Click on results & search
Pick a date eg 8 jul 2006 Racing click on RESULTS
Click on a race eg EAGLE FARM BR1
one line of data data includes 9-2-4 10-7-2
Click on EAGLE FARM BR6
data includes 8-10-13-5 8-10-13-11
Click on GOLD COAST QR1
data includes 10-1-3 "blank"
Sometimes the data is limited to 10-1

I will check out your suggestion later today. The Tennis is just
finished
and I must go to bed. just after 2am in Oz.

kind regards

--
Vince


"Vince" wrote:


--
Vince

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the
"I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and
then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Vince

My code should work with any 'date' irrespective of format and of course, it
only works on the activecell...try it

Excel does have many ways of stopping data sniffing, pre-format, apostrophe,
importing text file, text to columns, etc. It's just none of them suit this
particular application

Certainly there is no 'switch' in Excel and won't be in the next version
(2007)...maybe in the future

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Vince" wrote in message
...
Nick and Gord

Thankyou for your help.
After copying and pasting many times, I have columns of data some of which
is in the correct format 1-2-3-4 (from 1-2-3-4 type pastes), some is in
date
format 1-Feb (from 1-2 type pastes) and some is in date format 1/02/2003
(from 1-2-3 type pastes). The code suggested by Nick would presumably
work
on some of these (1/02/2003) but what would happen to the others? Just
to
confuse the issue the data is sometimes preceeded by an asterisk, *1-2,
*1-2-3, *1-2-3-4. I have no probem correcting this to another column
because this data is pasted unchanged.

Turning off the Excel feature seems to me to be a much better approach if
that is possible. Alternatively I could first make a text file and edit
it
to include an apostrophe in all cases and then read it into Excel. This
is
a lot of work.

Surely Excel should be designed with an option to accept any character
string without change.

Kind regards

Vince


The page is currently unavailable
Due to current high demand, the page you are looking for cannot be
delivered
right now.
________________________________________
Please click the Refresh button, or try again later.

HTTP Error 408 / 409 - Not acceptable / Resource conflict
Internet Explorer

Fortunately I copied it to Word first
Second attempt at posting

--
Vince


"Nick Hodge" wrote:

Vince

Having looked at it, I would lobby the website to change from
frames...It's
ugly!

Gord's answer won't work as the frame you want to extract data from is
not
even recognised by Excel, I guess as it's buried in many others

I think your best bet is to put this code in your personal.xls and select
the cells and it will change them to the correct format.

Excel has it's failings, but you can get around the 'sniffing' of data
types
in many ways, just not when it's buried in ugly frames.

Here is the code that should work (Select the cell first)

Sub changeDateToOdds()
Dim first As String
Dim second As String
Dim third As String
first = Day(ActiveCell.Text)
second = Month(ActiveCell.Value)
third = Val(Right(Year(ActiveCell.Value), 2))
ActiveCell.NumberFormat = "@"
ActiveCell.Value = CStr(first & "-" & second & "-" & third)
End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Vince" wrote in message
...
Gordon

To get the data that I copy and paste

go to the site
Click on Racing
Click on results & search
Pick a date eg 8 jul 2006 Racing click on RESULTS
Click on a race eg EAGLE FARM BR1
one line of data data includes 9-2-4 10-7-2
Click on EAGLE FARM BR6
data includes 8-10-13-5 8-10-13-11
Click on GOLD COAST QR1
data includes 10-1-3 "blank"
Sometimes the data is limited to 10-1

I will check out your suggestion later today. The Tennis is just
finished
and I must go to bed. just after 2am in Oz.

kind regards

--
Vince


"Vince" wrote:


--
Vince

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click
the
"I
Agree" button in the message pane. If you do not see the button,
follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and
then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Hi again Vince,

A pretty ugly workaround but if you paste into word first and do a
find and replace the hyphen with a dot or maybe a space, then
paste into Excel.

A bit slow but you may be able to add a lot of the selections
to word first and then do the find and replace and copy to Excel
in batches.

Just a thought
Martin

Nick, Gord and Martin

Thank you all for your replies. You have been extremely helpful in getting
my grey matter working.

Nick, I will try your code but I presume it means finding the appropriate
cells, activating them and applying the code. Is there a search method
for finding all entries of type 01/02/2003? Is there a "seach and replace"
method in Excel of using these subs as replacement? If so, I could paste
direct to Excel and then search for the offending entries and replace them
with a sub. I may need to do it twice to replace entries like 1 Feb.
Since I process the data further, I might write slightly different codes
using your template. It all depends on being able to automate the
replacement process in Excel.

Martin, your suggestion of search and replace to change the dash to another
character in a text file might be the answer. It means making a text file
first but automates the changes necessary to overcome the Excel problem. I
process the data futher so another character in place of the dash is no
problem. Replacing the dash with a space (a stroke of genius) would save
some of the reprocessing but I would have to devise a method of always having
4 columns. I will think about this. Maybe I will have to make 2 or more
text files by being selective with the copy and paste operation.

I hope Excel makes the change eventually. It wouldn't surprise me if an
earlier version of Excel (or some other spreadsheet program) did not have
this unfortunate operation.

Kind regards and thanks again.

--
Vince


"MartinW" wrote:

Hi again Vince,

A pretty ugly workaround but if you paste into word first and do a
find and replace the hyphen with a dot or maybe a space, then
paste into Excel.

A bit slow but you may be able to add a lot of the selections
to word first and then do the find and replace and copy to Excel
in batches.

Just a thought
Martin

It is a rediculous state of affaires when Excel does not allow a simple copy
and paste without screwing it up. Excel dates are very useful in some cases
and in others they are a pain.

"Vince" wrote:

Nick, Gord and Martin

Thank you all for your replies. You have been extremely helpful in getting
my grey matter working.

Nick, I will try your code but I presume it means finding the appropriate
cells, activating them and applying the code. Is there a search method
for finding all entries of type 01/02/2003? Is there a "seach and replace"
method in Excel of using these subs as replacement? If so, I could paste
direct to Excel and then search for the offending entries and replace them
with a sub. I may need to do it twice to replace entries like 1 Feb.
Since I process the data further, I might write slightly different codes
using your template. It all depends on being able to automate the
replacement process in Excel.

Martin, your suggestion of search and replace to change the dash to another
character in a text file might be the answer. It means making a text file
first but automates the changes necessary to overcome the Excel problem. I
process the data futher so another character in place of the dash is no
problem. Replacing the dash with a space (a stroke of genius) would save
some of the reprocessing but I would have to devise a method of always having
4 columns. I will think about this. Maybe I will have to make 2 or more
text files by being selective with the copy and paste operation.

I hope Excel makes the change eventually. It wouldn't surprise me if an
earlier version of Excel (or some other spreadsheet program) did not have
this unfortunate operation.

Kind regards and thanks again.

--
Vince


"MartinW" wrote:

Hi again Vince,

A pretty ugly workaround but if you paste into word first and do a
find and replace the hyphen with a dot or maybe a space, then
paste into Excel.

A bit slow but you may be able to add a lot of the selections
to word first and then do the find and replace and copy to Excel
in batches.

Just a thought
Martin

It must be much easier for Excel to record what is pasted rather than try and
work out what was intended and paste something else. The user should have
the option to paste a character string or a date. 1-2-3 is not even an
Excel optionall date format. If it were the problem probably wouldn't arise.


"James" wrote:

It is a rediculous state of affaires when Excel does not allow a simple copy
and paste without screwing it up. Excel dates are very useful in some cases
and in others they are a pain.

"Vince" wrote:

Nick, Gord and Martin

Thank you all for your replies. You have been extremely helpful in getting
my grey matter working.

Nick, I will try your code but I presume it means finding the appropriate
cells, activating them and applying the code. Is there a search method
for finding all entries of type 01/02/2003? Is there a "seach and replace"
method in Excel of using these subs as replacement? If so, I could paste
direct to Excel and then search for the offending entries and replace them
with a sub. I may need to do it twice to replace entries like 1 Feb.
Since I process the data further, I might write slightly different codes
using your template. It all depends on being able to automate the
replacement process in Excel.

Martin, your suggestion of search and replace to change the dash to another
character in a text file might be the answer. It means making a text file
first but automates the changes necessary to overcome the Excel problem. I
process the data futher so another character in place of the dash is no
problem. Replacing the dash with a space (a stroke of genius) would save
some of the reprocessing but I would have to devise a method of always having
4 columns. I will think about this. Maybe I will have to make 2 or more
text files by being selective with the copy and paste operation.

I hope Excel makes the change eventually. It wouldn't surprise me if an
earlier version of Excel (or some other spreadsheet program) did not have
this unfortunate operation.

Kind regards and thanks again.

--
Vince


"MartinW" wrote:

Hi again Vince,

A pretty ugly workaround but if you paste into word first and do a
find and replace the hyphen with a dot or maybe a space, then
paste into Excel.

A bit slow but you may be able to add a lot of the selections
to word first and then do the find and replace and copy to Excel
in batches.

Just a thought
Martin

It is a ridiculous state of affaires when Excel does not allow a simple copy
and paste without screwing it up. Excel dates are very useful in some cases
and in others they are a pain.

Indeed. I found this thread (and *only* this thread) when I searched for «
Automatic data conversion when pasting », trying to solve a problem that
cropped up suddenly.

I have this fixed-format file that I grab (sending the .txt contents to the
clipboard through the .ContentsOnClipboard SendTo target, courtesy of the
PowerToys), and then a simple Excel macro pastes it in before parsing it into
columns. It used to paste into a one-column-by-many-lines cell block, but
suddenly Excel insists on parsing it out at paste time, without my consent.
Why the sudden change of behaviour? How to prevent it from occurring?

Urhixidur

If you search on "vince" you will find 6 posters who have a similar problem.

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

--
Vince


"Urhixidur" wrote:

It is a ridiculous state of affaires when Excel does not allow a simple copy
and paste without screwing it up. Excel dates are very useful in some cases
and in others they are a pain.

Indeed. I found this thread (and *only* this thread) when I searched for «
Automatic data conversion when pasting », trying to solve a problem that
cropped up suddenly.

I have this fixed-format file that I grab (sending the .txt contents to the
clipboard through the .ContentsOnClipboard SendTo target, courtesy of the
PowerToys), and then a simple Excel macro pastes it in before parsing it into
columns. It used to paste into a one-column-by-many-lines cell block, but
suddenly Excel insists on parsing it out at paste time, without my consent.
Why the sudden change of behaviour? How to prevent it from occurring?

Urhixidur

There are now at least 7 users who have posted this problem.

See "Using Hyphens in a cell"
--
Vince


"Urhixidur" wrote:

It is a ridiculous state of affaires when Excel does not allow a simple copy
and paste without screwing it up. Excel dates are very useful in some cases
and in others they are a pain.

Indeed. I found this thread (and *only* this thread) when I searched for «
Automatic data conversion when pasting », trying to solve a problem that
cropped up suddenly.

I have this fixed-format file that I grab (sending the .txt contents to the
clipboard through the .ContentsOnClipboard SendTo target, courtesy of the
PowerToys), and then a simple Excel macro pastes it in before parsing it into
columns. It used to paste into a one-column-by-many-lines cell block, but
suddenly Excel insists on parsing it out at paste time, without my consent.
Why the sudden change of behaviour? How to prevent it from occurring?

"Vince" wrote:

Urhixidur

There are now at least 7 users who have posted this problem.

See "Using Hyphens in a cell"

That I would never have found, as hyphens do not appear in the clipboard
contents that trigger the problem (except for occasional substrings like "ID-
time").

I've since found that closing Excel and launching it again clears the
problem, but I'm still ****ed at the behaviour changing without any prompt
and without any apparent means of control on my part.

"Vince" wrote:

There are now at least 7 users who have posted this problem.

See "Using Hyphens in a cell"

Nope, the posts all concern Excel auto-formatting, whereas the intermittent
bug/feature I've run into is Excel auto-parsing (i.e. the Data: Convert
menu). A very different problem.

I completely agree with the suggestion.

In my case, I have a block of data to paste (scraped from a table on the
internet), two columns of which are win-loss records (11-3, 10-4, etc.). Not
only are they displayed as dates, but the cell contents are converted to date
code so there is no hope of changing them back.

Instead of copying and pasting the web data, use DataImport External
DataNew Web Query.

In that dialog you have, under "Options", the function to "Disable Date
Recognition"

Or copy and paste into Notepad and save as *.txt file.

Open that in Excel and the Text Import Wizard will pop up allowing you to
designate the format of the data.


Gord Dibben MS Excel MVP



On Tue, 3 Mar 2009 10:50:01 -0800, Duane
wrote:

I completely agree with the suggestion.

In my case, I have a block of data to paste (scraped from a table on the
internet), two columns of which are win-loss records (11-3, 10-4, etc.). Not
only are they displayed as dates, but the cell contents are converted to date
code so there is no hope of changing them back.