Generate alphanumeric unique 4 digit values from 12 digit values

[Guest]

I'm trying to convert 12 digit strings to individually unique 4 digit strings
for product coding. Any ideas?

 

[Niek Otten]

Can you give an example of what you're trying to do?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| I'm trying to convert 12 digit strings to individually unique 4 digit strings
| for product coding. Any ideas?

 

[Jason Lepack]

I'm trying to convert 12 digit strings to individually unique 4 digit strings
for product coding. Any ideas?

Examples required.

1) Insert a column
2) In the first row put '0000
3) In the second row put '0001
4) Fill down to your hearts content
5) You now have 10000 unique 4 digit ids

 

[Bernd]

12 digits are 10^12 = 1,000,000,000,000 possibilities.

4 alphadigits are 36^4 = 1,679,616 = a different alternatives.
1,679,627 = b is the next higher prime number. If you have less than a
articles you can define a table with b elements and convert your 12
digit strings as follows:
c = 12_digit_string mod b
if c not taken then c is new product code: table[c] = 12_digit_string
if c already taken then increase c until table[] is empty: set table[c
+i] = 12_digit_string
(if c+i >= b then start over with 1...)
finally convert the index c or c+i to 4 digit string (base 36)

If 12_digit_string mod b will cluster your idents to much then take
another function.

Regards,
Bernd

 

[Bernie Deitrick]

Mike,

If your 12 digit strings are in column A, starting in Row 2, then in cell B2 use the array formula
(entered with Ctrl-Shift-Enter)

=IF(COUNTIF($A$1:A2,A2)>1,VLOOKUP(A2,$A$1:$B2,2,FALSE),TEXT(MAX(VALUE($B$1:B1))+1,"0000"))

and copy down to match your list in column A. It will work, possibly slowly depending on how big
your list is...

HTH,
Bernie
MS Excel MVP

 

[Gord Dibben]

Data>Text to Columns>Fixed Width>Next. Click after every 4th number to draw a
break line.

The Next>Column Data Format>Text for each column(in case a 4-digit number has a
0 as first digit.

Finish.


Gord Dibben MS Excel MVP