Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 17th 06, 11:51 AM posted to microsoft.public.excel.newusers
Julie Willems
 
Posts: n/a
Default 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.

  #3   Report Post  
Old January 17th 06, 06:40 PM posted to microsoft.public.excel.newusers
Gord Dibben
 
Posts: n/a
Default How to keep "text" number from changing to date....

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...........DataImport External DataNew Web Query.

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

Click on the Options button and checkmark FormattingNone 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
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.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I keep the date from changing format in a mail merge? Brynn Wilson Excel Discussion (Misc queries) 2 February 1st 07 05:19 PM
How do I keep the date from changing format in a mail merge? Brynn Wilson Excel Discussion (Misc queries) 1 June 9th 05 06:44 PM
How do I stop excel automatically changing my date to 2005? zoemcb Excel Discussion (Misc queries) 6 April 30th 05 02:39 AM
changing a cell from date to a number mwhite Excel Worksheet Functions 1 March 22nd 05 04:35 PM
cells changing from number to date [email protected] Excel Discussion (Misc queries) 1 March 22nd 05 04:31 PM


All times are GMT +1. The time now is 10:15 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017