You’ve separated, manipulated, rearranged, and calculated your data… now you might need to put it all back together again.

This happens when the data needs to be in a specific format or it’s just easier to send it in an email as one line of text.

Here’s some separated data:

ABCD
1John Doejohn@example.com100 Example StreetNY

For whatever reason, you need this data as a single piece of text.

TEXTJOIN function to the rescue!

TEXTJOIN

TEXTJOIN takes multiple pieces of data and joins them together by placing a delimiter in between.

Syntax

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

Example

Let’s use the TEXTJOIN function to join the above data (which is in cells A1:A4) using a space as the delimiter:

=TEXTJOIN(" ", FALSE, A1:A4)

Here’s the output:

A
1John Doe john@example.com 100 Example Street NY

That’s not as clear as you might want it so let’s change the delimiter to a comma and a space:

=TEXTJOIN(", ", FALSE, A1:A4)

Here’s the output:

A
1John Doe, john@example.com, 100 Example Street, NY

Much better.

Now let’s think about a situation in which some of your data is incomplete:

ABCD
1John Doe100 Example StreetNY

John’s email address is missing.

If we use the same function on this data we get:

A
1John Doe, , 100 Example Street, NY

This is less than ideal as if you have a lot of data you might not notice this and it doesn’t look right.

Instead, we can choose to ignore the empty fields and use:

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

To get:

A
1John Doe, 100 Example Street, NY

Perfect.

Your turn:

Joining Text Exercises
Enter a formula for each question in the formula bar for the spreadsheet below to access the next lesson.
1. Include
In cell A2, enter a formula that joins the text (including the blank cell) in cells A1:D1 into a single cell separated by a comma followed by a space.
2. Ignore
In cell A3, enter a formula that joins the text (ignoring the blank cell) in cells A1:D1 into a single cell separated by a comma followed by a space.
ABCD
1BearsBeetsBattlestar Galactica
2
3
4
Next Lesson

FREE RESOURCE

Google Sheets Cheat Sheet

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

Google Sheets Cheat Sheet

By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.