How to ignore blank cells when stringing cell values together

Scott Burry
Scott Burry ✭✭
in Formulas and Functions

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!

Tags:
· Share on FacebookShare on Twitter

Best Answer

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

    · Share on FacebookShare on Twitter

Answers

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

    · Share on FacebookShare on Twitter
  • Scott Burry
    Scott Burry ✭✭
    https://community.smartsheet.com/discussion/comment/294433#Comment_294433

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

    · Share on FacebookShare on Twitter
  • 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=" + 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.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!