SOLVED

Barcodes in Exel

Copper Contributor

Hi guys i need some major help.

 

I am setting up a POS system for a shop and i'm having issues with the barcode formatting. When first entering the data I have no problems, I reformat the column to text and everything works fine. What i need help with is downloaded documents.

When I want to add a new product into the shop system I have to download a bulk template of all the products already uploaded which is where my problem lies. I have both barcodes with leading 0's and long numbers which convert to scientific notation. Everything I can find tells me how to prevent these things from happening not what to do when the file does it automatically upon opening. No matter what I do to the document the scientific numbers stay scientific and the leading 0's have already been deleted. To make it more complicated only some scientific numbers stay in that format while others are more than happy to return to a simple number format. Currently i have to go through and pick out all the barcodes in scientific and reformat the cells as numbers then pick out all the ones that should have leading 0's, reformat them to text then manually re add the 0. The catch is they are all mixed up and there is no way to tell what should have a leading 0 once that 0 is gone. Just because its a short barcode doesn't mean it should have a 0 and vice versa.
I am sick of needing to check 3000 barcodes every time we get a new product in the store. All i do these days is fix work i've already fixed a thousand times. I'm going crazy. Does anybody know how to save my barcodes?

 

To work the file must be in CSV and contain only simple numbers in the barcode column. 

In the picture bellow column P is what it should look like column Q is what i get.

20 Replies

@HShowalt If all you need is to save EAN numbers that you already have without errors in Excel - go with the spoiler and save them as text.

If you need to import and work with numbers generated outside the workbook, spend some time to understand the difference between numbers and strings. The replies above contains both information and possibilities but it’s up to you to balance the reading effort against your needs.