One of my friends is responsible for keeping track of cleaning materials at the company where he works. To do this, he goes to the cupboard where the materials are stored and using a hand-held scanner, he scans the barcode of each item. Once he is done, he plugs the scanner into the USB port of his laptop, opens a new workbook in Excel and presses a button on the device. The barcodes are copied to Excel, one per cell, down column A.

The problem is that because the barcodes are more than 11 digits in length, they are displayed in “exponential” format:

Exponential Format

To display the barcodes as proper numbers (i.e. a readable string of digits)…

Select the cells containing the numbers

On the Home tab of The Ribbon click the drop down arrow next to General and select More Number Formats (the screenshot below is taken on Excel 2016 for Mac but it’s the same on Windows)

Click Custom on the left hand side and select 0 (the first formatting option) and click OK


The values in column A are now displayed in the desired format: