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
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
-
=[Cell1] + " " + [Cell2] or =[Cell1] + "_" + [Cell2]
:-)
-
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?
-
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
-
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! -
See previous comment in this thread dated
September 20, 2016 8:08 am
Craig
-
Hi,
Thanks for that. I just updated the question for when/if you have a moment free!
-
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 processIn 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 dyestuffNow 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
-
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.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives