alphanumeric code calculation with set limits

  • Hello,
    My question is a bit complex. I am sure there is a formula that can do what Iam asking I just cant decide what formula best suites it.

    I have a string of serial numbers that once reach a certain limit will need to change a portion of the string ie

    A-1-1 (A represents a cabinet, 1 represents the slot, and 1 respesents the spaces in that slot)
    A-1-2
    A-1-3
    A-1-4

    There should be a max of 4 paces in each slot and a max of 225 slots. There are 7 drawers total.

    I started an If formula with Right, Mid and Left but I cant seem to wrap my head around it.

    Any Help is much appreciated.

  • Re: alphanumeric code calculation with set limits


    for getting the number of slots use this formula =MID(A1,FIND("-",A1)+1,FIND("-",REPLACE(A1,FIND("-",A1),1,""))-FIND("-",A1))

    for getting the number of spaces in each slot use this formula =RIGHT(A1,LEN(A1)-FIND("-",REPLACE(A1,FIND("-",A1),1,""))-1)

  • Re: alphanumeric code calculation with set limits


    Great that almost works! I guess i didnt clarify that the drawers will change as well. A-G for seven drawers but these will only change after the 225 slots and the 4 space per slot. Soo after 900 then the next drawer will begin all over again.

    After I have all these codes. Should I just concantinate them or is it possible to combine the formulas in one? which ever is easiest.

    Appricate the help!