Concatenate two cells

Deepak Adhikary

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.

Mads Liebst

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

 

:-)

J. Craig Williams

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

Laurie Schneider

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

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

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

See previous comment in this thread dated 

September 20, 2016 8:08 am

Craig

NeilGreenfield

Hi,

 

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

J. Craig Williams

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

 

Cesar Maradiaga

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

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.