Hi all, I would like to set a formula to join data 1,2,3 but something is wrong as below picture.

Could you help me out pls? the main purpose is to join data from upper 1-3 without "/" Redundant in case there is no data at any cell among upper 1, 2, 3.



Answers

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭

    Hi Kath, may I clarify the request?

    Are you looking to put a '/' between the words rather than a colon, comma or dash, in the hopes of creating a date? It may be a case of use the dash and formatting the cell to be a date?

    Apologies if I've misunderstood the request.

    For those interested, check out more here (JOIN Function | Smartsheet Learning Center) and this previous answer here (JOIN COLLECT formula — Smartsheet Community)

    Hope this helps and that you have a great day,

    Jason

  • hi Jason,

    Thanks for your quick response. I need to join data from non-continuous columns and put "/" between them. I dont know how to join them correctly.

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭

    Hi Kath, what happens when changing the "/" to a "-"?

    I know you're not after a dash; just looking to confirm the formula works with a dash instead of a slash, as the slash in my experience can sometimes be misinterpreted by formulas to be a division function.

    Also, from my experience, asking ISBLANK to look at a range of cells, rather than each cell may be an issue. I'd try something like IF(OR(ISBLANK(cell1), ISBLANK(cell2), ISBLANK(cell3)), "", JOIN(... etc...

    Hope this helps.

    Hope this helps and that you have a great day,

    Jason

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Kath Dang

    I hope you're well and safe!

    To add to Jason's excellent advice/answer.

    Try something like this for your example.

    =JOIN(COLLECT([Upper Material Type 1]@row:[Upper Material Type 3]@row, 
    [Upper Material Type 1]@row:[Upper Material Type 3]@row, <>""), " / ")
    

    Did that work/help?

    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:[email protected] | 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!