How to Use CONCATENATE in Google Sheets: Comprehensive Guide

CONCATENATE joins several pieces of text into one. It is the classic way to build a full name from first and last name, an ID from a prefix and a number, or a sentence from cell values. For most modern work the & operator or TEXTJOIN is more convenient, and this page explains when to use which.

Syntax

=CONCATENATE(string1, [string2, ...])

string1: The first piece of text, a cell reference, or a value.
string2, ... [Optional]: More pieces, joined in order.

CONCATENATE adds nothing between the pieces, so include spaces or punctuation yourself.

Basic examples

=CONCATENATE("Hello", " ", "World")

Returns Hello World.

=CONCATENATE(A2, " ", B2)

With Jane in A2 and Doe in B2, returns Jane Doe.

=CONCATENATE("INV-", TEXT(C2, "0000"))

With 42 in C2, returns INV-0042.

The & operator

Anything CONCATENATE does, the & operator does with less typing:

=A2 & " " & B2

This is identical to CONCATENATE(A2, " ", B2) and is the form most people prefer. CONCAT is another variant, but it accepts only two arguments.

Numbers, dates and formatting

Joined numbers and dates use their raw values, so formatting is lost. A date such as March 15, 2026 joins as the serial number 46096, and 0.25 stays 0.25 rather than 25%. Use TEXT to format before joining:

=CONCATENATE("Due on ", TEXT(D2, "mmmm d, yyyy"))

Returns Due on March 15, 2026.

=CONCATENATE("Growth: ", TEXT(E2, "0.0%"))

Returns Growth: 25.0%.

=CONCATENATE("Total: ", TEXT(F2, "$#,##0.00"))

Returns Total: $1,234.50.

Joining a whole column or row

CONCATENATE combines the pieces you list. To join a whole range with a separator, use TEXTJOIN, which also skips blanks:

=TEXTJOIN(", ", TRUE, A2:A20)

To build a joined value for every row at once, use & inside ARRAYFORMULA. (CONCATENATE inside ARRAYFORMULA merges the whole range into a single string rather than joining row by row.)

=ARRAYFORMULA(A2:A & " " & B2:B)

Where the rows may be blank, protect against filling empty rows with a lone space:

=ARRAYFORMULA(IF(A2:A = "", "", A2:A & " " & B2:B))

Practical patterns

Build an email address

=LOWER(CONCATENATE(A2, ".", B2, "@example.com"))

Create a lookup key from two columns

=A2 & "|" & B2

Helps VLOOKUP match on two criteria. The separator avoids false matches, such as 1 + 23 versus 12 + 3.

Build a formula or URL

=CONCATENATE("https://example.com/search?q=", ENCODEURL(A2))

Create a hyperlink

=HYPERLINK(CONCATENATE("https://example.com/product/", A2), "View product")

Add a line break

=CONCATENATE(A2, CHAR(10), B2)

Turn on Format > Wrap text to see both lines.

Build a sentence

=CONCATENATE(A2, " sold ", B2, " units in ", C2, ".")

Video Example

Common errors

Missing spaces. CONCATENATE(A2, B2) gives JaneDoe. Add " ".
A date appears as a number. Use TEXT(D2, "yyyy-mm-dd").
A percent or currency loses its format. Use TEXT with a format.
#NAME?. Text that isn't quoted: CONCATENATE(Hello, A2) should be CONCATENATE("Hello", A2).
Errors in a source cell spread. If any piece is an error, the result is that error. Wrap in IFERROR.
Joining blanks leaves stray separators. Use TEXTJOIN with ignore_empty set to TRUE.

CONCATENATE vs. & vs. TEXTJOIN

&: shortest and easiest for two or three items.
CONCATENATE: equivalent to &, a bit more verbose, familiar to users coming from Excel.
TEXTJOIN: best for many items, ranges, delimiters and skipping blanks.

Frequently asked questions

Is there a limit on the number of arguments? No practical limit, but long lists are better handled with TEXTJOIN.

Does CONCATENATE add spaces? No. Add them yourself.

How do I add a line break? Use CHAR(10) and turn on Wrap text.

What is the difference between CONCAT and CONCATENATE? CONCAT takes exactly two arguments. CONCATENATE takes many.

Related Functions

TEXTJOIN: Join text with a delimiter and skip blanks.
SPLIT: Divide text at a delimiter.
TEXT: Format a number or date as text.
HYPERLINK: Create a clickable link.
LEFT: Return the first characters of a text.

CONCATENATE