How to Use CONCATENATE in Google Sheets
=CONCATENATE(string1, [string2, ...]). Add the spaces and punctuation yourself as quoted text.
What CONCATENATE does
CONCATENATE appends text from several cells or typed strings into one result. Use it to build a full name from first and last name, or to assemble a label from separate fields.
CONCATENATE syntax
=CONCATENATE(string1, [string2, ...])
string1: the first piece of text or cell to join.string2, ...(optional): more pieces, appended in order.
CONCATENATE puts nothing between the pieces. If you want a space or a comma, add it as its own argument, such as " ".
A step-by-step example
Here is a name split over two cells in A1:B2.
| A | B | |
|---|---|---|
| 1 | First | Last |
| 2 | Ann | Lee |
To build the full name:
=CONCATENATE(A2, " ", B2)
This returns Ann Lee. Here is why:
- Sheets writes the contents of A2:
Ann. - It appends the space in quotation marks.
- It appends B2:
Lee.
To reverse the order with a comma, =CONCATENATE(B2, ", ", A2) returns Lee, Ann.
How it treats ranges
Google's help page says that when a range is more than one cell wide and tall, CONCATENATE reads across each row before moving down. So CONCATENATE(A2:B7) is the same as listing A2, B2, A3, B3, and so on. The pieces are still joined with nothing between them. To join a range with a separator, use TEXTJOIN.
Common mistakes
- Forgetting the separator.
=CONCATENATE(A2, B2)givesAnnLee. - Missing quotation marks around typed text.
- Joining a long range. TEXTJOIN is simpler and adds a delimiter.
- Numbers and dates. They are joined as plain values. Use TEXT first if you need a specific format.
Try it yourself
Use the name above (A2 is Ann, B2 is Lee). Type your formula into the box, starting with =, then press Check.
Exercise 1: Full name
Write a formula that joins the first name in A2 and the last name in B2 with a space between them.
Exercise 2: Last, First
Write a formula that returns the name as Last, First (the last name in B2, a comma and a space, then the first name in A2).
What to learn next
- TEXTJOIN: combine text from many cells with a separator.
- SPLIT: break text into separate cells.
- UPPER, LOWER and PROPER: change the case of text.
- CONCAT: a shorter function that joins just two values.
Syntax and rules in this guide were checked against Google's CONCATENATE help page. See all guides.