How to keep "text" number from changing to date....

I have copied and pasted numbers from a source on the internet into a column.
Some appear to be formatted as numbers, some are formatted as text. When I
reformat using Data and Text to Columns to organize them and get rid of extra
spaces, any multiple numbers less than 31 with a dash in between them change
to dates, and then if I reformat cells to text it changes the dates to a five
digit number. Here is one example... 4-27 changes to Apr 27, then to 38824.

I have thousands of these. I've been entering '4-27 to keep the number as
text. This takes forever. There must be an easier way to do the entire
column. Please help.

Hi

Before you paste the data, format the column as Text. Once you have done
this, you can use Paste Special / Values.

Hope this helps.
Andy.

"Julie Willems" <Julie [email protected]> wrote in message
news:[email protected]...
>I have copied and pasted numbers from a source on the internet into a
>column.
> Some appear to be formatted as numbers, some are formatted as text. When I
> reformat using Data and Text to Columns to organize them and get rid of
> extra
> spaces, any multiple numbers less than 31 with a dash in between them
> change
> to dates, and then if I reformat cells to text it changes the dates to a
> five
> digit number. Here is one example... 4-27 changes to Apr 27, then to
> 38824.
>
> I have thousands of these. I've been entering '4-27 to keep the number as
> text. This takes forever. There must be an easier way to do the entire
> column. Please help.

Julie

When in the Text to Columns dialog there is an option under "Column data format"
to choose "text".

Try that on the columns with the 4-27 etc.

Alternative...........Data>Import External Data>New Web Query.

You will have the options to pick the data you want to import and

Click on the Options button and checkmark Formatting>None Import
blocks into columns and........... Disable Date Recognition.........


Gord Dibben MS Excel MVP

On Tue, 17 Jan 2006 03:51:02 -0800, "Julie Willems" <Julie
[email protected]> wrote:

>I have copied and pasted numbers from a source on the internet into a column.
>Some appear to be formatted as numbers, some are formatted as text. When I
>reformat using Data and Text to Columns to organize them and get rid of extra
>spaces, any multiple numbers less than 31 with a dash in between them change
>to dates, and then if I reformat cells to text it changes the dates to a five
>digit number. Here is one example... 4-27 changes to Apr 27, then to 38824.
>
>I have thousands of these. I've been entering '4-27 to keep the number as
>text. This takes forever. There must be an easier way to do the entire
>column. Please help.