Concatenate including a "return/entder"

Baris Meretuk
Baris Meretuk ✭✭
edited 12/09/19 in Smartsheet Basics

Dear Smartsheet Community,

 

Do you know how to add a "return or enter" into the concatenate formula?

=[Column1]1 + " " + [Column2]2 + "[return/enter]" + [Column3]3

="John" + " " + "Smith" + "[return/enter]"+ "35"

to display:

John Smith

35

 

Thanks for you support.

Best,

Baris

 

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 02/19/19

    Hi Baris,

    Try this.

    Add a helper (text/number) column named JoinName or similar with the formula below:

    =JOIN([Column1]@row:[Column2]@row; " ")    

    The same version but with the below changes for your and others convenience.  

    =JOIN([Column1]@row:[Column2]@row, " ")

    Add another helper (text/number) column called Enter/Return or similar and add the following to the first cell in the column. Add a hyphen, carriage return (CMD+Enter on Mac) and a hyphen

    Should look something like - - and like below if you have Wrap active. 

    -

    -

    Add the below formula to the cell where you want the text to be displayed:

    =JOIN(JoinName@row:[Column3]@row; SUBSTITUTE([Enter/Return]$1; "-"; ""))  

    The same version but with the below changes for your and others convenience.  

    =JOIN(JoinName@row:[Column3]@row, SUBSTITUTE([Enter/Return]$1, "-", ""))

    The result should be something like:

    John Smith

    35

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Did it work?

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    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.

  • Baris Meretuk
    Baris Meretuk ✭✭
    edited 02/21/19

    Hi Andrée, 

    Thanks you very much, so simple but efficient :)

    Much appreciated,

    Baris

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

    I'm always happy to help!

    Best,

    Andrée

    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.

  • This is a great solution.

    Another one:

    You can use CHAR(10) add a line break within a formula.

    ="Apples" + CHAR(10) + "Oranges"
    

    However, neither solution works with update requests. We add an opening paragraph outlining the reason for the update request, and reference columns in the Smartsheet. (This is good, as it can't be edited by the recipient) However, the lack of word wrap in these emails makes them look atrocious. WORK AROUND: We add a new column for each paragraph in the opening letter, and include all columns. Not pretty, but it works.