M
mikep
I'm trying to convert 12 digit strings to individually unique 4 digit strings
for product coding. Any ideas?
for product coding. Any ideas?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
N
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?
--
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?
J
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
B
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
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
B
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
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
G
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
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