how to stop auto converting number to date?

[Giedrius]

Hi
here is my problem.
I type 9.1 -> I get september 1 (In my country decimal is ",", but lots of
files comes with ".")
Thats nothing, it is easy to correct, but I'am using copy/paste from lots of
different files/pages/apps, and on every number that can be converted to
date, Excel converts it to date. Worst it can not be converted back. Maybe
there is an option to completely turn off this kind of conversion and leave
it like text, so I can use replace, or other function?

 

[Bernard Liengme]

Begin the entry with an apostrophe; '9.1
This will make it text
OR
pre-format the cell(s) as Text before entering the 9.1
best wishes

 

[bealz]

I have long lists of numbers which are written such as "2-3_" and I need to edit these at large to remove the underscore. When I do so, even if I set the formatting for the cells to text first, all of the numbers conver to dates "3-Feb" or when I click on the cell to edit "2/3/2007". If I then try to change the cell formatting back to text, the numbers have been completely altered, apparently by some mysterious formula to "37654".

If I manually delete the underscore, this does not happen, but this is not an option for the amount of data I am dealing with on a regular basis.

I have even tried exporting the data as a delimited text file, performing the find and replace in a text edditor and then re-importing the data to excel, and it still does it.

Why is this happening, and how do I prevent it? This is madness.

The underscore is an industry wide modifier that represents someething, so I cannot do away with it, or change any other parameter for how the data is used.

Thanks.

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[Dave Peterson]

Maybe you could use a helper column with a formula in it:

=left(a1,len(a1)-1)
or
=substitute(a1,"_","")
(if you have more than just that final one to get rid of)

 

[Dave Peterson]

One more...

Select the range (single column at a time)
data|text to columns
delimited by Other (specify the underscore)
but make sure you choose Text for that first field.

 

[Ben Deed]

I know how frustrating this is having just tried to deal with it myself, and the seemingly complete lack of solutions on the web!

The problem i had was in opening a large amount of data from a .csv format into Excel other people had suggested converting the cell to text but as mentioned this is of no help once the file is open and the information already converted.

Another suggestion stated putting an = in front of the data that was being converted into a date, this would be good, but not practicable for large amounts of data.

The best way i found was to open excel (blank workbook), Data - Get External Data - Import text file.
Change the selection to "All files" so you can pickup your .csv

Then during the import you will be able to view the data and change how each column will be imported, it is at this point you can select "text".

also, don't forget to change the delimiter to comma.

Hi
here is my problem.
I type 9.1 -> I get september 1 (In my country decimal is ",", but lots of
files comes with ".")
Thats nothing, it is easy to correct, but I'am using copy/paste from lots of
different files/pages/apps, and on every number that can be converted to
date, Excel converts it to date. Worst it can not be converted back. Maybe
there is an option to completely turn off this kind of conversion and leave
it like text, so I can use replace, or other function?

On Wednesday, December 27, 2006 10:20 AM Bernard Liengme wrote:
Begin the entry with an apostrophe; '9.1
This will make it text
OR
pre-format the cell(s) as Text before entering the 9.1
best wishes

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

news:[email protected]...

Submitted via EggHeadCafe - Software Developer Portal of Choice
Book Review: Excel 2010 - The Missing Manual [OReilly]
http://www.eggheadcafe.com/tutorial...w-excel-2010--the-missing-manual-oreilly.aspx