Welcome, this is a follow-on video to an earlier presentation with solving exponential number format problems with numerical cells, especially when dealing with Excel csv file formats.

Our example source data is unable to present the full serial number; instead it is showing these cells in an exponential number format type.

Further, our serial numbers vary in length, which is proven when we apply the LEN Excel function. The previous solution won’t necessarily work because of this fluctuating length with the serial numbers.

Thus, we need a solution like this.

Again apply the TEXT function, but in the format_text format employ the REPT Excel function. The text syntax will be “#”, whilst for the number_times syntax we will apply the LEN Excel function to the underlying cell address of the serial number.

To prove it is working, fill this formula down the page.

We can clearly see this advanced Excel function formula is able to handle varying serial number lengths.

The case study workbook can be downloaded here.

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