by MarkC
20. July 2011 15:16
If you purchase some data from us that contains a telephone number column, you may find that it doesn't display correctly in Excel. For example, our phone number 0151 355 4555 may show as 1513554555 or even 1.51E+09. Not very helpful.
This is because Excel is treating the field as a number rather than text. The file will actually contain the correct number, e.g. "01513554555", but when Excel opens the file it decides to display it in the same way it displays other numeric fields.
If you encounter this problem, you can avoid it by using the following steps:
Rename the file
When the filename has a ".csv" extension, Excel will always convert telephone numbers to numeric values and display them in the formats shown above. The first thing to do then is to rename the file to give it a ".txt" extension instead.
Open the file
In Excel, click File > Open and select the .txt file. Click Open and the Text Import Wizard will open.
Choose Delimited file type
On the first screen of this wizard, select the Delimited data type option and click Next.
Choose Comma Delimited
On the second screen of the wizard, select the Comma delimiter option. Ensure that all the other delimiter options are unticked, as is the "Treat consecutive delimiters as one" option. The "Text qualifier" option should be left as a double-quote.
Treat the telephone number column as text
Select the telephone number column, then click the Text data type option. Repeat this for any additional telephone number columns you have. Click Finish and your file will be opened correctly.
