SmartSheet: Formulas to Concatenate Two or More Cells (Cheat Sheet)
Concatenate Two Text Cells
This formula concatenates the First Name in row 1 with the Last Name in row 1, and adds a space between them:
=[First Name]1 + " " + [Last Name]1
You can replace the space with any other text, for example:
=[First Name]1 + " --- " + [Last Name]1
You can also remove the space:
=[First Name]1 + [Last Name]1
Concatenate Multiple Text Cells
This formula expands the previous formula to concatenate more cells:
=[First Name]1 + " " + [Middle Name]1 + " " + [Last Name]1 + " " + [Address]1
Alternatively, you can use JOIN, which lets you concatenate multiple cells, with optional text between the values:
=JOIN([First Name]1:[Address]1, " ")
You can replace the space with whatever you want, or you can remove it:
=JOIN([First Name]1:[Address]1)
Concatenating Text Cells with Line Breaks
You can add a line break between two cells by using CHAR(10):
=[First Name]1 + CHAR(10) + [Last Name]1
CHAR(10) also works with JOIN:
=JOIN([First Name]1:[Address]1, CHAR(10))
Concatenate Number Cells
If [CustomerID]1 is 100 and [CompanyID]1 is 200, this formula gives us "100200":
=[CustomerID]1 + "" + [CompanyID]1
This formula removes the space so SmartSheet treats the cells as numbers and adds them together. The result is "300":
=[CustomerID]1 + [CompanyID]1
Concatenate Date Cells
You can use "+" or JOIN to concatenate DATE cells. DATE cells contain a date (e.g. 01/01/2024) and a time (e.g. 12:00 AM). If you want to concatenate only the date and exclude the time, you can use DATEONLY.
=DATEONLY([Birth Date]1) + " " + DATEONLY([Birth Date]2)
Additional Resources
