Analistable

How to concatenate in Google Sheets

By the Analistable team · Updated · 3 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

Text-joining functions in Google Sheets
FunctionArgumentsSeparatorSkips blanks
&Any numberAdd it yourselfNo
CONCATENATEAny number of values or rangesAdd it yourselfNo
CONCATExactly two valuesNoneNo
TEXTJOINSeparator, ignore_empty, valuesYesOptional
JOINSeparator, valuesYesNo

Examples

A2 = Ada, B2 = (empty), C2 = Lovelace
FormulaResult
=A2&" "&C2Ada 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

Combining several columns into one label
SKUNameColourPriceLabel formulaResult
AB-100DeskOak120=B2&" ("&C2&") – "&TEXT(D2,"£0.00")Desk (Oak) – £120.00
AB-101Chair45=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.

Troubleshooting

Concatenation problems
SymptomCauseFix
Date shows as 45678Dates are numbers underneathWrap the date in TEXT(A2, "yyyy-mm-dd")
Separators doubled where cells are empty& and CONCATENATE keep blanksUse TEXTJOIN with TRUE
Wrong number of decimalsJoined numbers lose their formattingUse TEXT(B2, "0.00")
ARRAYFORMULA fills the whole column with separatorsBlank rows still return the separatorWrap in IF(A2:A="", , ...)

The Excel version of these formulas is in combining text in Excel.

Check the result

Spot-check rows where a source cell is blank, contains a date or holds a number with decimals; these are where concatenation usually goes wrong. =COUNTIF(C2:C, "*, *") finds double separators left by empty cells, and =LEN(C2) shows unexpected trailing spaces.

Keep the results as text

If you need the joined values without formulas, for example before deleting the source columns, copy the column and use Edit → Paste special → Values only.

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.

Related guides