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:
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:
Useful. But is there a way to avoid this and make certain that during opening/download this is being avoided?
@Jan – Format the cells (or even entire column A) as TEXT before the barcodes are copied to the spreadsheet. If you format as TEXT after the “numbers” have been entered into the cells it has no effect and you’d have to use the process in this tutorial
I want to know how to then add a zero at the start of above barcode numbers once the format is changed to custom/0 without it dissapearing when you click out of the cell?
Hi Chloe
In another column (for example column B – you may need to insert a column) enter a formula: =0 & A1
Then copy the formula down column B or whichever column you have chosen
To overwrite the original numbers with the new values (the ones with the zeros), select the cells containing the formulas, click COPY, select the original data (in column A) and click the arrow below the Paste button and select VALUES – this copies the result of the formulas, not the formulas themselves