How to Concatenate Cells in Smartsheet: Complete Guide with Formulas

TL;DR

• Use the + operator to concatenate text cells: =[First Name]1 + " " + [Last Name]1
• JOIN function works for multiple cells: =JOIN([First Name]1:[Last Name]1, " ")
• Add line breaks with CHAR(10): =[First Name]1 + CHAR(10) + [Last Name]1
• Number cells concatenate differently - add empty quotes to treat as text
• Use DATEONLY() for date cells to exclude time values

Steps

1. Concatenate Two Text Cells with Space

   • Click on the destination cell where you want the combined result
   • Enter formula: =[First Name]1 + " " + [Last Name]1
   • Replace column names and row numbers with your actual column references
   • Press Enter to execute the formula

2. Concatenate Multiple Cells Using JOIN

   • Use JOIN for three or more cells: =JOIN([First Name]1:[Address]1, " ")
   • The range [First Name]1:[Address]1 includes all columns between First Name and Address
   • Change the separator (space) to any text you want between values
   • Remove separator entirely: =JOIN([First Name]1:[Address]1)

3. Add Line Breaks Between Concatenated Values

   • Use CHAR(10) for line breaks: =[First Name]1 + CHAR(10) + [Last Name]1
   • Combine with JOIN: =JOIN([First Name]1:[Address]1, CHAR(10))
   • Ensure cell formatting allows text wrapping to see line breaks

4. Handle Numbers and Dates Properly

   • For numbers as text: =[CustomerID]1 + "" + [CompanyID]1
   • For mathematical addition: =[CustomerID]1 + [CompanyID]1 (no quotes)
   • For dates without time: =DATEONLY([Birth Date]1) + " " + DATEONLY([Birth Date]2)

Pitfalls & Fixes

• Pitfall: Concatenated numbers are being added mathematically instead of joined as text

  • Fix: Add empty quotes between numbers: `=[CustomerID]1 + "" +

[CompanyID]1` to force text concatenation

• Pitfall: Date cells showing unwanted time values (12:00 AM) in concatenated result

  • Fix: Use DATEONLY() function: `=DATEONLY([Birth Date]1) + " " +

DATEONLY([Birth Date]2)`

• Pitfall: JOIN function not working with mixed column types

  • Fix: Ensure all columns in the range contain compatible data types, or convert them individually before joining

• Pitfall: Line breaks not appearing in concatenated text

  • Fix: Enable text wrapping in the cell formatting options to display CHAR(10) line breaks properly

Related Tasks

• Fix Text Capitalization Across Entire Sheets
• Copy Entire Columns Between Sheets in Seconds
• Spell Check Faster Than Any Other Tool

Stop Wasting Time on Manual Text Formatting

Manual concatenation works for occasional use, but becomes tedious when working with large datasets. SSFeatures provides text formatting tools like capitalization fixes and spell checking that save time when preparing data for concatenation.

For teams processing large amounts of text data, SSFeatures' text management tools turn 30-minute manual formatting processes into 2-minute automated operations.

FAQ

• Q: What's the difference between + operator and JOIN function for concatenation?

  • A: The + operator is simpler for 2-3 cells but requires specifying each cell individually. JOIN is more efficient for ranges of cells and allows consistent separators across multiple columns.

• Q: Can I concatenate cells from different sheets?

  • A: Yes, use cross-sheet references: `={Sheet Name}[Column Name]1 + " " +

{Sheet Name}[Column Name]2`. Replace "Sheet Name" with your actual sheet name.

• Q: Why do my concatenated dates show time even when the original cells don't display time?
  • A: Smartsheet date cells always contain time data internally. Use DATEONLY() to extract just the date portion, or apply date formatting to your destination cell to hide the time component.

Related Tools

• Hide Dozens of Columns with One Click
• Never Manually Sort Rows Again
• All Solutions