Long # turns into a formula-How do I turn that off?

C

Chris

I have to enter the date in a certain way, yyyymmddhhmm.
I enter 200704301524 into a cell. I save the file in .csv. When I open the
file again, the # is changed to a formula, 2.00704E+11
How do I get it to not do that? I have tried saving it as a text, and it
still doesn't work.
Thanks,
Chris
 
J

JE McGimpsey

You're seeing scientific notation format. You could change the format to:

Format/Cells/Number/Custom 000000000000
 
C

Chris

I do that, and save teh file. When I open the file again, it goes back to the
formula.
 
C

Chris

I have done that too, and the same thing happens. I save the file. Close it
out, and when I reopen it, the formula is back.
 
B

Bob I

I'm sorry, missed the CSV and opening it as CSV. Enclose it in quotes.
'200704301524'
 
D

Dave Peterson

Yep.

Excel sees the .csv file and does what it wants with the data.

You have a few options:
1. Don't save the file as .csv (use .xls -- a normal workbook)
2. Rename the .csv file to .txt and when you open that file via File|Open,
you'll be able to specify the field the way you want (text??).
3. Reapply the number formatting each time you open the .csv file.
 
D

Dave Peterson

But doesn't this add extra characters to each value?

I ended up with '1234' in a cell (including both apostrophes)
 
B

Bob I

Yes, but it will preserve the "text number" in the .csv save and reload
scenario. Otherwise they will need to play the .txt file game.
 
D

Dave Peterson

I wouldn't call '1234' with the surrounding apostrophes a "text number". It
looks like a plain old text string to me--just like x1234x does.

But maybe it'll server the OP.
 
Top