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

Answers

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭

    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"
    
  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭
    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?

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!