SOLVED

Barcodes in Exel

%3CLINGO-SUB%20id%3D%22lingo-sub-2804699%22%20slang%3D%22en-US%22%3EBarcodes%20in%20Exel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2804699%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20guys%20i%20need%20some%20major%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20setting%20up%20a%20POS%20system%20for%20a%20shop%20and%20i'm%20having%20issues%20with%20the%20barcode%20formatting.%20When%20first%20entering%20the%20data%20I%20have%20no%20problems%2C%20I%20reformat%20the%20column%20to%20text%20and%20everything%20works%20fine.%20What%20i%20need%20help%20with%20is%20downloaded%20documents.%3C%2FP%3E%3CP%3EWhen%20I%20want%20to%20add%20a%20new%20product%20into%20the%20shop%20system%20I%20have%20to%20download%20a%20bulk%20template%20of%20all%20the%20products%20already%20uploaded%20which%20is%20where%20my%20problem%20lies.%20I%20have%20both%20barcodes%20with%20leading%200's%20and%20long%20numbers%20which%20convert%20to%20scientific%20notation.%20Everything%20I%20can%20find%20tells%20me%20how%20to%20prevent%20these%20things%20from%20happening%20not%20what%20to%20do%20when%20the%20file%20does%20it%20automatically%20upon%20opening.%20No%20matter%20what%20I%20do%20to%20the%20document%20the%20scientific%20numbers%20stay%20scientific%20and%20the%20leading%200's%20have%20already%20been%20deleted.%20To%20make%20it%20more%20complicated%20only%20some%20scientific%20numbers%20stay%20in%20that%20format%20while%20others%20are%20more%20than%20happy%20to%20return%20to%20a%20simple%20number%20format.%20Currently%20i%20have%20to%20go%20through%20and%20pick%20out%20all%20the%20barcodes%20in%20scientific%20and%20reformat%20the%20cells%20as%20numbers%20then%20pick%20out%20all%20the%20ones%20that%20should%20have%20leading%200's%2C%20reformat%20them%20to%20text%20then%20manually%20re%20add%20the%200.%20The%20catch%20is%20they%20are%20all%20mixed%20up%20and%20there%20is%20no%20way%20to%20tell%20what%20should%20have%20a%20leading%200%20once%20that%200%20is%20gone.%20Just%20because%20its%20a%20short%20barcode%20doesn't%20mean%20it%20should%20have%20a%200%20and%20vice%20versa.%3CBR%20%2F%3EI%20am%20sick%20of%20needing%20to%20check%203000%20barcodes%20every%20time%20we%20get%20a%20new%20product%20in%20the%20store.%20All%20i%20do%20these%20days%20is%20fix%20work%20i've%20already%20fixed%20a%20thousand%20times.%20I'm%20going%20crazy.%20Does%20anybody%20know%20how%20to%20save%20my%20barcodes%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20work%20the%20file%20must%20be%20in%20CSV%20and%20contain%20only%20simple%20numbers%20in%20the%20barcode%20column.%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20picture%20bellow%20column%20P%20is%20what%20it%20should%20look%20like%20column%20Q%20is%20what%20i%20get.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2804699%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

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.

1 Reply
best response confirmed by Ali_111690 (Occasional Visitor)
Solution

@Ali_111690 On the Data ribbon, you should have a button "Get Data". Click it and select "From Text".

Navigate to the folder with the CSV file. Excel will only highlight TXT files but you can still select the CSV file and press Get Data. The text import wizard opens. In step 1 you select Delimited. In step 2 you set the comma as the delimiter. Step 3 will give you a preview of how the text will be split in columns. Now, make sure that you set the correct data types for each of the columns (General, Text or Date). In your case, you need to set the column with the barcodes as Text. Press finish.

This should import the CSV file in neat columns with the original barcodes as texts, preserving the leading zero's.