 # 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!