quickest way to create vendor IDs manually

[ryanb.]

I have a list of vendor names that I would like to convert to Vendor IDs
I would like the vendor ID to be the first 4 characters of the vendor
name (spaces ignored) followed by 3 digits starting at 001. for example:

If we have 5 vendors where the first four characters in their names are
THER (Thermafiber, Therm all, Thermal Systems, etc.). I would like
to be able to have a formula automatically assign them vendor IDs
THER001, THER002, THER003, etc. I am having trouble ignoring spaces
(eg 3M Company would be 3MCO001) and auto numbering.

Can anyone get me started this is what I have in column C:
=LEFT(B2,4)&TEXT(COUNTA(B$2:B2),"00#")

this does not ignore spaces, and the numbers do not reset at 1 for each
unique 4 character sequence.

Column B contains the Vendors names in alpha order

TIA,

 

[ryanb.]

ok... I took care of the spaces.. but the numbering i am still working on.

here is what I have:
=LEFT(SUBSTITUTE(B2," ",""),4)&TEXT(COUNTA(B$2:B2),"00#")

 

[ryanb.]

I added a helper column in C that performs the left(substitute()) function.
here is what I now have and it seems to work... is this the most efficient
method?

=LEFT(SUBSTITUTE(B2," ",""),4)&TEXT(COUNTIF(C$2:C2,(LEFT(SUBSTITUTE(B2,"
",""),4))),"00#")