Excel 2007 is converting CSV fields into formulas

I have a problem with a CSV file which has telephone numbers stored in their internationalized format (+1-800-555-1234). When opening this CSV in Excel 2007, it converts that data to a formula and displays the result (=1-800-555-1234; resulting in -2588).

How do I tell Excel to handle this field as a string?

Here is a sample - save this in to a .csv file and open in Excel reproduce the issue:

Number,Name
+1-800-555-1234,Bob
+1-800-555-1200,Jim

Solution 1:

Open a blank worksheet then select 'Data > Get External Data > From Text' (Excel 2007 menu layout). Then when you open your CSV file you'll get the 'Text Import Wizard' which will allow you to specify particular columns as Text instead of the default 'General'.
You might need to specify the appropriate File origin encoding (e.g. Unicode (UTF-8)) on the first page of the wizard, to hold onto the leading '+' characters.

Also, as Diago says, if you rename the file from .csv to .txt, and open in Excel, you'll get the Text Import Wizard without having to go through 'Data > Get external data...'.

Solution 2:

When saving the file use a TXT extension instead of CSV. This will force the Text Import Wizard to start and you can use this to force the column to text.

The only other way I can find a way to do this is by putting a single apostrophe in front of the + i.e.. '+1-8000. However when importing your stuck with this as part of the string.

From this explanation of CSV I can only deduce that this is one of the limitations with using the CSV format.

I would suggest rather importing and exporting in Xml rather then CSV if at all possible.