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

Please post a sample sheet - Thx

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

This formula works kind of:

=UPPER(IF(LEFT(D4,4)="THE ",MID(D4,5,3),LEFT(D4,3)))&DAY(D36)&MONTH(D36)

except it only displays one digit if the day or month is less than 10. This can be worked around with a few ifs:

=UPPER(IF(LEFT(D4,4)="THE ",MID(D4,5,3),LEFT(D4,3)))&IF(DAY(D36)<10,"0","")& DAY(D36)&IF(MONTH(D36)<10,"0","")&MONTH(D36)

Thanks gebobs,

I had to change it to reference cell C3 and C36 but it works perfectly! Thank you so much, you're a life saver!!!!