7

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
Improve this question

5 Answers 5

Reset to default
12

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...'.

Improve this answer
2
  • That did the did the trick, and I did need to specify UTF-8 to keep "+1-" at the start of the field. Thanks!
    – dlux
    Jul 29, 2009 at 15:42
  • +1 for UTF-8. I was thinking encoding might have an effect but then scratched it as a bad idea.
    – BinaryMisfit
    Jul 29, 2009 at 16:08
4

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.

Improve this answer
1
  • 1
    Good point with renaming to.txt
    – pelms
    Jul 29, 2009 at 16:04
0

Right-click on the cell --> choose 'Format Cell' --> Select under 'Category:' Text.

Or put " " around the data that you want to be viewed as text in the CSV file.

-JFV

Improve this answer
2
  • I have tried that and it just changes the result to text. The underlying data is still evaluated as a formula.
    – dlux
    Jul 29, 2009 at 14:41
  • Even when the source data has double-quotes around it in the CSV (ie: "+1-800-555-1234")?
    – JFV
    Jul 29, 2009 at 15:25
0

If you have any control over how the CSV is created, try the solution proposed here:

Improve this answer
1
  • 3
    I realise you're trying to help, but copy-paste answers and "look over here for the answer" answers are generally frowned upon. The latter (this answer, for example) is not actually helpful in itself. Feel free to post the same information, yes, but please tailor it to each question.
    – DMA57361
    Aug 3, 2011 at 9:07
0

Use the TEXT() function.

=TEXT(B1,"+1-800-555-1234")
Improve this answer
1
  • Similar to above, the TEXT formula is formating the result of B1 as text, not the underlying string which is still being evaluated.
    – dlux
    Jul 29, 2009 at 14:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .