Combining If Statements?

Awright

How do I combine two If Statements? (adding + or a comma didn't work!)

I have a column Called "Client Executive" that I am trying to turn into an email address. The PROBLEM is that it's crossing from our database 2 different Ways. (1. FirstName LastName and then 2. LastName,FirstName). I have the formula for each way to convert it to an email, I just don't know how to write it to do both. If it has a comma, I want it to use that one....If it has a space, then I want it to use that one!

These are the two separate formulas- how do I combine them?

Angela Wright   =IF([Client Executive]@row <> "", LEFT([Client Executive]@row, FIND(" ", [Client Executive]@row) - 1) + "." + RIGHT([Client Executive]@row, LEN([Client Executive]@row) - FIND(" ", [Client Executive]@row)) + "@sodexo.com", "")

Wright,Angela   =IF([Client Executive]@row <> ",", RIGHT([Client Executive]@row, LEN([Client Executive]@row) - FIND(",", [Client Executive]@row)) + "." + LEFT([Client Executive]@row, FIND(",", [Client Executive]@row) - 1) + "@sodexo.com", "")

TVang

Hi, @Awright ,

To combine the two methods...

Z = IF( FIND(",", [Client Executive]@row) <> 0, comma_method , space_method )

And then you'll want to account for when the field is blank..

IF([Client Executive]@row = "", "", Z )

You can use SUBSTITUTE() when the name is like "Angela Wright".

SUBSTITUTE([Client Executive]@row, " ", ".") + "@sodexo.com"

Using MID() will shorten the expression when the name is like "Wright,Angela".

MID([Client Executive]@row, FIND(",", [Client Executive]@row) + 1, 50) to retrieve the first name.

MID([Client Executive]@row, 1, FIND(",", [Client Executive]@row) - 1) to retrieve the last name.

Hope this is helpful!

.

Your comma_method:

RIGHT([Client Executive]@row, LEN([Client Executive]@row) - FIND(",", [Client Executive]@row)) + "." + LEFT([Client Executive]@row, FIND(",", [Client Executive]@row) - 1) + "@sodexo.com"

Your space_method:

LEFT([Client Executive]@row, FIND(" ", [Client Executive]@row) - 1) + "." + RIGHT([Client Executive]@row, LEN([Client Executive]@row) - FIND(" ", [Client Executive]@row)) + "@sodexo.com"