Stopping Excel from automatically converting CSV data to number format when opening CSV file.

I am opening a CSV file with Excel. I want excel to leave the data in certain fields alone and not convert it. Excel automatically converts the data value of "001" to the number "1". However, I need the sequence 001 to remain so I can concatenate it with another data value that has a similar sequence of 001 so that resulting data value is 001001 NOT 11. How do I prevent excel from autoconverting the data when it opens a csv file?
Scruples89Asked:
Who is Participating?
 
CompProbSolvConnect With a Mentor Commented:
Take a look at:
http://office.microsoft.com/en-us/excel-help/import-or-export-text-txt-or-csv-files-HP010099725.aspx#BMimport_data_from_a_text_file_by_openi

It appears that you should be able to import the file and restrict the formatting by setting it appropriately in the spreadsheet before importing.
0
 
Scruples89Author Commented:
Thanks! I followed the instructions in that link and it worked. Now my problem is that when Icreate teh concatenate formula, it is not calculating. I want to add two columns together to create one value in a third column and then save it back to strickly csv format. This means I want to do the calculation, get the values and then save the values as data. Any suggestions?
0
 
CompProbSolvCommented:
The value() function may be what you want.  For example:
=value(a1&a2)
will concatenate the strings in a1 and a2, then convert them to a numeric value.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Scruples89Author Commented:
My problem is that it appears that excel has "turned off" formula calculations. as a result of me importing the data as text..
0
 
Scruples89Author Commented:
I got the formulas switched back on by saving the file as a excel workbook and reopening it.
0
 
Scruples89Author Commented:
As an excel workbook I can now use formulas to combine text values, but the formulas are not calculating automatically. I need to push the F9 key to get them calculating properly.
0
 
Scruples89Author Commented:
Now I want to save the worksheet as a csv file but it does not give me that option when I go to save it. It only permits me to save it as excel type files.
0
 
Scruples89Author Commented:
I cannot believe that I am having so manyproblems working the csv data in excel! It is a nightmare. Is there a better progam to use to work with CSV data?
0
 
Scruples89Author Commented:
Why is Excel NOT giving me the option to save it as a CSV file?
0
 
Scruples89Author Commented:
I am opening up another question on this problem! Thanks for helping me here.
0
 
CompProbSolvCommented:
Needing to hit F9 implies that auto recalc is off.  In Excel 2010 that is found under:
File
Options
Formulas
Workbook Calculation

I'm not at all sure why you can't save to CSV.  What version of Excel?  What other options does it list?
0
 
Scruples89Author Commented:
Thanks this worked!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.