• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 558
  • Last Modified:

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?
0
Scruples89
Asked:
Scruples89
  • 9
  • 3
1 Solution
 
CompProbSolvCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 9
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now