Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Concatenate two cells

Options

Hi,

 

I need to concatenate data of two cell into one cell. I just use the formula "=[Cell1] + [Cell2])", which worked fine and gave the result "Cell1Cell2", however I need to have space or underscore between values of two cell like, "Cell1_Cell2" or "Cell1 Cell2".

 

My practical requirement is:

 

I need to concatenate "date" and "Job No." to give the result as "Batch No."

 

"08/17/16" and "WTL-2" to give result "08/17/17_WTL-2" and what am I getting now is, "08/17/16WTL-2"

 

Thank you.

Comments

  • Mads Liebst
    Options

    =[Cell1] + " " + [Cell2] or =[Cell1] + "_" + [Cell2]

     

    :-)

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    If the cells are contiguous or a collection*, then you can also use the new** JOIN() function.

     

    =JOIN([Column1]23:[Column2]23,"_") for two cells side by side

    or

    =JOIN([Column1]22:[Column1]23,"_") for two cells in adjacent rows.

     

    * A collection is a set of cells returned by formulas like CHILDREN() or ANCESTORS()

    ** new as of Aug 8th Smartsheet update

     

    Craig

  • I have a sheet with form that I need to display two columns of data in the calendar view.  My work around is to make the primary column (the one that displays in calendar view) the column with concatenated data using this formula - =JOIN([Organization Hosting Screening]1 + " " + [Location of Screening]1).  My issue is this.. how do i automate this field everytime  a form is filled out so I don't have to manage the sheet and manually enter/copy the formula into each new entry?

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    Are Forms the only way new rows are added? 

    Are they being added to the top or bottom (or both)?

    Auto-fill may have its quirks, but once the kinks are worked out, it seems to keep working (others report varying degrees of that)

    https://help.smartsheet.com/articles/1641473-auto-filling-formulas-and-formatting

    Craig

     

  • NeilGreenfield
    edited 06/11/18
    Options

    Hi there,

     

    New user here so apologies if this is obvious (to you!) but I'm trying to get the title of two fields to display on the Gantt and understand it's 'concaternate' but where am I typing this formula? I just get 'unparseable'!

     

    Edit - Ah ha! I've done it! (Needed the '1' after each cell. So now my question is how do I add a space, comma or dash between those fields?  Currently it looks like this;



    Task NameNeil



    And I'd like it to look like;



    Task Name - Neil



    Thanks!

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    See previous comment in this thread dated 

    September 20, 2016 8:08 am

    Craig

  • NeilGreenfield
    Options

    Hi,

     

    Thanks for that. I just updated the question for when/if you have a moment free!

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    Syntax for JOIN is 

    JOIN(range, delimiter)

    so

    The delimiter is the what separates the values being joined.

    =JOIN([Column1]22:[Column1]23," delimiter does not need to be a single character ") 

    would display

    A delimiter does not need to be a single character B

    if [Column1]22 was "A"

    and [Column1]23 was "B"

    (that's two cell vertically grouped)

    Craig

     

  • Hello

    could you please help, I would like to know how can I pull info from the children to a single cell

    -Project for Developments

      Requested yarn to Jim

      Asking for dyestuff

      run trial in finishing process

    In this case I would to have a cell showing this

    "Requested yarn to Jim

    Asking for dyestuff"

    and in case I add a new task just below the Parent something like

    -Project for Developments

      receive confirmation

      Requested yarn to Jim

      Asking for dyestuff

    Now in this case I would like to update automatic as

    "receive confirmation

      Requested yarn to Jim"

    Is there any way to do it?

    thanks in advance

  • Des P
    Des P
    edited 04/18/19
    Options

    Q1: This may be a naive question but is there a way to nest Join statements?

    I am looking to concatenate information within two separate columns into a single cell (combining the 2 formulas below).  

    =JOIN([Notes/Comments]174:[Notes/Comments]179, ";")

    =JOIN([Actual Start Date]185:[Actual Start Date]187, ";")

     

    Q2: For =JOIN([Actual Start Date]185:[Actual Start Date]187, ";") I am getting this as the result: 30/06/19 8:00 AM;23/04/19 8:00 AM;24/04/19 7:59 AM

    Is there a way to eliminate the listed time (I am only interested in joining the dates)?

     

    UPDATE: I changed the formula to DATEONLY([Actual Start Date]182) + ";" + DATEONLY([Actual Start Date]183) + ";" + DATEONLY([Actual Start Date]184) and got the desired result.

This discussion has been closed.