How to ignore blank cells when stringing cell values together

Scott Burry · 06/09/21

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 · 06/09/21

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=" + Term11in betweenCampaign11 +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 · 06/09/21

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=" + Term11in betweenCampaign11 +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 · 06/09/21

Hi, Scott.<\/p>

Give this a try...<\/p>

=([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)<\/code><\/p>

In your formula you had "&utm_term=" + Term11<\/code> in between Campaign11 +<\/code> and + "&utm_content="<\/code><\/p>

Since you already have your logic defined, leave blank if Term11 is blank<\/em>, form it between (...)<\/strong> and then insert that into your formula.<\/p>

If term is blankISBLANK(Term11)<\/code> then use empty ""<\/code> else use "&utm_term=" + Term11<\/code> <\/p>

( IF( ISBLANK(Term11), "", "&utm_term=" + Term11) )<\/code><\/p>","bodyRaw":"[{\"insert\":\"Hi, Scott.\\nGive this a try...\\n\"},{\"attributes\":{\"code\":true},\"insert\":\"=([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)\"},{\"insert\":\"\\nIn your formula you had \"},{\"attributes\":{\"code\":true},\"insert\":\"\\\"&utm_term=\\\" + Term11\"},{\"insert\":\" in between \"},{\"attributes\":{\"code\":true},\"insert\":\" Campaign11 +\"},{\"insert\":\" and \"},{\"attributes\":{\"code\":true},\"insert\":\"+ \\\"&utm_content=\\\"//www.santa-greenland.com/community/discussion/comment/\"},{\"insert\":\"\\nSince you already have your logic defined,\"},{\"attributes\":{\"italic\":true},\"insert\":\" leave blank if Term11 is blank\"},{\"insert\":\", form it between \"},{\"attributes\":{\"bold\":true},\"insert\":\"(...)\"},{\"insert\":\" and then insert that into your formula.\\nIf term is blank\"},{\"attributes\":{\"code\":true},\"insert\":\"ISBLANK(Term11)\"},{\"insert\":\" then use empty \"},{\"attributes\":{\"code\":true},\"insert\":\"\\\"\\\"//www.santa-greenland.com/community/discussion/comment/\"},{\"insert\":\" else use \"},{\"attributes\":{\"code\":true},\"insert\":\"\\\"&utm_term=\\\" + Term11\"},{\"insert\":\" \\n\"},{\"attributes\":{\"code\":true},\"insert\":\"( IF( ISBLANK(Term11), \\\"\\\", \\\"&utm_term=\\\" + Term11) )\"},{\"insert\":\"\\n\\n\"}]","format":"rich","dateInserted":"2021-06-09T22:35:27+00:00","insertUser":{"userID":136737,"name":"Toufong Vang","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Toufong%20Vang","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/n65S9PRP4PA0M.png","dateLastActive":"2023-06-28T16:10:01+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/294433#Comment_294433","embedType":"quote"}"> 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å · 06/10/21

Hi@Scott Burry

I hope you're well and safe!

增加交通的优秀建议/答案.

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=" +[email protected]+ (IF(ISBLANK([email protected]),"", "&utm_term=" +[email protected])) + "&utm_content=" +[email protected])

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 bymarking 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!

How to ignore blank cells when stringing cell values together

Best Answer

Answers

Help Article Resources

Categories