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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Julie Willems - 17 Jan 2006 11:51 GMT

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.

Reply to this Message

Andy - 17 Jan 2006 12:53 GMT

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.

>I have copied and pasted numbers from a source on the internet into a
>column.
[quoted text clipped - 11 lines]
> text. This takes forever. There must be an easier way to do the entire
> column. Please help.

Reply to this Message

Gord Dibben - 17 Jan 2006 18:40 GMT

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

>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
[quoted text clipped - 6 lines]
>text. This takes forever. There must be an easier way to do the entire
>column. Please help.

Reply to this Message