+ Reply to Thread
Results 1 to 3 of 3

Trying to increment a 4 character alphanumeric code in Excel

  1. #1
    Registered User
    Join Date
    04-17-2019
    Location
    Terre Haute, IN
    MS-Off Ver
    Office 2019 for Mac
    Posts
    6

    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!
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

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

    Please try at
    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)
    Attached Files Attached Files

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    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: copy to clipboard
    Please Login or Register  to view this content.

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

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

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

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

    In B2 just copy MC down
    In A2 copied down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VBA Auto increment of alphanumeric values
    By fmm139 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-21-2019, 01:16 PM
  2. Auto increment of alphanumeric values
    By fmm139 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-21-2019, 01:14 PM
  3. [SOLVED] Increment Alphanumeric List
    By frekels21 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 12-27-2017, 10:00 PM
  4. insert row and increment alphanumeric unique ID
    By rickmeister in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-31-2016, 08:32 PM
  5. [SOLVED] Can't increment Alphanumeric string by one
    By Ochimus in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-07-2016, 12:08 AM
  6. Increment Alphanumeric Value of a Cell on each print job
    By rudygortiz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-12-2014, 03:55 PM
  7. Replicate and increment alphanumeric keywords?
    By toolsavvy in forum Excel General
    Replies: 7
    Last Post: 12-16-2007, 03:02 AM

Tags for this Thread

Bookmarks

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