extra space and a comma to remove

I'm using this formula to combine Last, First Names with the Middle name initial (if it exists) and the credentials:

=[Last Name]@row + ", " + [First Name]@row + " " + [Middle Initial]@row + ", " + [Degree(s)]@row


If there is a Middle name initial, there is no problem and it displays fine. However, if there is no initial, I get the extra space and a comma. Is it possible to make this formula conditional and ONLY display the extra space and comma when there is an initial?


Thank you


Answers

  • James Hokamp
    James Hokamp ✭✭✭
    edited 07/24/23

    Hi @OshaK,


    You should be able to do this with an IF statement.




    Let me know if this helps!


    James

  • OshaK
    OshaK ✭✭✭✭

    thank you so much, @James Hokamp! Any chance you can copy/paste the formula to this conversation? Also, I'm sorry, I need one more condition for the Degrees column because not everyone has the degrees listed. So the commas and spaces should only appear if there is either an initial or degrees or both, and no spaces and commas if none is present, and a single comma, space if one of them is present.

    Thank you!

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

    Hi @OshaK

    I hope you're well and safe!

    We could use JOIN COLLECT, but would it work if we moved the columns around so they would be?

    Last Name, First Name, Middle Initial, Degree(s)

    Is that an option?

    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, Awesome, 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.

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 07/24/23

    @Andrée Starå

    A substitute join would be cleaner i think.

    if the columns are arranged as you said, Last Name, First Name, Middle Initial, Degree(s)

    =substitute(join([Last Name]@row:[Degree(s)]@row,", "),", ,",",")

    vs

    =join(collect([Last Name]@row:[Degree(s)]@row,[Last Name]@row:[Degree(s)]@row,not(isblank(@cell))),", ")

  • OshaK
    OshaK ✭✭✭✭

    this is great, thank you, @Andrée Starå , it's perfect!

  • OshaK
    OshaK ✭✭✭✭

    @Andrée Starå, thank you again, it works great, except when there is no initial nor degrees.

    When there are just last and first names, there is an extra comma. Is there a way to remove it automatically if there are only last and first names?

    so instead of

    Doe, Osha,

    can it be

    Doe, Osha

    ?


    thank you!

  • L_123
    L_123 ✭✭✭✭✭✭

    Then @Andrée Starå's initial suggestion would be optimal. I assumed you were forcing the user to enter those values.

    =join(collect([Last Name]@row:[Degree(s)]@row,[Last Name]@row:[Degree(s)]@row,not(isblank(@cell))),", ")

  • OshaK
    OshaK ✭✭✭✭

    @L_123 thank you, that works! How would I develop this formula further so I can have a break line?

    I need it to look like that:


    Last Name, First Name

    Degree 1, Degree2

    Degree 3


    (I need it to generate the document, where the formatting is important. Thank you!

  • L_123
    L_123 ✭✭✭✭✭✭

    you need to separate it out into 3 separate statements and use char(10) between them

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!