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
-
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"
-
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
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!