|2. Cell Referencing|
|3. Data Types|
|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:
|1||John Doefirstname.lastname@example.org||100 Example Street||NY|
For whatever reason, you need this data as a single piece of text.
TEXTJOIN function to the rescue!
TEXTJOIN takes multiple pieces of data and joins them together by placing a delimiter in between.
- 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
Let’s use the TEXTJOIN function to join the above data (which is in cells A1:A4) using a space as the delimiter:
Here’s the output:
|1||John Doe email@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:
Here’s the output:
|1||John Doe, firstname.lastname@example.org, 100 Example Street, NY|
Now let’s think about a situation in which some of your data is incomplete:
|1||John Doe||100 Example Street||NY|
John’s email address is missing.
If we use the same function on this data we get:
|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:
|1||John Doe, 100 Example Street, NY|
Google Sheets Cheat Sheet
12 exclusive tips to make user-friendly sheets from today:
You'll get updates from me with an easy-to-find "unsubscribe" link.