Excel TEXTJOIN Function | 6 Practical examples

Excel TEXTJOIN Function

The Excel TEXTJOIN Function is use to combine text from multiple ranges and/or strings, and includes a delimiter users specify between each text value combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.

In this section:

---

1. Syntax of TEXTJOIN Function
2. Join First and Last Names
3. Join phone numbers with area codes
4. Join text with conditions
5. Join names with PROPER Case
6. Create email address by joining first and last names
7. Join first, last, and middle names with some middle names missing

---

1. Syntax of TEXTJOIN Function:

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

where:

• delimiter: Separator (e.g., , ; -) between texts users want to join. REQUIRED.
• ignore_empty: Users can ignore empty cells by using TRUE. REQUIRED
• text1: The first text value or range value to join. REQUIRED
• text2: The second text value or range to join. [This is OPTIONAL]

2. Join first and last names:

To join the first and last names, the formula is =TEXTJOIN(” “, TRUE, C6:D6), which joins the first and last names.

3. Join phone numbers with area code:

To join phone numbers with area codes, the formula is =TEXTJOIN(“-“, TRUE, C6:D6).

4. Join text with conditions:

Suppose, we want to see the names of students who scores more than 90 in one cell, the formula to join those students in one cell is =TEXTJOIN(“, “, TRUE,(IF(D6:D11>90, C6:C11, “”))), which makes the list of students (in cell E6) who scored more than 90.

5. Join names with PROPER Case:

To join names with proper case, the formula is =PROPER(TEXTJOIN(” “, TRUE,C6:D6)), which joins first and last names with PROPER case.

6. Create email address by joining first and last names:

To create common email by joining first and last names, the formula is =TEXTJOIN(“”,TRUE,C6:D6,”@gmail.com”), which joins first and last names with common email.

7. Join first, last, and middle names with some middle names missing:

To join first, last, and middle names with some middle names missing, the formula is =PROPER(IF(COUNTA(D6)=1, TEXTJOIN(” “, TRUE,C6:E6), TEXTJOIN(” “, TRUE,C6,E6))), which joins all parts of names in PROPER case.

More related readings:

1. MS Office Post on TEXTJOIN Function
2. Excel CONCAT Function
3. Extract first Name in excel (3 practical examples)
4. Extract Middle names (3 practical examples)
5. Excel ARRAYTOTEXT Function
6. Excel CLEAN Function with practical examples
7. Excel CONCAT Function to join text
8. Excel REPLACE Function to replace text
9. Excel TRIM Function with Practical Examples
10. Excel RIGHT Function