Creating a 4-digit alphanumeric code string in Excel (two variations)

I've been trying to figure out how to make a 4-digit alphanumeric code in excel to associate with certain promo/discount codes in our system. I'm looking for the code to be a code string, not random generators, because we can only use the code once, so I don't want any duplicates. The order I was looking to have is: a0a0, a0a1, a0a2, ..., a0a9, a1a0, a1a1, ..., a9a9, a0b0, a0b1, ... So the first/third digits are letters and the second/fourth digits are numbers.

The second variation I was looking to make was the same as above but have any combination of letters and numbers (as long as their is at least 1 letter, so it's not a 4 number code).

I'm assuming the best way to do this is with the VBA, but I'm not as comfortable with the VBA portion of Excel as I am with other areas.

Thanks for your help!

Put this formula in A1:

=IF(ROWS($1:1)>26*26*100,"",CHAR(97+INT((ROWS($1:1)-1)/2600))&LEFT(TEXT(MOD(ROWS($1:1)-1,100),"00"))&CHAR(97+MOD(INT((ROWS($1:1)-1)/100),26))&RIGHT(MOD(ROWS($1:1)-1,100)))

then copy down as required. It will produce your sequence up to z9z9 in A67600, and then produce blanks so that the codes are unique.

Hope this helps.

Pete

Pete,

This worked perfect, thank you!

Is there a way to adjust this formula to have a 4-digit alphanumeric code that has at least one letter but where order doesn't matter? They would still need to be unique.

I'm just wondering in case we reach enough uses that we have to expand out of the current, two letter two number combination.

Thanks again!

Mark

I'm not really sure what you mean, and 67,000+ codes is quite a lot !

You could have a sequence which goes a000 to z999 (i.e. 26000 codes), and then if the letter can be in any position (i.e. 0a00 to 9z99 followed by 00a0 to 99z9 and 000a to 999z) you will have 4 times as many unique codes.

If you had aa00 to zz99, this is effectively the same as you have now (67600 codes), which can be multiplied by 6 if the two letters can be in any position, i.e. aa00, a0a0, a00a, 0aa0, 0a0a, and 00aa, so that means you could have 405600 unique codes.

If you had the pattern aaa0 to zzz9, this will give you 175760 unique codes, with 4 times as many if the digit could be in any position.

However, each of these patterns would need their own formula, and could result in a massively complex formula.

Hope this helps.

Pete

That definitely makes sense. These codes should last us a long time but I just wanted to be thinking ahead so I knew what I would need to do just in case we would need to have more codes in the future. I appreciate your help!

Okay, if you look at the formula that I gave you, you will see that you basically have a:

CHAR_function & LEFT_function & CHAR_function & RIGHT_function

so you can switch these around to give you different combinations, i.e. aa00 to zz99, and a00a to z99z and 00aa to 99zz and so on, to give you 6 times as many unique codes. Do this each time in the next available cell, i.e. A67601 for the second set, but ensure that you use ROWS($1:1) for the first cell in each set.

Another approach would be to list all the characters you want to use in a column somewhere, e.g. 0 to 9 and a to z in cells X1 to X36, and to pick each character uniquely 4 times. This would potentially give you 36 * 36 * 36 * 36 different codes, i.e. 16,719,616, which is more than Excel can cope with. The formula to do this in A1 is:

=INDEX(X:X,MOD(INT((ROWS($1:1)-1)/36/36/36),36)+1)&INDEX(X:X,MOD(INT((ROWS($1:1)-1)/36/36),36)+1)&INDEX(X:X,MOD(INT((ROWS($1:1)-1)/36),36)+1)&INDEX(X:X,MOD(ROWS($1:1)-1,36)+1)

However, some of the combinations are not valid, as you would have 10,000 codes which are purely numbers, and other codes which are purely alpha characters. You could avoid these codes by means of IF statements, returning a blank instead, but then your codes would not be in consecutive columns, and the formula would become much more complex.

Anyway, something to think about ...

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you may like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Hope this helps.

Pete

Thank you so much Pete. I really appreciate all of your help this is exactly what I was looking for!

see attached file
I would like to make all combinations possible

I tried to use your formula but I suppose there is a problem

thanks for your support