# Trying to increment a 4 character alphanumeric code in Excel

1. ## Trying to increment a 4 character alphanumeric code in Excel

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 VGW1, VGW2, etc. 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.

Also, if someone could kindly inform me of how I can make the errored cell show up correctly (it's supposed to be a percent symbol), that would be wonderful.

Please let me know if you need more information. I've attached the test file I was using to figure out how to make this work. Thanks!

2. ## Re: Trying to increment a 4 character alphanumeric code in Excel

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)

3. ## Re: Trying to increment a 4 character alphanumeric code in Excel

Here's what I did.
In B1, "MC"
In C1, 100 or whatever number you are starting with
In D1, your starting 4character code (i.e. VGBA)
In E1 copied right to H1
Formula:
`Please Login or Register  to view this content.`

In E2 Copied right to G2 and down as far as you can
Formula:
`Please Login or Register  to view this content.`

This increments skipping over the symbols between codes 57 and 65
In H2, copied down
Formula:
`Please Login or Register  to view this content.`

In D2 copied down
Formula:
`Please Login or Register  to view this content.`

In C2 copied down
Formula:
`Please Login or Register  to view this content.`

In B2 just copy MC down
In A2 copied down
Formula:
`Please Login or Register  to view this content.`

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1