0

I'm trying to create a CSV file of one of my customer's serial numbers. We print them as barcodes for them to use, and normally I'd use our barcode software to generate the numbers. However, we're using a different method of printing, and it requires a CSV/Excel file of all the numbers to be printed. The barcode is as follows:

MC100VGVA.

The last digit is a check digit created from the rest of the string.

Now, my problem comes with the "VGVA" bit. Column A is the prefix (MC), Column B is the number (100), Column C is the incrementing 4 characters (VGVA), and Column D is the check digit.

I need for the VGVA bit to increment alphanumerically. So, when it gets to VGVZ, I need it to go to VGW0. Then when it gets to VGZZ, it needs to go to VH00 and so on until they reach ZZZZ, in which the next digit would increase Column B to 101, and Column C would become 0000.

I've attempted to use the CHAR formula, as well as CONCATENATE, and MID. But, because I'm not well versed in these formulas, my attempts at editing them to work with 4 digits have been failing me.

I'm not opposed to using VBA if needed, but it's not something I've ever worked with, so you'll have to forgive any ignorance on my part.

Please let me know if you need more information. Thanks!

KMToney09
  • 1
  • 1
    Hi, try looking at https://stackoverflow.com/questions/37475970/increment-excel-column-reference-using-vba-z-to-aa-aa-to-ab. It might just give you the answer/knowledge to make this work. – Havard Kleven Jul 07 '20 at 12:37

2 Answers2

2

It looks like you are trying to create a new base, the one based on 27 digits (0 and all letter from 'A' to 'Z'). So I'd advise you to create a conversion from and to 27-digit system.

Let me first explain you what I mean in octal numbering (8 digits, from 0 to 7): in that system we start from (just some examples):

a=0011
b=1237
c=1277

The meaning of those numbers is:

a equals 0*8^3 + 0*8^2 + 1*8^1 + 1*8^0 = 9, so:
a+1 equals 10, and converting this to octal numbering yields:

0012

b   equals 1*8^3+2*8^2+3*8^1+7*8^0 = 671, so:
b+1 equals 672, and converting this to octal numbering yields:

1240

c   equals 1*8^3 + 2*8^2 + 7*8^1 + 7*8^0 = 703, so:
c+1 equals 704, and converting this to octal numbering yields:

1300

I propose to do exactly the same for your 27-digit system, with following example:

VGZZ   equals 22*27^3 + 7*27^2 + 26*27^1 + 26 = 438857
VGZZ+1 equals 438858, and converting this to 27-digit numbering yields:

VH00

You can do this, using a VBA function you need to develop yourself. The converting from the string to the normal number is obvious, and in the other way around, you use =MOD(...,27^3) and other similar functions.

Dominique
  • 16,450
  • 15
  • 56
  • 112
  • I believe I'm actually trying to use a 36-base system (0-9, A-Z). However, I assume it would still be the same idea, just instead of multiplying by 27, I'd multiply by 36 to get the number to convert? – KMToney09 Jul 07 '20 at 15:03
0

I believe I've found a non-VBA answer to this question, thanks to someone on another forum.

Here's what they suggested and it seems to be working perfectly:

B2 =B1+(C2="0000")

C2 =RIGHT(BASE(DECIMAL(C1,36)+1,36,4),4)

and maybe try this at D1 =MID("0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%",MOD(SUMPRODUCT(SEARCH(MID((A1&B1&C1),ROW($1:$99),1), "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%") )-99,43)+1,1)

KMToney09
  • 1