Bar Code generator

JayG30

New Member
Joined
Sep 2, 2014
Messages
10
Hello,

I'm trying to put together a excel workbook that will calculate the code for a barcode. The barcode is "almost" a HIBC barcode (HERE). However someone decided to "alter" the standard (specifically how it decides to put a $, $$, or none before the second concatenated code) and the generator tools just don't work. So here are the 9 individual fields that go into the barcode;

[TABLE="class: grid, width: 800, align: center"]
<tbody>[TR]
[TD="align: center"]Field[/TD]
[TD="align: center"]Value[/TD]
[TD="align: center"]Status/Description[/TD]
[/TR]
[TR]
[TD]ID Mark for Standard[/TD]
[TD="align: center"]+[/TD]
[TD]Simple, don't need help.[/TD]
[/TR]
[TR]
[TD]Label/Location Identification Code (LIC)[/TD]
[TD="align: center"]<user input=""></user>[/TD]
[TD]Simple, don't need help.[/TD]
[/TR]
[TR]
[TD]Product/Catalog Number[/TD]
[TD="align: center"]<user input=""></user>[/TD]
[TD]Simple, don't need help.[/TD]
[/TR]
[TR]
[TD]Unit of Measure[/TD]
[TD="align: center"]<user input=""></user>[/TD]
[TD]Simple, don't need help.[/TD]
[/TR]
[TR]
[TD]Separator[/TD]
[TD="align: center"]/[/TD]
[TD]Simple, don't need help.[/TD]
[/TR]
[TR]
[TD]Expiration Date[/TD]
[TD="align: center"]<user input=""></user>[/TD]
[TD]I want users to enter dates normally (MM/DD/YYYY) but it needs to be changed to a 5 digit julian date. I'm using a formula to do this which works. So I think I have this.[/TD]
[/TR]
[TR]
[TD]Lot Number[/TD]
[TD="align: center"]<user input=""></user>[/TD]
[TD]Simple, don't need help.[/TD]
[/TR]
[TR]
[TD]Date of Manufacturing[/TD]
[TD="align: center"]<user input=""></user>[/TD]
[TD]Need help. This is formatted as 3 characters. First is the month represented by a letter (Jan=A, Feb=B,..Dec=L). last two digits are for the year (A15 = Jan 2015).[/TD]
[/TR]
[TR]
[TD]Modulus 43 Check Character[/TD]
[TD="align: center"]<calculated></calculated>[/TD]
[TD]Need help. This is calculated from the SUM of the whole barcode string. However for non-number values there is a correlating value and it isn't the ANSI equivalent. That info is HERE. Then the total is divided by 43 and you take the remainder (MOD).[/TD]
[/TR]
</tbody>[/TABLE]

Once I have all the values calculated I can just concatenate them with the "&" operation. Really there are two difficult fields.

What I was thinking could be done for the date of manufacturing would be to create a lookup table or some type of custom mapping. I swear I've seen this done with excel before, where you could basically define 01 or January to equate to the letter A. Unfortunately the date formatting didn't do this it does a 3 character date format where it takes the first character of the Month (J15 = January 2015).

With the Modulus 43 value it sounded easy but I can't find a good way to do it in excel. In a programming language like Java or C++ I would store the values in an array, step through them, have a case statement that said 'if array= "$" then +39' and so on for each possibility (or create some form of map). However I'm not sure if there is a way to accomplish this using excel functions to basically say "step through this array of characters, compare it to this lookup table, and sum the values".

Once I have the code correct creating the barcode image can be done.

Thanks for any assistance.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Try this for the Date of Manufacturing. Assuming three character code is in A1:

=TEXT(DATE(2000,CODE(A1)-64,1),"mmm")&" 20"&RIGHT(A1,2)
 
Last edited:
Upvote 0
Try this for the Date of Manufacturing. Assuming three character code is in A1:

=TEXT(DATE(2000,CODE(A1)-64,1),"mmm")&" 20"&RIGHT(A1,2)

Hum, I think you might be confused becaue what I get isn't what I'm expecting.
I want to be able to allow a user to enter
Expiration Date: 01/31/2016 or January 31, 2016 or whatever
and have that turn into A16 (A=january) so I can correctly format it for the actual barcode.
 
Upvote 0
Hum, I think you might be confused becaue what I get isn't what I'm expecting.
I want to be able to allow a user to enter
Expiration Date: 01/31/2016 or January 31, 2016 or whatever
and have that turn into A16 (A=january) so I can correctly format it for the actual barcode.

Ah, I did it backwards. Let me see what I can come up with.
 
Upvote 0
I pretty much got this working. It wasn't the most elegant solution but it worked. The only thing I didn't get was the date of manufacturing which I explained in the above post. The modulus number works though.

For anyone that might want to take a look the Excel file is HERE. Everything with calculating the code is done with formulas, not macro. The macro in this workbook is just for creating an actual barcode. I'm using the work from HERE. You have to have the Code128 font installed on your PC for the image to generate.

I ended up using the MID function to break the characters in the cell apart into rows and then used an index:match on a lookup table to calculate everything. Because there are defined limits to the size of fields I could actually set this up to work for all possible valid barcodes they could make. I'll repeat that this is a very specific type of code though, not HIBC.
 
Upvote 0
Hum, I think you might be confused becaue what I get isn't what I'm expecting.
I want to be able to allow a user to enter
Expiration Date: 01/31/2016 or January 31, 2016 or whatever
and have that turn into A16 (A=january) so I can correctly format it for the actual barcode.

Here's the fix to this:

=CHAR(MONTH(A1)+64)&TEXT(A1,"yy")
 
Upvote 0
That worked great!
Do you mind giving me a quick explanation of what it does?
I'm not sure I understand how it manages to set the month to the correct letter (A-L). Looks like it has to do with the MONTH(A1)+64 part.
I get the "yy" part.
 
Upvote 0
Sure.

This part:
=CHAR(MONTH(A1)+64)&TEXT(A1,"yy")

returns the month number (e.g. if 2/1/14 is in A1, it returns 2).

This part:
=CHAR(MONTH(A1)+64)&TEXT(A1,"yy")

tells Excel to change the number into the proper ANSI character. Since the capital letter alphabet starts at 65 in the ANSI character set, adding 64 to the result makes it alphabetical (e.g. 2 + 64 = 66. The 66th ANSI character is B).

The ampersand is just a concatenation joining the letter and the year.
 
Last edited:
Upvote 0
Awesome. I didn't originally realize the letters were ANSI representations of the month. Thanks again!
 
Upvote 0

Forum statistics

Threads
1,223,284
Messages
6,171,181
Members
452,388
Latest member
Lorenzo_Barry

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top