Help needed on IF and CONCATENATE formula

[Hrishikesh Sandilya]

Dear Members,

I am trying to use a combination of Concatenate and IF formula to produce an email.

My input.

D4: First Name:
D5: Middle Name:
D6: Last Name:

D8: Organization:

Once all these are filled, I want the formula to produce a result like

FirstName.M.LastName@Organization.com

I have used the following formula.

=CONCATENATE(D4,".",IF(D5<>" ",CONCATENATE(LEFT(D5,1),".",D6),D6),"@",D8)

Problem is if there is a middle name the formula works fine, but in case where there is no middle name, it produces the following result.

FirstName..LastName@Organization.com

Please advice how do I remove the additional (.) in cases where there is no middle name.

Please help.

Regards

 

[Swapnil]

=CONCATENATE(D4,".",IF(D5="",D6,CONCATENATE(LEFT(D5,1),".",D6)),"@",D8)

Use this formula

Its the same as you posted , You were getting the wrong result becaise you were checking for " " in middle name , ideally when its blank it has to be checked for ""

 

[Hrishikesh Sandilya]

Thank You Swapnil.

Your help is really appreciated.

 

[Swapnil]

you are welcome

 

[shrivallabha]

One more alternative would be:
=SUBSTITUTE(CONCATENATE(D4,".",D5,".",D6,"@",D8),"..",".")