How to ignore blank cells when stringing cell values together

Scott Burry

Hi everyone!

Hoping someone can help me out. I have created a Smartsheet form that someone can fill out to create a custom URL. On the backend of the Smartsheet, I have a formula set up to string all the values together and add delimiters into a url string. It works great, with one exception.

All of the form fields are mandatory except for one. I need to add something into the function to remove a blank value from the final string if the cell for that value is blank.

My current set up is:

=([Website URL]11 + "?utm_medium=" + [Final Medium]11 + "&utm_source=" + [Final Source]11 + "&utm_campaign=" + Campaign11 + "&utm_term=" + Term11 + "&utm_content=" + Content11)

The field in question is "Term". So if the Term column has a blank value, I need it remove "&utm_term=" + Term11 from the final string.

Can someone help me out? Thanks!

Toufong Vang

Hi, Scott.

Give this a try...

=([Website URL]11 + "?utm_medium=" + [Final Medium]11 + "&utm_source=" + [Final Source]11 + "&utm_campaign=" + Campaign11 + (IF(ISBLANK(Term11),"", "&utm_term=" + Term11)) + "&utm_content=" + Content11)

In your formula you had "&utm_term=" + Term11 in between Campaign11 + and + "&utm_content="

Since you already have your logic defined, leave blank if Term11 is blank, form it between (...) and then insert that into your formula.

If term is blankISBLANK(Term11) then use empty "" else use "&utm_term=" + Term11

( IF( ISBLANK(Term11), "", "&utm_term=" + Term11) )

Toufong Vang

Hi, Scott.

Give this a try...

=([Website URL]11 + "?utm_medium=" + [Final Medium]11 + "&utm_source=" + [Final Source]11 + "&utm_campaign=" + Campaign11 + (IF(ISBLANK(Term11),"", "&utm_term=" + Term11)) + "&utm_content=" + Content11)

In your formula you had "&utm_term=" + Term11 in between Campaign11 + and + "&utm_content="

Since you already have your logic defined, leave blank if Term11 is blank, form it between (...) and then insert that into your formula.

If term is blankISBLANK(Term11) then use empty "" else use "&utm_term=" + Term11

( IF( ISBLANK(Term11), "", "&utm_term=" + Term11) )

Scott Burry

https://community.smartsheet.com/discussion/comment/294433#Comment_294433

Hi @TFC. This worked perfectly! Thanks so much for such a quick response!

Andrée Starå

Hi @Scott Burry

I hope you're well and safe!

To add to TFC's excellent advice/answer.

I'd recommend changing the row numbers to @row instead, so you don't have to think about the row numbers + you might be able to make it a so-called column formula.

Something like this.

=([Website URL]@row + "?utm_medium=" + [Final Medium]@row + "&utm_source=" + [Final Source]@row + "&utm_campaign=" + Campaign@row + (IF(ISBLANK(Term@row),"", "&utm_term=" + Term@row)) + "&utm_content=" + Content@row)

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!