Do your phone numbers look like this in Excel?

This is a common problem for any piece of software that exports data containing a phone number to a .csv file and is later opened in Excel.

The telephone number data in the CRM is correct and the .csv file in the SDD contains the correct information but when you try to open the file in Excel it is reformatted into not so helpful scientific notation with strange formatting. You may also notice that the leading zero is missing.

For versions of Excel previous to Office 2016, follow these simple few steps to make sure your data is presented correctly.

  1. Open Excel (Do not double click on the file to open the spreadsheet)

  2. Select the Data Tab > From Text

  3. Select the .csv file from your computer. Click Import.

  4. Make sure the ‘comma’ option is selected. Click Next

  5. Select the Telephone Number column in the dialogue box. Click Next

  6. Change the column data format to text. Click Finish.

  7. Choose New Workbook to open the data in a new file. Click OK

Excel will now show you the phone numbers as they should be shown.

For versions of Excel after Office 2016, follow these steps to make sure your data is presented correctly.

  1. Open Excel (Do not double click on the file to open the spreadsheet).

  2. Select a blank workbook.

  3. Select the Data tab, and choose "From Text/CSV" (as in Fig 1, below).

  4. Select your CSV file from your computer.

  5. On the dialog box that appears click on the "Data Type Detection" drop down and choose "Do not detect data types" (as in Fig 2, below).

  6. Click "Load".

Fig 1:

Fig 2:

For more information on opening CSV files in Excel, please take a look at Microsoft Office's Official Support Pages

Did this answer your question?