Link to home
Start Free TrialLog in
Avatar of Mike McLoughlin
Mike McLoughlinFlag for Canada

asked on

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?
ASKER CERTIFIED SOLUTION
Avatar of CompProbSolv
CompProbSolv
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mike McLoughlin
Mike McLoughlin
Flag of Canada image

ASKER

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?
Avatar of CompProbSolv
CompProbSolv
Flag of United States of America image
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.
Avatar of Mike McLoughlin
Mike McLoughlin
Flag of Canada image

ASKER

My problem is that it appears that excel has "turned off" formula calculations. as a result of me importing the data as text..
Avatar of Mike McLoughlin
Mike McLoughlin
Flag of Canada image

ASKER

I got the formulas switched back on by saving the file as a excel workbook and reopening it.
Avatar of Mike McLoughlin
Mike McLoughlin
Flag of Canada image

ASKER

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.
Avatar of Mike McLoughlin
Mike McLoughlin
Flag of Canada image

ASKER

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.
Avatar of Mike McLoughlin
Mike McLoughlin
Flag of Canada image

ASKER

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?
Avatar of Mike McLoughlin
Mike McLoughlin
Flag of Canada image

ASKER

Why is Excel NOT giving me the option to save it as a CSV file?
Avatar of Mike McLoughlin
Mike McLoughlin
Flag of Canada image

ASKER

I am opening up another question on this problem! Thanks for helping me here.
Avatar of CompProbSolv
CompProbSolv
Flag of United States of America image
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?
Avatar of Mike McLoughlin
Mike McLoughlin
Flag of Canada image

ASKER

Thanks this worked!