Combining text pieces into a single cell is constant work in spreadsheets — full names from first plus last, addresses from line1+city+state+zip, comma-separated lists from a column of values. Google Sheets gives you four good options (CONCATENATE, the & operator, JOIN, TEXTJOIN) and each fits a different shape of problem.
& for ad-hoc concatenation
=A2 & " " & B2 & " (" & C2 & ")"The ampersand operator is the right tool for stringing together a small number of pieces with literal text in between. It reads naturally, accepts any value (numbers and dates get auto-converted to strings), and is what most experienced sheet builders reach for first.
TEXTJOIN for ranges with a separator
=TEXTJOIN(", ", TRUE, A2:A20)TEXTJOIN excels when you have a range of values and want them comma-separated. The first argument is the separator, the second is whether to skip empty cells (almost always TRUE), and the third onward is the list of values. Unlike CONCATENATE it accepts ranges directly, which means you do not have to spell out every cell.
JOIN — TEXTJOIN's older sibling
JOIN does the same thing as TEXTJOIN but without the empty-skipping flag, so blanks turn into double-separators in the output. Reach for JOIN only when you want that behaviour deliberately. For everyday work, TEXTJOIN is the cleaner default.
Worked example: build a CSV-style status line
From an order sheet with columns Order, Customer, Items (range), and Total, the line =A2 & " — " & B2 & " — " & TEXTJOIN(", ", TRUE, C2:K2) & " — " & TEXT(L2, "\$#,##0.00") produces "INV-1042 — Acme Co — Widget, Sprocket, Cog — \$418.00". Notice the TEXT() call to format the total — concatenation otherwise renders a raw number with too many decimal places.
Worked examples
Full name from first and last
=A2 & " " & B2Reach for & when there are only two or three pieces.
Comma-separated tags
=TEXTJOIN(", ", TRUE, D2:Z2)Empty cells skipped automatically.
Address block on three lines
=A2 & CHAR(10) & B2 & ", " & C2 & " " & D2CHAR(10) is a line break. Turn on Wrap on the cell to see it.
Common mistakes (and how to avoid them)
- Forgetting to format numbers and dates: bare concatenation prints "45292.5" instead of "1/24/2024 12:00 PM". Wrap with TEXT() and a format string.
- Using JOIN when blanks matter — you get double commas. TEXTJOIN with TRUE skips them.
- Hardcoding separators that may appear inside the data. If a value already contains a comma, your CSV is broken — switch to a tab or pipe.
- Concatenating across thousands of rows with
&in one giant formula. Use TEXTJOIN with a range or build a helper column.
Tips that pay off later
TEXT(B2, "\$#,##0.00")formats currency,TEXT(C2, "yyyy-mm-dd")formats dates — keep these snippets in your scratch sheet.- TEXTJOIN can use any string separator including emoji or HTML; it is not limited to single characters.
- For wrapping long concatenations across multiple lines in the formula bar, use Alt+Enter in the bar — readability is worth it.
When to reach for something else
If you are building markup or rich layouts, generate the text with a helper sheet and paste — keeping presentation logic out of formulas keeps the sheet maintainable.