How to ignore blank cells when stringing cell values together
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!
Best Answer
-
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 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 blank
ISBLANK(Term11)
then use empty""
else use"&utm_term=" + Term11
( IF( ISBLANK(Term11), "", "&utm_term=" + Term11) )
Answers
-
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 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 blank
ISBLANK(Term11)
then use empty""
else use"&utm_term=" + Term11
( IF( ISBLANK(Term11), "", "&utm_term=" + Term11) )
-
Hi @TFC. This worked perfectly! Thanks so much for such a quick response!
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.2K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 142 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!