Create Client ID based on Customer Name, Company Name (No vowels, No Duplicates)

Dear Excel Experts,

I'd like to create a client ID based on three consonants of the name, company name and a value to account for duplicates eg. Name: Mikkel and Kasper, Company name: Spreadsheeto. The client ID returns: MKKSPR01

Ive been using:
=UPPER(LEFT([@Name];3))&UPPER(LEFT([@Company];3))
... for the example above, this would return: MIKSPR

(1) however I don't know how to return CONSONANTS ONLY
(2) I don't know how to remove "The" at the beginning of some company names
and (3) I don't know how to add a value at the end, (without excel removing the zero), and ensuring I am not getting duplicate entries with similar names

To clarify (3) above, if we added another name "Mikky", with company name "Spraypainters Limited", i would like the customer ID to return: MKKSPR02
(4) Its added the a two-digit value and accounted for the duplicate first 6 letters by adding 02 (not 01)

My final table would have columns for Name, Company name, Telephone, Email, etc as well as a column for Client ID


I appreciate any help on this matter

Best regards
Darren

Thank you so much this worked perfectly!

I appreciate the prompt response, its helped a great deal

Darrent,

Pleasure helping out.

As it does what you want, can you just click on "thread tools" at the top of the page and mark the thread as "solved"? Shows others looking for something similar that there is an answer.

Ochimus