How to use the TEXTJOIN Function in Google Sheets
Engineering - Follow us to learn every day 💡
❤️ Learning & Engineering
Combines the text from multiple strings and/or arrays, with a specifiable delimiter separating the different texts.
The TEXTJOIN function in Google Sheets is a powerful tool designed to concatenate or join multiple text strings into one single string, with a specified delimiter between each text segment. This function is particularly useful when you need to combine values from different cells while maintaining a clear separation between each value.
Benefits
1. Efficiency: Combine multiple cells' data quickly without manually concatenating each cell.
2. Flexibility: Choose any delimiter to separate the concatenated values, such as commas, spaces, or custom characters.
3. Cleanliness: Option to ignore empty cells, resulting in a cleaner and more accurate combined string.
4. Automation: Reduces manual effort and potential errors in data entry and formatting.
Step-by-Step Guide
Syntax
TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
- delimiter: A string, such as a space, comma, or any character, that separates each text segment.
- ignore_empty: A boolean value (`TRUE` or FALSE) indicating whether to ignore empty cells.
- text1, [text2], …: The text segments you want to join. These can be individual cell references, ranges, or even direct text strings.
1. Open Google Sheets: Start a new Google Sheets document or open an existing one.
2. Input Data: Enter the data you want to concatenate. For example, in cells A1 to A5:
A1: Apple
A2: Banana
A3:
A4: Cherry
A5: Date
3. Using the TEXTJOIN Function:
- Click on the cell where you want the concatenated result to appear.
- Enter the TEXTJOIN formula. For example:
=TEXTJOIN(", ", TRUE, A1:A5)
- Here, ", " is the delimiter (comma and space), TRUE indicates that empty cells should be ignored, and A1:A5 specifies the range of cells to join.
4. Press Enter: The result should be:
Apple, Banana, Cherry, Date
Example
Let's create a detailed example using a larger data set. Suppose we have a list of names and departments in a company, and we want to create a mailing list.
1. Input Data:
A1: John Doe
A2: Jane Smith
A3:
A4: Alice Johnson
A5:
A6: Bob Brown
A7:
2. Construct the TEXTJOIN Formula:
- We want to join the names into a single string, separated by semicolons, and ignore the empty cells.
- Enter the formula:
=TEXTJOIN("; ", TRUE, A1:A7)
3. Result:
John Doe; Jane Smith; Alice Johnson; Bob Brown
Advanced Tips for Using TEXTJOIN
1. Combining Text with Numbers:
- You can join text with numbers. For example, if you have quantities in column B:
A1: Apple B1: 10
Recommended by LinkedIn
A2: Banana B2: 5
A3: Cherry B3: 12
Use:
=TEXTJOIN(", ", TRUE, A1:A3, B1:B3)
This will concatenate both columns' values, resulting in:
Apple, 10, Banana, 5, Cherry, 12
2. Using TEXTJOIN with Conditions:
- Combine TEXTJOIN with other functions like IF to conditionally join text. For instance, if you want to join only the names where the quantity is greater than 5:
=TEXTJOIN(", ", TRUE, IF(B1:B3>5, A1:A3, ""))
The result will be:
Apple, Cherry
3. Joining Data from Different Sheets:
- You can reference data from different sheets:
=TEXTJOIN(", ", TRUE, Sheet1!A1:A5, Sheet2!B1:B5)
This will concatenate values from ranges in Sheet1 and Sheet2.
4. Creating Custom Formatted Strings:
- Combine TEXTJOIN with other text functions like CONCATENATE, LEFT, RIGHT, etc., for more complex formatting.
- Example: Combine first names with last initials:
A1: John Doe
A2: Jane Smith
A3: Alice Johnson
Formula:
=TEXTJOIN(", ", TRUE, LEFT(A1, FIND(" ", A1)-1) & " " & LEFT(RIGHT(A1, LEN(A1) - FIND(" ", A1)), 1) & ".", LEFT(A2, FIND(" ", A2)-1) & " " & LEFT(RIGHT(A2, LEN(A2) - FIND(" ", A2)), 1) & ".", LEFT(A3, FIND(" ", A3)-1) & " " & LEFT(RIGHT(A3, LEN(A3) - FIND(" ", A3)), 1) & ".")
Result:
John D., Jane S., Alice J.
👉Purchase our book to improve your Excel productivity :
📚102 Most Useful Excel Functions with Examples: The Ultimate Guide
▶️▶️ Order it here : https://lnkd.in/enmdA8hq
🚀 Transform from novice to pro with:
🔍 Step-by-Step Guides
🖼️ Clear Screenshots
🌎 Real-World Examples
📔 Downloadable Practice Workbooks
💡 Advanced Tips