I am trying to copy a multicolumn table to my spreadsheet;

However, for about 5 of the fifteen columns, I get a "DATE",
such as

1-3-2000

instead of 1-3-1....which means the team won 1, lost 3, and tied 1.

If I reformat the " 1/3/2000 ", I get 36528 (i.e., the date's numerical
equivalent) -- i.e. still does NOT work.

Also, some columns have roughly half the numbers copied correctly, while the
rest of the column is, again, an apparent "DATE".

Can anyone help please?

Thanks,

FLKulchar

When you import the data (through the text import wizard?), specify the
format of the relevant columns as being text.
--
David Biddulph

"F. Lawrence Kulchar" wrote in
message ...
I am trying to copy a multicolumn table to my spreadsheet;

However, for about 5 of the fifteen columns, I get a "DATE",
such as

1-3-2000

instead of 1-3-1....which means the team won 1, lost 3, and tied 1.

If I reformat the " 1/3/2000 ", I get 36528 (i.e., the date's numerical
equivalent) -- i.e. still does NOT work.

Also, some columns have roughly half the numbers copied correctly, while
the
rest of the column is, again, an apparent "DATE".

Can anyone help please?

Thanks,

FLKulchar

If you use dataimport external datanew web query you can set
some restraints like not converting what looks like dates to dates.

--


Regards,


Peo Sjoblom

"F. Lawrence Kulchar" wrote in
message ...
I am trying to copy a multicolumn table to my spreadsheet;

However, for about 5 of the fifteen columns, I get a "DATE",
such as

1-3-2000

instead of 1-3-1....which means the team won 1, lost 3, and tied 1.

If I reformat the " 1/3/2000 ", I get 36528 (i.e., the date's numerical
equivalent) -- i.e. still does NOT work.

Also, some columns have roughly half the numbers copied correctly, while
the
rest of the column is, again, an apparent "DATE".

Can anyone help please?

Thanks,

FLKulchar

Hi,

Format the target range to TEXT first and then copy and in Excel see if
there are any Paste Special options available, if so pick formula or values.

If that doesn't work you can format those cells to text and manually enter
the 1-3-1, if there arn't too many.

You could write code to make the conversion easier.

Or you could enter a formula like

=MONTH(A1)&"-"&DAY(A1)&"-"&RIGHT(YEAR(A1),1)


--
Thanks,
Shane Devenshire


"F. Lawrence Kulchar" wrote:

I am trying to copy a multicolumn table to my spreadsheet;

However, for about 5 of the fifteen columns, I get a "DATE",
such as

1-3-2000

instead of 1-3-1....which means the team won 1, lost 3, and tied 1.

If I reformat the " 1/3/2000 ", I get 36528 (i.e., the date's numerical
equivalent) -- i.e. still does NOT work.

Also, some columns have roughly half the numbers copied correctly, while the
rest of the column is, again, an apparent "DATE".

Can anyone help please?

Thanks,

FLKulchar

I understand what you wrote...but how is all that done please?

FLKulchar


"Peo Sjoblom" wrote:

If you use dataimport external datanew web query you can set
some restraints like not converting what looks like dates to dates.

--


Regards,


Peo Sjoblom

"F. Lawrence Kulchar" wrote in
message ...
I am trying to copy a multicolumn table to my spreadsheet;

However, for about 5 of the fifteen columns, I get a "DATE",
such as

1-3-2000

instead of 1-3-1....which means the team won 1, lost 3, and tied 1.

If I reformat the " 1/3/2000 ", I get 36528 (i.e., the date's numerical
equivalent) -- i.e. still does NOT work.

Also, some columns have roughly half the numbers copied correctly, while
the
rest of the column is, again, an apparent "DATE".

Can anyone help please?

Thanks,

FLKulchar

I did format the destination to cells, but to no avail...and there are too
many to manually change.

Any other suggestions?

FLKulchar

"ShaneDevenshire" wrote:

Hi,

Format the target range to TEXT first and then copy and in Excel see if
there are any Paste Special options available, if so pick formula or values.

If that doesn't work you can format those cells to text and manually enter
the 1-3-1, if there arn't too many.

You could write code to make the conversion easier.

Or you could enter a formula like

=MONTH(A1)&"-"&DAY(A1)&"-"&RIGHT(YEAR(A1),1)


--
Thanks,
Shane Devenshire


"F. Lawrence Kulchar" wrote:

I am trying to copy a multicolumn table to my spreadsheet;

However, for about 5 of the fifteen columns, I get a "DATE",
such as

1-3-2000

instead of 1-3-1....which means the team won 1, lost 3, and tied 1.

If I reformat the " 1/3/2000 ", I get 36528 (i.e., the date's numerical
equivalent) -- i.e. still does NOT work.

Also, some columns have roughly half the numbers copied correctly, while the
rest of the column is, again, an apparent "DATE".

Can anyone help please?

Thanks,

FLKulchar

When you click new web query you will get a window opening, if you have your
default web application running it will probably open that website
regardless it has an address bar where you can type or paste in the address
of you web table and when the website opens there should be a yellow arrow
to the left of your table, click that arrow to select the table and then
click import. Now the import data dialogue box will open and under
properties you can select different options such as save query definition,
enable background refresh, preserve cell formatting and the latter will make
sure the import will be text if you previously formatted it as text and data
like 1-3-1 will come in as expected and not as a date.

--


Regards,


Peo Sjoblom

"F. Lawrence Kulchar" wrote in
message ...

I understand what you wrote...but how is all that done please?

FLKulchar


"Peo Sjoblom" wrote:

If you use dataimport external datanew web query you can set
some restraints like not converting what looks like dates to dates.

--


Regards,


Peo Sjoblom

"F. Lawrence Kulchar" wrote
in
message ...
I am trying to copy a multicolumn table to my spreadsheet;

However, for about 5 of the fifteen columns, I get a "DATE",
such as

1-3-2000

instead of 1-3-1....which means the team won 1, lost 3, and tied 1.

If I reformat the " 1/3/2000 ", I get 36528 (i.e., the date's numerical
equivalent) -- i.e. still does NOT work.

Also, some columns have roughly half the numbers copied correctly,
while
the
rest of the column is, again, an apparent "DATE".

Can anyone help please?

Thanks,

FLKulchar

Hi,

Use the formula approach I suggested at the end of the last post.
--
Thanks,
Shane Devenshire


"F. Lawrence Kulchar" wrote:

I did format the destination to cells, but to no avail...and there are too
many to manually change.

Any other suggestions?

FLKulchar

"ShaneDevenshire" wrote:

Hi,

Format the target range to TEXT first and then copy and in Excel see if
there are any Paste Special options available, if so pick formula or values.

If that doesn't work you can format those cells to text and manually enter
the 1-3-1, if there arn't too many.

You could write code to make the conversion easier.

Or you could enter a formula like

=MONTH(A1)&"-"&DAY(A1)&"-"&RIGHT(YEAR(A1),1)


--
Thanks,
Shane Devenshire


"F. Lawrence Kulchar" wrote:

I am trying to copy a multicolumn table to my spreadsheet;

However, for about 5 of the fifteen columns, I get a "DATE",
such as

1-3-2000

instead of 1-3-1....which means the team won 1, lost 3, and tied 1.

If I reformat the " 1/3/2000 ", I get 36528 (i.e., the date's numerical
equivalent) -- i.e. still does NOT work.

Also, some columns have roughly half the numbers copied correctly, while the
rest of the column is, again, an apparent "DATE".

Can anyone help please?

Thanks,

FLKulchar

Go back and read Peo's last posting.

Import rather than copy/paste.


Gord Dibben MS Excel MVP

On Mon, 10 Nov 2008 13:39:03 -0800, F. Lawrence Kulchar
wrote:

I did format the destination to cells, but to no avail...and there are too
many to manually change.

Any other suggestions?

FLKulchar

"ShaneDevenshire" wrote:

Hi,

Format the target range to TEXT first and then copy and in Excel see if
there are any Paste Special options available, if so pick formula or values.

If that doesn't work you can format those cells to text and manually enter
the 1-3-1, if there arn't too many.

You could write code to make the conversion easier.

Or you could enter a formula like

=MONTH(A1)&"-"&DAY(A1)&"-"&RIGHT(YEAR(A1),1)


--
Thanks,
Shane Devenshire


"F. Lawrence Kulchar" wrote:

I am trying to copy a multicolumn table to my spreadsheet;

However, for about 5 of the fifteen columns, I get a "DATE",
such as

1-3-2000

instead of 1-3-1....which means the team won 1, lost 3, and tied 1.

If I reformat the " 1/3/2000 ", I get 36528 (i.e., the date's numerical
equivalent) -- i.e. still does NOT work.

Also, some columns have roughly half the numbers copied correctly, while the
rest of the column is, again, an apparent "DATE".

Can anyone help please?

Thanks,

FLKulchar

Your formula works perfectly...Thank you,

FLKulchar

--
Francis L. Kulchar
"ShaneDevenshire" wrote in
message ...
Hi,

Format the target range to TEXT first and then copy and in Excel see if
there are any Paste Special options available, if so pick formula or
values.

If that doesn't work you can format those cells to text and manually enter
the 1-3-1, if there arn't too many.

You could write code to make the conversion easier.

Or you could enter a formula like

=MONTH(A1)&"-"&DAY(A1)&"-"&RIGHT(YEAR(A1),1)


--
Thanks,
Shane Devenshire


"F. Lawrence Kulchar" wrote:

I am trying to copy a multicolumn table to my spreadsheet;

However, for about 5 of the fifteen columns, I get a "DATE",
such as

1-3-2000

instead of 1-3-1....which means the team won 1, lost 3, and tied 1.

If I reformat the " 1/3/2000 ", I get 36528 (i.e., the date's numerical
equivalent) -- i.e. still does NOT work.

Also, some columns have roughly half the numbers copied correctly, while
the
rest of the column is, again, an apparent "DATE".

Can anyone help please?

Thanks,

FLKulchar

Thank you...your formula works perfectly...thank you!!

FLKulchar

--
Francis L. Kulchar
"ShaneDevenshire" wrote in
message ...
Hi,

Format the target range to TEXT first and then copy and in Excel see if
there are any Paste Special options available, if so pick formula or
values.

If that doesn't work you can format those cells to text and manually enter
the 1-3-1, if there arn't too many.

You could write code to make the conversion easier.

Or you could enter a formula like

=MONTH(A1)&"-"&DAY(A1)&"-"&RIGHT(YEAR(A1),1)


--
Thanks,
Shane Devenshire


"F. Lawrence Kulchar" wrote:

I am trying to copy a multicolumn table to my spreadsheet;

However, for about 5 of the fifteen columns, I get a "DATE",
such as

1-3-2000

instead of 1-3-1....which means the team won 1, lost 3, and tied 1.

If I reformat the " 1/3/2000 ", I get 36528 (i.e., the date's numerical
equivalent) -- i.e. still does NOT work.

Also, some columns have roughly half the numbers copied correctly, while
the
rest of the column is, again, an apparent "DATE".

Can anyone help please?

Thanks,

FLKulchar

You mention:

"click that arrow to select the table and then
click import. "

Where is this import button...on the Excel spreadsheet (if so, where?), or
on the web page?

FLKulchar

--
Francis L. Kulchar
"Peo Sjoblom" wrote in message
...
When you click new web query you will get a window opening, if you have
your default web application running it will probably open that website
regardless it has an address bar where you can type or paste in the
address of you web table and when the website opens there should be a
yellow arrow to the left of your table, click that arrow to select the
table and then click import. Now the import data dialogue box will open
and under properties you can select different options such as save query
definition, enable background refresh, preserve cell formatting and the
latter will make sure the import will be text if you previously formatted
it as text and data like 1-3-1 will come in as expected and not as a date.

--


Regards,


Peo Sjoblom

"F. Lawrence Kulchar" wrote
in message ...

I understand what you wrote...but how is all that done please?

FLKulchar


"Peo Sjoblom" wrote:

If you use dataimport external datanew web query you can set
some restraints like not converting what looks like dates to dates.

--


Regards,


Peo Sjoblom

"F. Lawrence Kulchar" wrote
in
message ...
I am trying to copy a multicolumn table to my spreadsheet;

However, for about 5 of the fifteen columns, I get a "DATE",
such as

1-3-2000

instead of 1-3-1....which means the team won 1, lost 3, and tied 1.

If I reformat the " 1/3/2000 ", I get 36528 (i.e., the date's
numerical
equivalent) -- i.e. still does NOT work.

Also, some columns have roughly half the numbers copied correctly,
while
the
rest of the column is, again, an apparent "DATE".

Can anyone help please?

Thanks,

FLKulchar