How to ignore blank cells when stringing cell values together

06/09/21
Accepted

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

  • Toufong VangToufong Vang ✭✭✭
    Accepted 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 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) )

Answers

  • Toufong VangToufong Vang ✭✭✭
    Accepted 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 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) )

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

  • Andrée Starå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=" + [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 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 PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Sign In or Register to comment.