Opening numbers related data in Excel the right way

Common CSV files that have data usually contain numbers and when you open those files in Microsoft Excel you encounter issues with how the data is displayed in Excel.
Some common problems are

  • ‘Leading’  zeros may get dropped – very commonly seen with US Zip codes in the North East 02134 eg becomes 2134
  • Long numbers that are displayed as exponential characters
  • Date show up as numbers

While loading a CSV file in Excel, the software interprets/treats postal codes, phone numbers, dates, and other similar entries as numbers. Excel applies the General or Number format to them, and automatically removes preceding or succeeding zeroes, long numbers get displayed as exponential characters or dates displayed as numbers.

Luckily, Excel also provides the means to keep the format as it is in the CSV and further on in this article, you will find a step by step solution to resolve these problems without hassle.

For older versions of Excel;

  1. Start Microsoft Excel.
  2. In Excel, click the Data tab, and in the Get External Data ribbon/panel, click From Text.
  3. In the Import Text File dialog box, in the lower-right corner (to the right of the File name box), select Text Files (*.prn;*.txt;*.csv) as the file type, browse to the location where you exported/downloaded the CSV file, and click Import.
  4. Step 1; Select the Delimited radio button — Text Import Wizard (this step determines your data is delimited) – Click Next
  1. Step 2; Check Comma as a delimiter (column dividers will appear in preview), (this step lets you set delimiters) – Click Next.
  1.  Step 3; Highlight the column(s) with the formatting problem. Mark those columns format as Text by clicking the radio button in the Column Data Format section. NOTE: You will need to do this for each column where the data has an issue.
  1. Click Finish.

Voilà! you now have a properly formatted excel file.

For newer version of Office 365 based Excel;

  1. Start Microsoft Excel.

  2. In Excel, click the Data tab, and in the Get & Transform Data ribbon/panel, click From Text/CSV.

  3. In the Import Text File dialog box, in the lower-right corner (to the right of the File name box), select Text Files (*.prn;*.txt;*.csv) as the file type, browse to the location where you exported/downloaded the CSV file, and click Import.
  4. Step 1; In the new dialog box that opens, select Comma as Delimiter and select Data Type Detection as Based on entire dataset.

In most cases, the above step resolves the problem and we just need to click on Load. Check the preview if the issue is resolved, if not, move to the next step.

  1. Step 2; Click the Transform Data button and the Power Query Editor will open in a new window.
  1. Step 3; In the Power Query Editor window, select the column(s) that have the problem and change the Data Type as Text (dropdown) from the Transform ribbon.
  1. Finally, click on Close & Load from the left top corner.

Note; The above solution(s) works for all formatting issues such as;

  • Missing Leading zeros
  • Missing Trailing zeros (Phone numbers and Zip codes)
  • Dates displayed as numeric
  • Long numbers displayed as exponential characters

Turn the Internet into meaningful, structured and usable data