Using a barcode scanner and Excel to track lot numbers and expiration dates.

I looking for a way to scan the barcode for the product number, the lot number and Expiration date and have Excel populate columns in the spread sheet. The first problem is that the only way I can get the barcode to read in excel is to format the cell as text. The first Code 128 bar code contains the product number but the product number is in the middle of the code. For example the first barcode reads 0130382902212615
.
I only need the numbers in red. This would populate one column.

The second barcode contains the Lot number and the Expiration date.

10318334617131025300100


The lot number is in Blue and the Epiration date is in Green. These would populate 2 other columns. I would also like to convert the date from yymmdd into something a little easier to read such as mm/dd/yy.


I believe that the 10 and the 17 are constant dividers for these barcodes.


Any ideas would be greatly appreciatied.

Thanks
Dayle

Thanks you very much. I guess I was just over thinking it.

One more question.....I hope. I have built the spreadsheet and have entered the a table of product code and products on Sheet 2. I would like to populate column B by using the product number in column C to search Sheet2 and return the product name. I have tried to use the LOOKUP function but I don't have something quite right with it. I keep getting "#N/A".

Thanks
Dayle

Thanks

I figured out that if I add another column and multiply the value in column C, that contains the left and right functions, times 1 and look up this number the Look up function works fine. I then hid column D to make the spreadsheet look cleaner. Now when I enter the first 3 products they work fine. When I try to enter the fourth set of bar codes in lines 8 and 9 I get an error stating "cannot change part of a merged cell". When I click ok it enters the information correctly. If i start entering data at lines 8 and 9 they work fine but the error pops up on lines 10 and 11.

error.gif