Bar Code generator

[JayG30]

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;

Field	Value	Status/Description
ID Mark for Standard	+	Simple, don't need help.
Label/Location Identification Code (LIC)	<user input=""></user>	Simple, don't need help.
Product/Catalog Number	<user input=""></user>	Simple, don't need help.
Unit of Measure	<user input=""></user>	Simple, don't need help.
Separator	/	Simple, don't need help.
Expiration Date	<user input=""></user>	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.
Lot Number	<user input=""></user>	Simple, don't need help.
Date of Manufacturing	<user input=""></user>	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).
Modulus 43 Check Character	<calculated></calculated>	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).

<tbody>
</tbody>

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.

 

[bbott]

****Disregard post****

 

[bbott]

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)

 

[JayG30]

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.

 

[bbott]

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.

 

[JayG30]

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.

 

[bbott]

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")

 

[JayG30]

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.

 

[bbott]

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.

 

[JayG30]

Awesome. I didn't originally realize the letters were ANSI representations of the month. Thanks again!