How to Combine Text Using CONCATENATE and TEXTJOIN in Google Sheets

Google Sheets provides several powerful functions to manipulate and manage your data. One of the most common tasks when working with spreadsheets is combining text from different cells. In this post, we will explore two essential functions for this task: CONCATENATE and TEXTJOIN.

🔗 1. The CONCATENATE Function

The CONCATENATE function is a simple yet effective way to combine text from multiple cells into one. This function allows you to join several strings of text into a single cell.

=CONCATENATE(string1, string2, ...)

For example, if you want to combine the first and last name in separate columns (A2 for first name, B2 for last name), use the following formula:

=CONCATENATE(A2, " ", B2)

This formula will merge the first and last name with a space in between, such as "John Doe".

💬 2. The TEXTJOIN Function

The TEXTJOIN function is more advanced and flexible than CONCATENATE. It allows you to join text from a range of cells, with a specified delimiter between each text item. It’s especially useful when you have a large range of text and want to combine them with a separator (such as a comma or space).

=TEXTJOIN(delimiter, ignore_empty, range)

In this formula:

• delimiter: The character or string that will appear between the text items (e.g., ", " or " ").
• ignore_empty: TRUE to ignore empty cells, or FALSE to include them.
• range: The range of cells containing the text you want to join.

For example, to join the text in cells A2 through A5 with commas, use the following formula:

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

This will create a comma-separated list from the values in cells A2 to A5, excluding any empty cells.

🌟 3. CONCATENATE vs TEXTJOIN: Which One to Use?

While both CONCATENATE and TEXTJOIN can combine text, TEXTJOIN offers more features and flexibility. The key differences are:

• TEXTJOIN: Allows specifying a delimiter and can ignore empty cells, making it ideal for combining text with consistent separators.
• CONCATENATE: Works well for combining a small number of cells, but lacks the delimiter and empty-cell handling features of TEXTJOIN.

If you need to join large ranges of data with a delimiter, TEXTJOIN is the better choice. However, for simpler tasks with just a few cells, CONCATENATE may suffice.

🔧 4. Practical Example: Creating an Email List

Suppose you have a list of names and email addresses in separate columns, and you want to create a list of email addresses separated by commas. You can use TEXTJOIN like this:

=TEXTJOIN(", ", TRUE, B2:B10)

This will join all email addresses from cells B2 to B10 into a comma-separated list, excluding any empty cells.

📥 Downloadable PDF

• Google Sheets CONCATENATE and TEXTJOIN Functions Guide (PDF)

---

Tags: CONCATENATE function, TEXTJOIN function, combine text, google sheets functions, text manipulation, google sheets tutorial