# Combining If Statements?

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", "")

• ✭✭✭✭✭

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.

.

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

```LEFT([Client Executive]@row, FIND(" ", [Client Executive]@row) - 1) + "." + RIGHT([Client Executive]@row, LEN([Client Executive]@row) - FIND(" ", [Client Executive]@row)) + "@sodexo.com"
```
• ✭✭✭✭✭✭
edited 03/15/24

I like starting out the complicated formulas by breaking them apart. For your case, I input for formulas in the "Space" and "Comma" columns I'd created.

=IF(CONTAINS(" ", [Client Executive]@row), Space@row, Comma@row)

Then it's just a matter of replacing our Space@row and Comma@row variables with your formulas:

=IF(CONTAINS(" ", [Client Executive]@row), 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", ""), 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", ""))

Jason Tarpinian - Sevan Technology

Smartsheet Aligned Partner

• @Jason Tarpinian that worked!!! You're a genius! Any way to incorporate something that if the Client Executive Field is blank that it won't produce an #invalid Operations?

• ✭✭✭✭✭✭

Just nestle in another IF statement at the beginning to check if it's a blank:

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

Jason Tarpinian - Sevan Technology

Smartsheet Aligned Partner

• edited 03/19/24

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!