Using Spreadsheet-style Formula to combine multiple variables into one

  • 13 February 2020
  • 1 reply

Userlevel 7
Badge +10

My team and I seem to run into this alot with CRMs that have multiple fields for email addresses. Take a look at this screenshot:

Screenshot 2020-02-13 18.52.19.pngWe're not 100% confident that the Home Email will always have an email address, yet in the next step we need an email address for this lead/contact. So here's the tip I use to solve for this issue.

Add a Formatter - Numbers - Spreadsheet-Style Formula step

Screenshot 2020-02-13 15.23.10.png

And I use the below formula, remember when dealing with strings or string values in Spreadsheet-style formula you need to surround the values with quotes.


In English, that formula says if mobile_email is empty, and home_email is empty use work_email, if mobile_email is empty but home_email is not use home_email, if mobile_email isn't empty use mobile_email.

In it's current version that formula doesn't test for if work_email is empty and all of the email variables are empty. In this case the output of this step would be "" (empty, null, etc) In that case we don't have a valid email address so you might want to put a path/filter in the next step so if the output of this formula is empty then it won't try to create a contact.

If you wanted to test the third variable for an empty state and replace it you could use this variation on the formula:

if("{{mobile_email}}"="",if("{{home_email}}"="",if("{{work_email}}"="","Value For all three empty","{{work_email}}"),"{{home_email}}"),"{{mobile_email}}")

Nested if statements can get unruly, but in general they're very powerful to use when you need to combine/replace/condense variables based on their values.

1 reply

Userlevel 7
Badge +12

This is a great tip, thanks Paul!