CONCATENATE & TEXTJOIN in Google Sheets

CONCATENATE in Google Sheets joins text from multiple cells into a single string. The text functions pillar covers the full set of text manipulation tools, but combining text is one of the most common tasks, whether you are building full names, assembling addresses, or creating summary fields from multiple columns.

This guide covers three ways to combine text: the & operator, CONCATENATE, and TEXTJOIN. You’ll learn the syntax for each, when to use which, and how to handle delimiters and empty cells.

In This Guide

• The & Operator
• CONCATENATE Syntax and Examples
• TEXTJOIN Syntax and Examples
• Combine Text Step-by-Step
• Practical Examples
• Common Errors and How to Fix Them
• Tips and Best Practices
• Related Google Sheets Tutorials
• Frequently Asked Questions

The & Operator

The ampersand (&) joins two or more values. It is the shortest way to combine text in Google Sheets.

This joins the value in A2, a space, and the value in B2 into one string. You can chain as many & operators as needed: =A2 & ", " & B2 & " (" & C2 & ")" builds a formatted string with commas and parentheses.

The & operator does not add any separator on its own. If you write =A2&B2, the two values run together with nothing between them. Always include a text string for the separator you want: " " for a space, ", " for a comma and space, " - " for a dash.

CONCATENATE Syntax and Examples

CONCATENATE does the same thing as & with function syntax.

Example: =CONCATENATE("Hello, ", A2) returns "Hello, SHERLOCK HOLMES" when A2 contains "SHERLOCK HOLMES".

CONCATENATE does not accept range references. =CONCATENATE(A2:C2) returns only the value from the first cell in the range. To join multiple cells, list each one: =CONCATENATE(A2, " ", B2, " ", C2).

In practice, the & operator is more common because it is shorter. CONCATENATE is useful when you prefer function syntax for readability, but most users choose & for combining 2-3 values.

TEXTJOIN Syntax and Examples

TEXTJOIN joins a range of cells with a delimiter and can skip empty cells. It is the most flexible option for combining text.

Example: =TEXTJOIN(", ", TRUE, A2:E2) joins all five columns in row 2 with a comma and space between each value. If any cell in the range is empty, TEXTJOIN skips it when ignore_empty is TRUE.

TEXTJOIN accepts both individual cells and ranges. =TEXTJOIN(" | ", TRUE, A2:E2) is cleaner than =A2 & " | " & B2 & " | " & C2 & " | " & D2 & " | " & E2.

Combine Text Step-by-Step

We’ll use the messy contacts table with 8 rows of names, emails, phone numbers, addresses, and company names.

Open the contacts spreadsheet. The data has Full Name in column A, Email in column B, Phone in column C, Address in column D, and Company in column E. The names have extra spaces and mixed capitalization, but we’ll combine first and clean later.

Select cell F2 and enter:

This joins the full name and company with a dash separator. For Sherlock Holmes, the result is "SHERLOCK HOLMES - holmes and watson consulting". The extra spaces and capitalization issues carry through, which is expected. You can wrap the result in TRIM and PROPER to clean it.

Select cell G2 and enter:

This joins the name, email, and company with a comma and space. The result is "SHERLOCK HOLMES, sherlock.holmes@bakermail.com, holmes and watson consulting". TEXTJOIN makes the delimiter consistent without manually adding ", " between every pair.

Select cell H2 and enter:

This joins all five columns with a pipe separator. Copy the formula down through row 9 to build a one-line summary for each contact. For Sherlock Holmes, the result is "SHERLOCK HOLMES | sherlock.holmes@bakermail.com | (555) 123-4567 | 221B Baker Street, Marylebone, London NW1 6XE | holmes and watson consulting".

Practical Examples

Example 1: Build a Full Address Line

You need to combine name, company, and address into a single mailing line.

For Sherlock Holmes, this returns "Sherlock Holmes, Holmes And Watson Consulting, 221B Baker Street, Marylebone, London NW1 6XE". PROPER and TRIM clean the name and company before TEXTJOIN combines everything.

Example 2: Create an Email Subject Line

You want to generate a personalized subject line from a contact name.

For row 2, this returns "Follow up: Sherlock Holmes at Holmes And Watson Consulting". The & operator works well here because you are joining a fixed number of values with different separators.

Example 3: Join with Line Breaks

TEXTJOIN can use a line break character as the delimiter. Use CHAR(10) for a newline.

This stacks name, email, and address on separate lines within a single cell. You need to enable text wrapping (Format > Wrapping > Wrap) for the line breaks to display.

Common Errors and How to Fix Them

Values Running Together Without Spaces

If =A2&B2 returns "SHERLOCK HOLMESsherlock.holmes@bakermail.com", you forgot the separator. Add a space or delimiter between the references: =A2 & " " & B2.

CONCATENATE Ignoring Range References

=CONCATENATE(A2:C2) returns only the first cell’s value. CONCATENATE does not expand ranges. List each cell individually: =CONCATENATE(A2, ", ", B2, ", ", C2), or switch to TEXTJOIN with a range reference.

Numbers Losing Formatting

When you concatenate a formatted number, the formatting is lost. If C2 displays $1,234.00, ="Total: " & C2 returns "Total: 1234". Use TEXT to preserve the format: ="Total: " & TEXT(C2, "$#,##0.00").

Tips and Best Practices

1. Use & for 2-3 values, TEXTJOIN for 4 or more. The & operator is cleaner for short joins. TEXTJOIN is cleaner when you have a range or many values with a consistent delimiter.

2. Always set ignore_empty to TRUE in TEXTJOIN unless you have a specific reason to include blanks. Consecutive delimiters from empty cells look like errors in the output.

3. Clean before combining. Wrap individual values in TRIM, PROPER, or LOWER before joining them. Cleaning after concatenation is harder because the delimiter characters interfere with functions like TRIM.

4. Use CHAR(10) for multiline output. TEXTJOIN with CHAR(10) as the delimiter creates line breaks within a cell. Enable Format > Wrapping > Wrap for the breaks to render.

5. TEXTJOIN replaces CONCATENATE in most cases. Unless you need backward compatibility with older Sheets files that predate TEXTJOIN, there is little reason to use CONCATENATE. TEXTJOIN handles everything CONCATENATE does, plus ranges and delimiters.

Related Google Sheets Tutorials

• Text Functions in Google Sheets: Full guide to cleaning, extracting, and combining text data
• SPLIT Function in Google Sheets: The reverse of CONCATENATE: separate text into columns by delimiter
• TRIM and CLEAN in Google Sheets: Remove extra spaces before combining text
• LEFT, RIGHT, MID in Google Sheets: Extract parts of a string before or after combining
• Data Validation in Google Sheets: Set input rules to prevent messy data that needs combining