Create Unique Numeric String from Alphanumeric String

[zzer0]

Hello all,

I need to create a unique 5 digit code that is generated from a 4 digit alphanumeric string. See below for an example.

4 Digit Alphanumeric Code	5 Digit Code
ABBO	10001​
ABEL	10002​
ABER	10003​
ABRA	10004​
ADAD	10005​
ADAD	10005​
ADAD	10005​
ADAD	10005​
ADAM	10006​
ADAM	10006​
ADAM	10006​
ADLE	10007​
ADLE	10007​
ADST	10008​
ADST	10008​
AHRE	10009​
AHRE	10010​
AICF	10011​

The 5 digit code could be any number, as long as it is unique based on the 4 digit input. I thought about assigning numbers to letters but the numbers get too large i.e. ZZZZ would be 26262626. I have over a thousand unique 4 digit codes and I'd prefer not to have to go one by one assigning them 5 digit codes manually. Any guidance is greatly appreciated!

 

[Scott Huish]

Why don't you just number them sequentially like you did in your example?

 

[zzer0]

Why don't you just number them sequentially like you did in your example?

Hi Scott,

I could for sure, but because I have over 2000 4 digit codes it would be tedious to do it manually when a formula might be able to do the trick. I'm not opposed to doing it manually but I figured I'd ask here first.

 

[Joe4]

It looks like you only want new codes for new numbers.
Really simple solution.

Sort your 4 Digit Alphanumeric Codes in alphabetical order.
Then, place "10001" on the first row (let's say cell B2).
Then in cell B3, enter this formula and copy down for all rows:

=IF(A3=A2,B2,B2+1)

Your results will look like this (formulas in red):

 

[Scott Huish]

I wasn't actually suggesting you do it manually, I was just asking why they couldn't go sequentially.

 

[zzer0]

It looks like you only want new codes for new numbers.
Really simple solution.

Sort your 4 Digit Alphanumeric Codes in alphabetical order.
Then, place "10001" on the first row (let's say cell B2).
Then in cell B3, enter this formula and copy down for all rows:

=IF(A3=A2,B2,B2+1)

Your results will look like this (formulas in red):

View attachment 104747

Thanks, Joe4!

Appreciate your help, this is a really simple solution, I'm ashamed I didn't think of it first. Appreciate your help.

 

[Joe4]

You are welcome.
Glad we were able to help!