LESSONS 1. Intro 2. Cell Referencing 3. Data Types 4. Operators 5. Functions 6. Intro To Logic 7. Simple Math 8. Math & Logic 9. Changing Text 10. Replacing Text 11. Splitting Text 12. Joining Text 13. Sorting Data 14. Rearranging Data 15. Looking Up Data

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:

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

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, ...])
• delimiter = the text you want in between the data points
• ignore_empty = true or false setting that determines if empty text entered is ignored (TRUE) or included (FALSE)
• text1 = a single piece of data, an array of data, a cell reference, or range reference
• [text2, ...] = optional additional data to join together

### 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 1 John 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 1 John 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:

 A B C D 1 John Doe 100 Example Street NY

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

 A 1 John 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 1 John Doe, 100 Example Street, NY

Perfect.

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.
 A B C D 1 Bears Beets Battlestar Galactica 2 3 4
Next Lesson

FREE RESOURCE

12 exclusive tips to make user-friendly sheets from today:

Get my 12-tip cheat sheet that will make your spreadsheets more user-friendly.