This article will show one approach to correct the exponential number format referencing issue in a cell, especially when dealing with csv files. Sometimes, as we can see here, certain numerical data contains formatting problems; even if we try to convert it to numbers.

This can be quite frustrating because we simply want to manage source data. To validate the length of these serial numbers in column C, we will apply the LEN Excel function to identify the number of characters.

As we can see, our serial numbers are all a uniform 12 characters long.

To overcome the exponential number format issue, apply the TEXT Excel function like so. For the format_text syntax in the TEXT function, we need to apply the hashtag 12 times in the formula.

This solution is clearly working and we can fill the formula down the data set. As mentioned, this problem is often encountered when dealing with data in csv Excel file formats.

The case study workbook can be downloaded here.

Download Spreadsheet Vault SpreadsheetVault – Scientific (exponential) notation to number format (solution).csv