Imagine a library where all the books in a particular section are scattered across the floor. You’ve been asked to tidy them up into neat rows on the shelves—but only the ones that match certain criteria. You might think you’d need to pick each book up one by one, but instead, you’ve got a special tool that lets you gather up only the relevant titles at once, arranging them neatly side by side. In Excel, TEXTJOIN is your special tool for pulling together multiple pieces of information based on conditions you specify, saving time and reducing the need for manual work.
Here, we’ll dive into the ins and outs of TEXTJOIN, its unique applications, how it integrates with other Excel functions, and tips on using ChatGPT to streamline your work with it. Follow along with our practice sheet here: TEXTJOIN in Excel.xlsx
Introduction to TEXTJOIN Function in Excel
The TEXTJOIN function in Excel is a text-concatenation powerhouse that lets you join multiple text strings with ease, specifying a delimiter to separate each value. It’s incredibly versatile, handling not just strings of text but ranges, conditions, and exclusions.
The function syntax is as follows:
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
- Delimiter: The character(s) you want between each text item (e.g., a comma, space, or dash).
- Ignore_empty: Whether to ignore empty cells (TRUE or FALSE).
- Text1, Text2, …: The text strings or ranges you want to combine.
Let’s dive into some unique examples to see how TEXTJOIN can solve real-world problems in Excel.
Examples Using TEXTJOIN
- Generating a Custom Address Label
Imagine you have an address broken up across several cells (Street, City, State, Zip) in cells B2 to E2. Here’s how TEXTJOIN makes it easy to create a complete address in one cell:
Formula:
=TEXTJOIN(“, “, TRUE, D6:H6)
This puts together all elements of the address, ignoring blanks and automatically adding commas for a neat result.
Result:
- Automating Password Generation for Confidential Files
Imagine you’re managing a team that handles sensitive government documents. To ensure security, each document requires a unique password – first four letters of a name followed by a date of birth. Instead of manually creating passwords, you can automate this process using Excel’s TEXTJOIN function.
Formula:
=TEXTJOIN(“”, TRUE, UPPER(LEFT(B6, 4)), TEXT(D6, “ddmmyyyy”))
TEXTJOIN(“”, TRUE, …) :This function combines multiple text strings into a single text string. The “” argument specifies that no delimiter should be used between the concatenated strings.
UPPER(LEFT(B6, 4)) : LEFT(B6, 4) extracts the first four characters from cell B6 (the first name). UPPER() function converts the extracted characters to uppercase.
TEXT(C2, “ddmmyyyy”): TEXT(C2, …) formats the date in cell C2 according to the specified format. “ddmmyyyy” format string specifies the desired date format: two digits for the day, two digits for the month, and four digits for the year.
Result:
- Dynamically Generating File Paths
Imagine ou need to create file paths dynamically based on specific criteria, such as date, project name, and file type.
Formula:
=TEXTJOIN(“\”, TRUE, “C:\Reports\”, Table3[@[Project Name]], Table3[@[File Type]] & Table3[@[File_Format]] )
TEXTJOIN(“\”, TRUE, …)
- TEXTJOIN is a function in Excel used to combine (concatenate) text values with a specified delimiter. In this case, the delimiter is a backslash (\), commonly used in file paths. The first argument, “\\”, tells TEXTJOIN to use a backslash as the delimiter between each component. The second argument, TRUE, specifies that empty values should be ignored. If any of the referenced cells are blank, TEXTJOIN will skip over them, avoiding double backslashes.
“C:\Reports\”
- This is the base folder path provided as a static text string, “C:\Reports\”. This part will appear at the beginning of each combined file path. Since it’s provided as a string, it will not change for each row in Table3.
Table3[@[Project Name]]
- This component dynamically references the Project Name column in Table3, specifically for the current row. The @ symbol means this will take the value from the current row where the formula is applied. If the Project Name in the row is “Project Alpha”, this part would add “Project Alpha” to the file path.
Table3[@[File Type]] & Table3[@[File_Format]]
- This part uses the & operator to combine two columns, File Type and File_Format, from Table3. The & simply concatenates them directly without any additional characters in between. For example, if File Type is “Report” and File_Format is “.pdf”, this part would produce “Report.pdf”. The @ symbol again means this reference applies to the current row only.
Result:
- TEXTJOIN with Multiple Delimiters
Unfortunately, TEXTJOIN doesn’t directly support multiple delimiters within a single function. However, we can achieve the desired result by combining TEXTJOIN with other functions like CONCATENATE or the ampersand (&) operator or used nested TEXTJOIN. Let’s take a look at how to do this using just TEXTJOIN.
=TEXTJOIN(“@”,TRUE,
TEXTJOIN(“-“,TRUE,TEXTJOIN(“_”,TRUE,LOWER(B6),LOWER(C6),D6)),
TEXTJOIN(“.”,TRUE,LOWER(E6),LOWER(F6)))
In this case, the formula constructs an email address by first combining the Last Name (B6), First Name (C6), and Employee ID (D6) with underscores (_) between the first and last name, then a hyphen (-) between the name and employee ID. It then appends the Domain Name (E6) and TLD (F6), converting them to lowercase, and separates them with a period (.). The resulting parts are then joined with an @ symbol between the name-employee ID section and the domain-TLD section. For example, if the data is B6=Smith, C6=John, D6=1234, E6=company, F6=com, the result would be: [email protected].
You can also specify different delimiters as strings in TEXTJOIN without using nested statements:
=TEXTJOIN(“”, TRUE, LOWER(B6), “_”, LOWER(C6), “-“, D6, “@”, LOWER(E6), “.”, LOWER(F6))
Here’s another way to achieve the same result using the “&” operator which is the shorthand notation for text concatenation functions:
=LOWER(B6) & “_” & LOWER(C6) & “-” & D6 & “@” & LOWER(E6) & “.” & LOWER(F6)
Finally, let’s look at how you can CONCATENATE() function for the same result:
=CONCATENATE(LOWER(B6), “_”, LOWER(C6), “-“, D6, “@”, LOWER(E6), “.”, LOWER(F6))
Result Comparison:
Examples Using TEXTJOIN With Other Excel Functions
- With IF()
- Automatic Email Subject Line Generation
Imagine you’re automating email generation based on specific conditions. You want to create dynamic subject lines that include relevant information.
Formula:
=TEXTJOIN(” – “, TRUE, IF(B6=”Urgent”, “!URGENT”, “”), C6, D6)
TEXTJOIN(” – “, TRUE, …)
- Combines multiple text values, separated by ” – “. The TRUE argument tells TEXTJOIN to ignore empty values, so it skips any blank cells.
IF(B6=”Urgent”, “!URGENT”, “”)
- Checks if cell B6 contains the word “Urgent.” If B6 is “Urgent,” it returns “!URGENT” (an attention-grabbing label); otherwise, it returns an empty string (“”), so no urgent label is included.
C6 and D6
- These are additional values to include in the result, whatever is in cells C6 and D6.
Result:
- Generating Dynamic SQL Queries
Imagine you’re a data analyst working with a large database. You frequently need to run complex SQL queries to extract specific information. Instead of manually writing these queries, you can automate the process using Excel’s TEXTJOIN function. Let’s say you have a database with tables for Customers, Orders, and Products. You want to create dynamic SQL queries to retrieve data based on user-specified criteria.
Formula:
=TEXTJOIN(” “, TRUE, “SELECT * FROM “, B6,
” WHERE “, C6, ” “, D6, ” ‘”, E6, “‘”,
IF(F6<>””, ” AND “, “”), F6, ” “, G6, ” “, H6, “”,
IF(I6<>””,” ORDER BY “, “”), I6, ” “, J6)
This formula builds a dynamic SQL query string using TEXTJOIN to concatenate values from various cells. It starts with “SELECT * FROM ” and pulls the table name from cell B6. The WHERE clause is constructed using C6, D6, and E6 (for the first condition), and if F6 has a value, it adds an AND clause using F6, G6, and H6 as a second condition. The ORDER BY clause is included if I6 is filled, sorting by I6 in the order specified in J6. This approach dynamically builds SQL-like queries directly from cell values.
Result:
With VLOOKUP()
Imagine you have a table of customer orders, each with a unique order ID. You want to create a formula that generates a custom order code by combining the order ID with a prefix and suffix. Then, you’ll use this custom order code to look up additional order details from another table.
Order Table:
Order Details Table:
Formulas and Results:
- Generate Custom Order Code:
=TEXTJOIN(“-“, TRUE, “ORD”, B12)
This formula creates a custom order code by combining “ORD” with the order ID.
- Use the Custom Order Code in VLOOKUP:
=VLOOKUP(TEXTJOIN(“-“, TRUE, “ORD”, B12), OrderDetailsTable, 2, FALSE)
This formula uses the custom order code generated in step 1 as the lookup value to retrieve the shipping address from the Order Details table. By combining TEXTJOIN and VLOOKUP in this way, you can create dynamic formulas that can be used to automate various tasks and extract information from multiple data sources.
Leveraging Google Gemini AI for Complex Excel Tasks using TEXTJOIN
Gemini AI can significantly streamline your Excel workflows, especially when dealing with complex tasks like combining functions and data manipulation. For instance, to create a dynamic lookup formula using TEXTJOIN and VLOOKUP, you can simply describe the problem and desired outcome to Gemini. It can provide the exact formula, explain its components, and even suggest alternative approaches. By harnessing the power of AI, you can automate repetitive tasks, improve accuracy, and gain valuable insights from your data.
Let’s look at how to get the above VLOOKUP formula using Google Gemini AI:
Prompt:
“I have two tables:
Order Table and Order Details Table
*insert a screenshot of the tables*
I want to create a formula that to looks up the shipping address from the second table. Please show how TEXTJOIN can be used for this and any alternatives as well.
Expected Output:
If I have the value “12345” in the lookup cell, I must get the value “123 Main St” from Order Details table.”
Result:
Here are the responses by Google Gemini AI:
VLOOKUP with TEXTJOIN:
=VLOOKUP(TEXTJOIN(“-“, TRUE, “ORD”, A2), E2:F4, 2, FALSE)
VLOOKUP with CONCATENATE:
=VLOOKUP(CONCATENATE(“ORD-“, A2), E2:F4, 2, FALSE)
Concatenate vs. TEXTJOIN: Which One Should You Use?
| Feature | CONCATENATE (or & Operator) | TEXTJOIN |
| Basic Functionality | Joins text from multiple cells into a single cell. Requires manually adding delimiters (e.g., spaces or commas) between each piece of text. Example: =CONCATENATE(A2, “-“, B2, “-“, C2) | Joins text with a specified delimiter, applied automatically between each value. Example: =TEXTJOIN(“-“, TRUE, A2:C2) |
| Concatenating Large Ranges | Requires individual references to each cell, making it impractical for large ranges. For example: =CONCATENATE(A1, A2, A3, …).=CONCATENATE(A1:A100) will work like HSTACK() in excel 365. The new CONCAT() can be used instead. | Efficiently handles large ranges by accepting a single range, such as A1:A100, and inserting the delimiter between each cell’s value automatically. |
| Compatibility with Older Excel Versions | Compatible with all Excel versions, making it universally usable. This can be helpful in environments with mixed Excel versions. | Available only in Excel 2016 and later, which may limit compatibility for users with older versions. |
| Syntax Simplicity | Often requires more complex syntax due to the need to specify delimiters repeatedly. | Simplifies syntax, as delimiters are specified just once and automatically applied throughout the range. |
| Recommended Use Cases | Best for compatibility with older Excel versions or when working with small sets of data with few cells to concatenate. | Ideal for handling large ranges, ignoring empty cells, and simplifying text joins with a uniform delimiter. |
Where Would TEXTJOIN() Prevail Over CONCATENATE() and CONCAT()?
Scenario: Joining Customer Names in a List
Imagine you have a list of customers who purchased a particular product, and you want to create a summary that lists the customer names separated by commas.
Here’s the data in columns B and C(Product names and Customer names), with some rows of customer data:
Dynamic Table – Table6
Approaches:
Let’s summarise the results for joining customer names for every product using each function:
Before we get into using the functions, let’s take a look at how to create a dropdown of the product list, so that we can dynamically filter the comma-seperated customer list based on the selected product.
Steps to Create a Dropdown List in Excel:
- Prepare List: Ensure your list of options (e.g., products) is ready in a column (e.g., B6:B11 or Table6[Products]).
- Select Cell: Click on the cell where you want the dropdown (e.g., E5).
- Go to Data Tab: In the ribbon, click on the Data tab.
- Open Data Validation: Click Data Validation under the Data Tools group.
- Set Validation Criteria:
- In the Settings tab, select List under the Allow dropdown.
- In the Source box, enter the range (e.g., =$B$6:$B$11 or =Table6[Products]) or select the range manually.
- Click OK: Press OK to apply the dropdown.
Now, E5 will have a dropdown with the options from your list!
- TEXTJOIN:
- Formula: =TEXTJOIN(“, “, TRUE, IF(Table6[Product]=E5, Table6[Customer Name], “”))
- Result: “Bob Johnson, Emily Davis”
- Pros: Automatically skips empty cells, handles ranges efficiently, uses a delimiter, and works with conditions (like checking the product name).
- CONCAT:
- Formula:
=CONCAT(IF(B5:B11=E5, Table6[Customer Name], “”))
- Result: “Bob JohnsonEmily Davis”
- Cons: No delimiter handling automatically, doesn’t skip empty cells. Else part must be taken care of, or else FALSE gets appended to the string. Even if you use a range, like B6:B10, CONCAT will simply concatenate the values without delimiters.
- CONCAT WITH DELIMITER (MANUAL):
- Formula:
=CONCAT(
IF(B5=E5, C5 & “, “, “”),
IF(B6=E5, C6 & “, “, “”),
IF(B7=E5, C7 & “, “, “”),
IF(B8=E5, C8 & “, “, “”),
IF(B9=E5, C9 & “, “, “”),
IF(B10=E5, C10 & “, “, “”),
IF(B11=E5, C11, “”)
)
- Result: “Bob Johnson, Emily Davis, “
- Cons:
- Requires manual entry for each delimiter with multiple if conditions. Although CONCAT works for ranges, it falls short in the above case. CONCAT works only by combining cell references or values one by one. It cannot apply a delimiter automatically between values. Itdoes not support delimiters or ranges in the way we want (i.e., automatically adding a comma and space between values in a range). So, if you use CONCAT, you still need to specify the logic for concatenation in each individual IF statement.
- The primary issue with the provided formula is the unavoidable inclusion of commas within the IF function statements, even when the condition is false. This leads to unnecessary commas being added to the final concatenated string. For instance, if B5 doesn’t match E5, the IF statement will still return an empty string followed by a comma and a space. This extra comma is then concatenated to the final result, leading to the unwanted last comma.
- CONCATENATE:
- Formula:
=CONCATENATE(
IF(B5=E5, C5, “”),
IF(B6=E5, C6,””),
IF(B7=E5, C7,””),
IF(B8=E5, C8,””),
IF(B9=E5, C9,””),
IF(B10=E5, C10,””),
- Result: “Bob JohnsonEmily Davis”
- Cons: Same as CONCAT, but even more outdated – does not work. It also requires manual entry for each delimiter with multiple IF statements and doesn’t handle empty cells automatically. Even if it works for ranges in excel 365, it does not give the result in a single cell – rather it spills the result to the adjoining cells working like HSTACK() in excel for dynamic arrays.
- CONCATENATE WITH DELIMITER (MANUAL):
- Formula:
=CONCAT(
IF(B5=E5, C5 & “, “, “”),
IF(B6=E5, C6 & “, “, “”),
IF(B7=E5, C7 & “, “, “”),
IF(B8=E5, C8 & “, “, “”),
IF(B9=E5, C9 & “, “, “”),
IF(B10=E5, C10 & “, “, “”),
IF(B11=E5, C11, “”)
)
- Result: “Bob Johnson, Emily Davis, “
- Cons: Same as CONCAT, but even more outdated. Requires manual entry for each delimiter and doesn’t handle empty cells automatically. The primary issue with the provided formula is the unavoidable inclusion of commas within the IF statements, even when the condition is false. This leads to unnecessary commas being added to the final concatenated string. For instance, if B5 doesn’t match E5, the IF statement will still return an empty string followed by a comma and a space. This extra comma is then concatenated to the final result, leading to the unwanted last comma.
Result Comparison:
Key Advantages of TEXTJOIN OVER CONCATENATE() and CONCAT():
- Automatic delimiters: You specify the delimiter once, and TEXTJOIN applies it between each concatenated value.
- Handles ranges efficiently: Instead of writing out individual IF conditions for each row, TEXTJOIN handles ranges like B6:B10 and C6:C10 with ease when you want to concatenate multiple values in one cell.
Limitations of TEXTJOIN in Large Office Environments
Character Limit: TEXTJOIN has a character limit for the resulting text string. While this limit is high, it’s important to consider it if you’re working with extremely large concatenations.
Performance: For very large datasets or complex formulas, TEXTJOIN may impact Excel’s performance, especially when used along with other resource-intensive functions.
Formatting: Although TEXTJOIN can combine text strings, it does not apply formatting to the output. You might need to use additional formatting functions or custom number formats to achieve a specific appearance.
Error Handling: TEXTJOIN doesn’t have built-in error handling for invalid inputs or errors within the concatenated data. To handle these issues, you may need to use IFERROR or similar functions.
Limited Flexibility in Delimiters: You can specify a delimiter, but it’s limited to a fixed character or string. Changing delimiters dynamically based on conditions or data isn’t directly possible with TEXTJOIN.
FAQs
Q1: Can TEXTJOIN handle non-continuous ranges?
A1: Yes, TEXTJOIN can join values across different columns which follow a non-continuous trend.
Q2: Does TEXTJOIN have a character limit?
A2: Yes, like other Excel functions, it’s capped at 32,767 characters.
Concluding Remarks
The TEXTJOIN function in Excel is a powerful tool for combining text across multiple cells, offering simplicity, efficiency, and flexibility, particularly when working with large ranges of data. Its ability to ignore empty cells, use a single delimiter across a range, and streamline syntax makes it ideal for creating clean, organised text outputs quickly.
While TEXTJOIN has some limitations, such as character limits, performance concerns with large datasets, and a lack of direct formatting or dynamic delimiter flexibility, these can be managed with thoughtful formula design and supplementary functions like IFERROR for error handling.
Overall, TEXTJOIN stands out as a modern alternative to CONCATENATE, especially in dynamic, data-rich environments. Embracing it can simplify text-based tasks and allow you to work smarter with Excel’s capabilities, making it a valuable function for any Excel user aiming to handle text data efficiently. Download or browse our practice file on Excel web to get started with TEXTJOIN : TEXTJOIN in Excel.xlsx.
