Hi,



I'm struggling here - Please can someone help me??

I am wanting to create a unique account name/number using both numbers and letters from two separate cells.

Cell C4 is the company Name.
Cell C36 is the initial contact date.

I want cell C2 to contain the "Account number" which is made up of the first 3 letters of the company name (in cell C4) and the dd/mm in cell C36

Eg - Company Name - Microsoft
Initial contact date - 26/09/2014
I want cell C2 to contain MIC2609

HOWEVER - if the company name begins with "The" I want to eliminate this so it will skip to the next word in the company name - eg,
Company Name - The Potting Shed
Initial Contact date - 26/09/2014
I want cell C2 to contain - POT2609

Can anyone please help me with a function or formula for this please?

Thank you