Append dynamic number to a cell

**ReplicaR** · 08-07-2012, 01:35 PM

I'm create a unique id for our customer by using first 4 letters of the customer name, and then auto-assigning a 4 digit number after that. I've got the first part down, but I'm kinda stumped when it comes to the dynamic number assignment. The idea is that I would like to assign a 4 digit number after the 4 letters, starting with 0001, and increasing by one if the cell already exists. Here is an example of what I would like to do.

Customer
20th Century
Time Warner
Time Magazine
Timex
Zoo Productions

CustomerID:
20TH0001
TIME0001
TIME0002
TIME0003
ZOOP0001

Hope someone can help with this. I've got 7500 rows of data, really don't want to do this by hand.

**JBeaucaire** · 08-07-2012, 03:21 PM

Based on that, assuming the customer name is column A, then this formula in column B, then copied down:

=UPPER(LEFT($A2,4) & TEXT(COUNTIF($A$2:$A2,LEFT($A2,4)&"*"), "0000"))

Afterwards, copy column B, then do a PASTE SPECIAL > VALUES to remove the formulas and leave the codes behind.

EDIT: Use Jason's version below, it covered the spaces which I forgot.

**DBY** · 08-07-2012, 03:30 PM

Sorry Jerry I didn't realize you'd replied.

Your solution seems better so I've removed mine.

DBY

**jason.b75** · 08-07-2012, 03:33 PM

Allowing for spaces in the first 4 characters of the customer name

=UPPER(LEFT(SUBSTITUTE(A2," ",""),4))&TEXT(1+COUNTIF(B$1:B1,LEFT(SUBSTITUTE(A2," ",""),4)&"*"), "0000")