Glenn wrote:
mklafert wrote:
I am going to create customer ID - Alphanumeric. I have the whole
company names and I was trying to come up with a scheme that I can use
an Excel Function to make the process faster. I thought about Len but
not sure - Is there a command where I can say give me Say the first
letter of the company name and numbers behind it.

Such as
American Can Company - Result of Function - Ameri125

If this is even possible


Assuming you have your list of company names starting in A1 and some
open columns to work with, try this:

B1
=" "&TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(A1,"-"," "),","," "),"."," ")," "," "))&REPT(" ",4)


Forgot to mention that in the SUBSTITUTE formula you should put in whatever
separators that are found in your list of companies. I included a dash ("-"),
period ("."), comma (",") and a space (" "), the last of which of course had no
effect because it was being replaced by a space.



C1
=TRIM(MID(B1,FIND("~",SUBSTITUTE(B1," ","~",1))+1,1)&
MID(B1,FIND("~",SUBSTITUTE(B1," ","~",2))+1,1)&
MID(B1,FIND("~",SUBSTITUTE(B1," ","~",3))+1,1)&
MID(B1,FIND("~",SUBSTITUTE(B1," ","~",4))+1,1)&
MID(B1,FIND("~",SUBSTITUTE(B1," ","~",5))+1,1))

D1
=IF(LEN(C1)=1,LEFT(TRIM(A1),3),IF(LEN(C1)=2,LEFT(A 1,2)&RIGHT(C1,1),C1))

E1
=TEXT(COUNTIF($D$1:D1,D1),"000")

F1
=UPPER(D1)&E1

Copy B1:F1 down to the end of your list of companies. Column F will
contain unique alphanumeric company ID's with three to five letters,
followed by a three digit number. Copy / Paste Special / Values on
column F, then delete columns B through E.