How to concatenate in Google Sheets
By the Analistable team · Updated · 2 min read
Use =A2&" "&B2 or =CONCATENATE(A2, " ", B2) to join a few cells. Use =TEXTJOIN(", ", TRUE, A2:D2) to join a range with a separator and skip blanks. CONCAT takes only two values in Google Sheets. To fill a whole column with one formula, use =ARRAYFORMULA(IF(A2:A="", , A2:A&" "&B2:B)).
Part of our guide: How to merge cells, columns and text in Excel
The functions compared
| Function | Arguments | Separator | Skips blanks |
|---|---|---|---|
| & | Any number | Add it yourself | No |
| CONCATENATE | Any number of values or ranges | Add it yourself | No |
| CONCAT | Exactly two values | None | No |
| TEXTJOIN | Separator, ignore_empty, values | Yes | Optional |
| JOIN | Separator, values | Yes | No |
Examples
| Formula | Result |
|---|---|
| =A2&" "&C2 | Ada Lovelace |
| =CONCATENATE(A2, " ", B2, " ", C2) | Ada Lovelace (two spaces) |
| =TEXTJOIN(" ", TRUE, A2:C2) | Ada Lovelace |
| =JOIN(" ", A2:C2) | Ada Lovelace (two spaces) |
Fill a whole column with one formula
=ARRAYFORMULA(IF(A2:A="", , A2:A&" "&B2:B))The IF leaves rows blank where column A is empty, so the formula doesn't fill thousands of rows with a lone space. TEXTJOIN doesn't work row by row inside ARRAYFORMULA (it joins the whole range), so use & or BYROW for row-wise joins:
=BYROW(A2:C10, LAMBDA(r, TEXTJOIN(" ", TRUE, r)))Dates and numbers
Like Excel, Sheets joins the underlying number of a date. Use TEXT: ="Due "&TEXT(B2, "d mmm yyyy").
Joining matched values
=TEXTJOIN(", ", TRUE, FILTER(Orders!C:C, Orders!B:B=A2)) lists every order for the customer in A2 — useful when a lookup would return only the first. See linking two Google Sheets.
The same formulas in Excel, with Excel-specific differences: combining text in Excel.
Combine text with numbers and line breaks
="Order "&A2&": "&TEXT(B2, "£#,##0.00")&CHAR(10)&"Due "&TEXT(C2, "d mmm")CHAR(10) adds a line break inside the cell; turn on Format → Wrapping → Wrap to see it. TEXT keeps currency and date formats, which & would otherwise drop.
Common mistakes
- Using CONCAT with three values — it returns an error. Use CONCATENATE or &.
- JOIN with blank cells leaves double separators; use TEXTJOIN with TRUE.
- ARRAYFORMULA without an IF for empty rows fills the column with separators.
Worked example: a product label
| SKU | Name | Colour | Price | Label formula | Result |
|---|---|---|---|---|---|
| AB-100 | Desk | Oak | 120 | =B2&" ("&C2&") – "&TEXT(D2,"£0.00") | Desk (Oak) – £120.00 |
| AB-101 | Chair | 45 | =TEXTJOIN(" ", TRUE, B3, C3)&" – "&TEXT(D3,"£0.00") | Chair – £45.00 |
The second formula uses TEXTJOIN so the missing colour doesn't leave an empty pair of brackets or a double space.
Frequently asked questions
- How do I concatenate with a space in Google Sheets?
- Use =A2&" "&B2 or =CONCATENATE(A2, " ", B2).
- Why does CONCAT only accept two values in Google Sheets?
- Google Sheets' CONCAT is defined for exactly two values. Use CONCATENATE or TEXTJOIN for more.
- How do I concatenate a whole column?
- Use ARRAYFORMULA with &, for example =ARRAYFORMULA(IF(A2:A="", , A2:A&" "&B2:B)), or BYROW with TEXTJOIN.