Google Sheets CONCATENATE vs TEXTJOIN: Combine Text the Right Way

You need to combine text in Google Sheets, and you type =CONCATENATE( out of habit. It works. But then you hit a wall: you need a delimiter between values, you want to skip blanks, or you're dealing with an entire range instead of individual cells. That's where TEXTJOIN enters the picture.

Both CONCATENATE and TEXTJOIN join text in Google Sheets, but they solve different problems. This guide breaks down when to use each, with practical examples you can copy right into your spreadsheet.

Key Takeaways:TEXTJOIN is the better choice for most text-combining tasks in Google SheetsCONCATENATE can't add delimiters or skip empty cells nativelyTEXTJOIN accepts full ranges (A1:A100) while CONCATENATE needs each cell listedFor large CSV datasets that need cleanup before combining, SmoothSheet handles the heavy lifting server-side

CONCATENATE: The Classic Way to Join Text

CONCATENATE has been in Google Sheets since the beginning. It takes two or more text strings and glues them together, end to end.

Syntax

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

• string1 (required) - The first text value or cell reference
• string2, ... (optional) - Additional values to join, up to 30 arguments

Basic Examples

Join first and last name with a space:

=CONCATENATE(A2, " ", B2)

Combine a label with a value:

=CONCATENATE("Total: $", C10)

Chain multiple cells:

=CONCATENATE(A2, " ", B2, " - ", C2)

The Ampersand (&) Shortcut

Most experienced Sheets users skip CONCATENATE entirely and use the & operator. It does the same thing with less typing:

=A2 & " " & B2

This is functionally identical to =CONCATENATE(A2, " ", B2). The & operator is easier to read in complex formulas and doesn't have the argument limit that CONCATENATE does.

Where CONCATENATE Falls Short

• No built-in delimiter - You must manually add " ", ", ", or "-" between every pair of values
• No empty cell handling - If B2 is blank, you get an awkward double space or trailing comma
• No range support - You can't write =CONCATENATE(A1:A10) and get all values joined. You need to list each cell: =CONCATENATE(A1, A2, A3, ...)
• Argument limit - Officially supports up to 30 arguments

These limitations are why Google introduced TEXTJOIN.

TEXTJOIN: The Modern Alternative

TEXTJOIN was added to Google Sheets to fix every limitation CONCATENATE has. It lets you specify a delimiter once, ignore empty cells automatically, and pass entire ranges.

Syntax

=TEXTJOIN(delimiter, ignore_empty, text1, [text2, ...])

• delimiter (required) - The character(s) placed between each text value. Use "" for no separator
• ignore_empty (required) - TRUE to skip blank cells, FALSE to include them
• text1 (required) - A cell, range, or text string
• text2, ... (optional) - Additional values or ranges

Basic Examples

Join a range with commas, skipping blanks:

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

Create a dash-separated string:

=TEXTJOIN("-", TRUE, A2, B2, C2)

Join with a line break (useful for multi-line cells):

=TEXTJOIN(CHAR(10), TRUE, A2:A10)

The ignore_empty Parameter

This is TEXTJOIN's biggest advantage. Consider a list where some cells are blank:

With ignore_empty set to TRUE:

=TEXTJOIN(", ", TRUE, A1:A5)
// Result: "Apple, Banana, Cherry"

With ignore_empty set to FALSE:

=TEXTJOIN(", ", FALSE, A1:A5)
// Result: "Apple, , Banana, , Cherry"

With CONCATENATE, you'd need a complex IFERROR or IF wrapper around each cell to achieve the same clean output.

CONCATENATE vs TEXTJOIN: Side-by-Side Comparison

Here's how the two functions stack up across the features that matter most:

Bottom line: Use TEXTJOIN when you're combining more than two or three values, need a separator, or are working with ranges that may contain blanks. Use CONCATENATE (or the & operator) for quick, simple two-cell joins.

Practical Examples

Combine First and Last Name

The most common text-joining task. If column A has first names and column B has last names:

Using CONCATENATE:

=CONCATENATE(A2, " ", B2)

Using the & operator:

=A2 & " " & B2

Using TEXTJOIN:

=TEXTJOIN(" ", TRUE, A2, B2)

All three produce the same result. For this simple case, the & operator is the most readable. But if you also have a middle name in column C that might be blank, TEXTJOIN wins:

=TEXTJOIN(" ", TRUE, A2, C2, B2)
// "John Smith" if middle name is blank
// "John Michael Smith" if middle name exists

With CONCATENATE, a blank middle name would give you "John Smith" (double space).

Build Email Addresses

Generate email addresses from first name, last name, and domain:

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

Or with the & operator:

=LOWER(A2 & "." & B2 & "@company.com")

For email generation, CONCATENATE or & works perfectly since you know exactly which cells to reference and there's no need for empty cell handling.

Create CSV Strings from Columns

Need to turn a column of values into a comma-separated string? This is where TEXTJOIN truly shines:

=TEXTJOIN(", ", TRUE, D2:D50)

This produces a clean comma-separated list from up to 49 values in a single formula. With CONCATENATE, you'd need to reference each cell individually and manually add commas between them.

If you're working with actual CSV files and need to merge or split them before analysis, SmoothSheet's CSV Merger handles that in the browser without uploading your data to any server.

Join with ARRAYFORMULA

When you need to combine text across an entire column, ARRAYFORMULA is your friend. But here's the catch: TEXTJOIN doesn't work directly inside ARRAYFORMULA for row-by-row operations.

The workaround? Use the & operator with ARRAYFORMULA:

=ARRAYFORMULA(A2:A100 & " " & B2:B100)

This creates full names for every row in one formula. It's one of the rare cases where the & operator is genuinely better than TEXTJOIN.

If you need to apply TEXTJOIN-like behavior across rows with ARRAYFORMULA, you can use a QUERY function or REDUCE/LAMBDA combination, but that's an advanced technique beyond most use cases.

Common Errors and Fixes

#VALUE! Error

This usually means you've mixed incompatible data types. Both CONCATENATE and TEXTJOIN expect text, but they usually handle numbers automatically. If you see #VALUE!, check for:

• Array formula conflicts
• Circular references
• Cells containing errors (wrap with IFERROR to handle gracefully)

=TEXTJOIN(", ", TRUE, IFERROR(A2:A10, ""))

Too Many Arguments (CONCATENATE)

CONCATENATE maxes out at 30 arguments. If you're hitting this limit, it's a clear sign you should switch to TEXTJOIN, which supports 252 arguments and accepts ranges.

Instead of:

=CONCATENATE(A1, ", ", A2, ", ", A3, ", ", A4, ...)

Use:

=TEXTJOIN(", ", TRUE, A1:A30)

Missing or Wrong Delimiter

With TEXTJOIN, forgetting the ignore_empty parameter causes an error. Remember: TEXTJOIN always requires exactly three or more arguments (delimiter, ignore_empty, then your text).

// Wrong - missing ignore_empty
=TEXTJOIN(", ", A1:A10)

// Correct
=TEXTJOIN(", ", TRUE, A1:A10)

Result Exceeds Cell Character Limit

Google Sheets cells have a 50,000 character limit. If you're joining a massive range, you may hit this ceiling. For large-scale data operations, consider processing your files outside of Sheets first. SmoothSheet can import large CSV and Excel files into Google Sheets via server-side processing, so you can work with clean, properly structured data from the start.

FAQ

What is the difference between CONCATENATE and TEXTJOIN in Google Sheets?

CONCATENATE joins individual cell values end-to-end with no built-in separator. TEXTJOIN lets you set a delimiter once, skip empty cells with the ignore_empty parameter, and pass entire ranges like A1:A100. For most tasks involving more than two or three values, TEXTJOIN is the better choice.

Can I use CONCATENATE with a range in Google Sheets?

No. CONCATENATE does not accept ranges like A1:A10. You must list each cell individually: =CONCATENATE(A1, A2, A3, ...). If you need to join a range, use TEXTJOIN instead: =TEXTJOIN("", TRUE, A1:A10).

How do I combine text and skip blank cells in Google Sheets?

Use TEXTJOIN with the ignore_empty parameter set to TRUE: =TEXTJOIN(", ", TRUE, A1:A20). This automatically skips any blank cells in the range. CONCATENATE has no equivalent feature and will include blanks, often resulting in double delimiters or trailing spaces.

Is the ampersand (&) operator better than CONCATENATE?

For simple joins (two or three cells), yes. The & operator is shorter to type, has no argument limit, and works inside ARRAYFORMULA for row-by-row operations. It's functionally identical to CONCATENATE but more flexible. However, for joining many values with a separator, TEXTJOIN is still the best option.

Wrap Up

Here's the simple rule: use the & operator for quick two-cell joins, and TEXTJOIN for everything else. CONCATENATE still works, but TEXTJOIN does everything it does and more, with cleaner syntax.

If your text-combining tasks involve cleaning up or merging data from multiple CSV or Excel files before it even reaches Google Sheets, SmoothSheet handles large file imports server-side so your browser doesn't crash. That means you spend less time on data prep and more time on the formulas that actually matter.