Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
share|improve this question
add comment

5 Answers

up vote 9 down vote accepted

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

share|improve this answer
    
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 '09 at 15:42
    
+1 for UTF-8. I was thinking encoding might have an effect but then scratched it as a bad idea. –  Diago Jul 29 '09 at 16:08
add comment

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.

share|improve this answer
1  
Good point with renaming to.txt –  pelms Jul 29 '09 at 16:04
add comment

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

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

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

share|improve this answer
1  
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 '11 at 9:07
add comment

Use the TEXT() function.

=TEXT(B1,"+1-800-555-1234")
share|improve this answer
    
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 '09 at 14:45
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.