The TEXTJOIN function is one of the (TEXT) functions of Excel. It concatenates a list or range of
text strings using a delimiter.
We can find this function in TEXT of insert function Tab.
How to use TEXTJOIN function in excel
- Click on empty cell (like F5 )
2. Click on fx on the below of font word (or press shift+F3)
3. In insert function tab you will see all functions
4. Select TEXT category
5. Select TEXTJOIN function
6. Then select ok
7. In function arguments Tab you will see TEXTJOIN function
8. Delimiter: Character or string to insert between each text item
9. Ignore empty: if TRUE(default), ignores empty cells
10. Text1: text1,text2,… are 1 to 252 text strings or ranges to be joined
11. You will see the results in formula result section
Examples of TEXTJOIN function in Excel
- To join text values from cells A1 to A5 with a comma and space separator: =TEXTJOIN(“, “,TRUE,A1:A5)
- To join text values from cells A1 to A5 with no separator, ignoring empty cells: =TEXTJOIN(“”,TRUE,A1:A5)
- To join text values from cells A1 to A5 with a line break separator: =TEXTJOIN(CHAR(10),TRUE,A1:A5)
- To join text values from cells A1 to A5 with a custom separator: =TEXTJOIN(“; “,TRUE,A1:A5)
- To join text values from cells A1 to A5, ignoring any cells that contain errors: =TEXTJOIN(“, “,TRUE,IF(ISERROR(A1:A5),””,A1:A5))
- To join text values from cells A1 to A5, ignoring any cells that are blank or contain errors: =TEXTJOIN(“, “,TRUE,IF(A1:A5=””,””,IF(ISERROR(A1:A5),””,A1:A5)))
- To join text values from cells A1 to A5 that contain specific text: =TEXTJOIN(“, “,TRUE,IF(ISNUMBER(SEARCH(“apple”,A1:A5)),A1:A5,””))
- To join text values from cells A1 to A5 with a custom separator, but only if they are not already joined: =IF(COUNTIF(B:B,A1)=0,A1&”; “&TEXTJOIN(“; “,TRUE,IF($B1:1:B100=A1,100=A1,C1:1:C$100,””)),””) (where column B contains previously joined values)
- To join text values from cells A1 to A5 with a custom separator, but only if they meet certain criteria (e.g. value is above a certain threshold): =TEXTJOIN(“; “,TRUE,IF(A1:A5>10,A1:A5,””))
- To join text values from cells A1 to A5 with a custom separator, but only up to a certain number of characters: =LEFT(TEXTJOIN(“, “,TRUE,A1:A5),50)
How do I use the TEXTJOIN function to concatenate text in Excel?
The TEXTJOIN function in Excel is used to concatenate or join text strings from a range of cells, using a specified delimiter to separate the text values. The syntax for the TEXTJOIN function is as follows:
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
- Delimiter: This is the character or string that will be used to separate each text value in the resulting string. It can be any valid text string enclosed in double quotes, such as “, “, “; “, or “-“.
- Ignore_empty: A logical value (TRUE or FALSE) that specifies whether empty cells should be ignored or included in the resulting string.
- Text1: This is the first text value or cell reference that you want to include in the resulting string.
- [Text2]: This is an optional argument that allows you to add additional text values or cell references to the function.
Here are some examples of how to use the TEXTJOIN function to concatenate text in Excel:
Example 1: Suppose you have the following values in cells A1:A5: “apple”, “banana”, “cherry”, “”, “orange”. If you want to concatenate these values with a comma and space separator, you can use the following formula:
=TEXTJOIN(", ", TRUE, A1:A5)
The result will be: “apple, banana, cherry, orange”
Example 2: Suppose you want to concatenate only the non-empty cells in the same range as above, but still using a comma and space separator. You can use the following formula:
=TEXTJOIN(", ", TRUE, A1,A2,A3,A5)
The result will be: “apple, banana, cherry, orange”
Example 3: Suppose you have different text values in cells A1:A4, and you want to concatenate them using a dash separator. You can use the following formula:
=TEXTJOIN("-", TRUE, A1:A4)
If the values in those cells are “John”, “Doe”, “0123456”, and “gmail.com“, then the result will be: “John-Doe-0123456-gmail.com“
Example 4: Suppose you want to concatenate text values from different parts of your worksheet. For example, suppose you have a list of first names in column A, and last names in column B, and you want to create a list of full names with a space between them. You can use the following formula:
=TEXTJOIN(" ", TRUE, A1:A5&" "&B1:B5)
If the values in column A are “John”, “Jane”, “Bob”, “Sue”, and “Mike”, and the values in column B are “Smith”, “Doe”, “Johnson”, “Jones”, and “Williams”, then the result will be: “John Smith Jane Doe Bob Johnson Sue Jones Mike Williams”
Example 5: Suppose you want to include only certain text values that meet a specific criterion, and concatenate them using a comma and space separator. For example, suppose you have a list of fruits in column A, and you want to create a list of fruits that are not bananas or oranges. You can use the following formula:
=TEXTJOIN(", ", TRUE, IF((A1:A5<>"banana")*(A1:A5<>"orange"), A1:A5,""))
If the values in column A are “apple”, “banana”, “cherry”, “orange”, and “grape”, then the result will be: “apple, cherry, grape”
What are the arguments for the TEXTJOIN function in Excel?
The TEXTJOIN function in Excel has multiple arguments that allow you to customize the way in which text values are concatenated. Here is a complete explanation of each argument:
- Delimiter – This is the character or string that will be used to separate each text value in the resulting string. It can be any valid text string enclosed in double quotes, such as “, “, “; “, or “-“.
- Ignore_empty – A logical value (TRUE or FALSE) that specifies whether empty cells should be ignored or included in the resulting string. If set to TRUE, the function will ignore any empty cells in the range.
- Text1 – This is the first text value or cell reference that you want to include in the resulting string. You can enter up to 252 text values or cell references separated by commas.
- [Text2], [Text3], … – These are optional arguments that allow you to add additional text values or cell references to the function. You can enter up to 252 additional text values or cell references separated by commas.
Note that the TEXTJOIN function requires at least one text value or cell reference to work properly. If you attempt to use it without specifying any text values, the function will return a #VALUE! error.
Here are some examples of how to use the TEXTJOIN function with different arguments in Excel:
Example 1: Concatenating text values with a comma and space separator
=TEXTJOIN(", ", TRUE, A1:A5)
This formula uses a comma and space separator to concatenate the text values in cells A1 through A5. The TRUE argument tells the function to ignore any empty cells in the range.
Example 2: Concatenating text values with a custom separator
=TEXTJOIN("->", FALSE, A1:A5)
This formula uses a custom separator (an arrow with a hyphen) to concatenate the text values in cells A1 through A5. The FALSE argument tells the function to include any empty cells in the range.
Example 3: Concatenating text values from multiple ranges
=TEXTJOIN(", ", TRUE, A1:A5, C1:C5)
This formula concatenates the text values in cells A1 through A5 and C1 through C5 using a comma and space separator. You can add up to 252 additional ranges to the function by separating them with commas.
Example 4: Ignoring blank spaces when concatenating text values
=TEXTJOIN(", ", TRUE, A1:A5, "")
This formula concatenates the text values in cells A1 through A5 and ignores any blank spaces. The second argument is set to TRUE, which tells the function to ignore empty cells but not blank spaces.
Can the TEXTJOIN function ignore empty cells or cells with errors?
Yes, the TEXTJOIN function in Excel can ignore empty cells or cells with errors if you specify the appropriate argument.
The second argument of the TEXTJOIN function is called “ignore_empty”, and it is a logical value that defaults to TRUE. When this argument is set to TRUE, the function will automatically ignore any empty cells in the range that you are concatenating. This means that the resulting string will not include any extra delimiters where there were empty cells.
For example, suppose you have the following list of values in cells A1:A5:
apple
banana
cherry
orange
If you want to concatenate these values with a comma and space separator using the TEXTJOIN function, you can use the following formula:
=TEXTJOIN(", ", TRUE, A1:A5)
The resulting string will be:
apple, banana, cherry, orange
Note that the empty cell between “banana” and “cherry” is ignored, and there is no extra delimiter in the resulting string.
Similarly, if there are cells with errors in the range that you are concatenating, you can use the IFERROR function to replace those errors with a blank string. For example, suppose you have the following list of values in cells A1:A5:
apple
=1/0
cherry
orange
If you try to concatenate these values with a comma and space separator using the TEXTJOIN function, you will get a #DIV/0! error because of the second cell. To ignore this error and any other errors that may appear in the range, you can use the IFERROR function to replace them with a blank string. Here is the formula:
=TEXTJOIN(", ", TRUE, IFERROR(A1:A5,""))
The resulting string will be:
apple, cherry, orange
Note that the cell with the error (“#DIV/0!”) is replaced by a blank string in the resulting string.
In summary, you can use the TEXTJOIN function to ignore empty cells or cells with errors in Excel by setting the “ignore_empty” argument to TRUE, and using the IFERROR function to replace any errors with a blank string.
How do I use the TEXTJOIN function to concatenate text with a delimiter in Excel?
The TEXTJOIN function in Excel can be used to concatenate text with a delimiter, or separator, between each of the text values. The delimiter can be any character or string that you specify, such as a comma, space, dash, or any other character.
Here’s an example of how to use the TEXTJOIN function to concatenate text with a delimiter:
Suppose you have a list of fruits in cells A1 through A4: “apple”, “banana”, “cherry”, and “orange”. You want to concatenate these values with a comma and space separator. Here’s the formula you would use:
=TEXTJOIN(", ", TRUE, A1:A4)
This formula uses the comma and space characters (“, “) as the delimiter to join the text values in cells A1 through A4. The second argument is set to TRUE, which tells the function to ignore any empty cells in the range.
The resulting string will be:
apple, banana, cherry, orange
You can customize the delimiter to be any character or string you like. Here are a few more examples:
Example 1: Concatenating text values with a dash separator
Suppose you have a list of names in cells A1 through A4: “John”, “Jane”, “Bob”, and “Sue”. You want to concatenate these values with a dash separator. Here’s the formula you would use:
=TEXTJOIN("-", TRUE, A1:A4)
This formula uses the dash character (“-“) as the delimiter to join the text values in cells A1 through A4. The second argument is set to TRUE, which tells the function to ignore any empty cells in the range.
The resulting string will be:
John-Jane-Bob-Sue
Example 2: Concatenating text values with a custom separator
Suppose you have a list of email addresses in cells A1 through A4: “john.smith@gmail.com“, “jane.doe@yahoo.com“, “bob.johnson@hotmail.com“, and “sue.jones@aol.com“. You want to concatenate these values with a custom separator (a vertical bar). Here’s the formula you would use:
=TEXTJOIN("|", TRUE, A1:A4)
This formula uses the vertical bar character (“|”) as the delimiter to join the text values in cells A1 through A4. The second argument is set to TRUE, which tells the function to ignore any empty cells in the range.
The resulting string will be:
john.smith@gmail.com|jane.doe@yahoo.com|bob.johnson@hotmail.com|sue.jones@aol.com
Can I use the TEXTJOIN function to concatenate text from a range of cells in Excel?
Yes, you can use the TEXTJOIN function in Excel to concatenate text from a range of cells. This can be useful if you want to combine multiple pieces of text into a single cell or string.
Here’s an example of how to use the TEXTJOIN function to concatenate text from a range of cells:
Suppose you have a list of names in cells A1 through A4: “John”, “Jane”, “Bob”, and “Sue”. You want to concatenate these names into a single cell or string. Here’s the formula you would use:
=TEXTJOIN(", ", TRUE, A1:A4)
This formula uses the comma character (“, “) as the delimiter to join the text values in cells A1 through A4. The second argument is set to TRUE, which tells the function to ignore any empty cells in the range.
The resulting string will be:
John, Jane, Bob, Sue
Note that the TEXTJOIN function can handle up to 252 text values or cell references, so you can concatenate a large number of cells if needed.
You can also use the TEXTJOIN function to concatenate text from non-contiguous ranges of cells. For example, suppose you have a list of first names in cells A1 through A5, and a list of last names in cells C1 through C5. You want to concatenate these names into a single cell or string with a space between each first and last name. Here’s the formula you would use:
=TEXTJOIN(" ", TRUE, A1:A5&" "&C1:C5)
This formula concatenates the first names in cells A1 through A5 with the corresponding last names in cells C1 through C5, separated by a space character (” “). The ampersand (&) operator combines the two cell ranges, and the double quotes (” “) insert a space between the first and last names.
The resulting string will be:
John Smith Jane Doe Bob Johnson Sue Jones
How do I use the TEXTJOIN function to concatenate text with a line break in Excel?
In Excel, you can use the TEXTJOIN function to concatenate text with a line break (or a new line) between each of the text values. This is useful when you want to create a list or paragraph of text with multiple lines.
Here’s an example of how to use the TEXTJOIN function to concatenate text with a line break:
Suppose you have a list of items in cells A1 through A4: “apple”, “banana”, “cherry”, and “orange”. You want to concatenate these items into a single cell or string with a line break between each item. Here’s the formula you would use:
=TEXTJOIN(CHAR(10), TRUE, A1:A4)
This formula uses the CHAR function with the code 10 to insert a line break character between the text values in cells A1 through A4. The second argument is set to TRUE, which tells the function to ignore any empty cells in the range.
Note that you should not type the line break character directly into the formula. Instead, you should use the CHAR function to generate the line break character.
The resulting string will be:
apple
banana
cherry
orange
Note that each item appears on a separate line.
You can also customize the delimiter to be any character or string you like. Here are a few more examples:
Example 1: Concatenating text values with a line break and a custom separator
Suppose you have a list of names in cells A1 through A4: “John”, “Jane”, “Bob”, and “Sue”. You want to concatenate these names into a single cell or string with a line break between each name, and a dash separator between the first and last names. Here’s the formula you would use:
=TEXTJOIN(CHAR(10), TRUE, A1:A4&"-"&C1:C4)
This formula concatenates the first names in cells A1 through A4 with the corresponding last names in cells C1 through C4, separated by a dash character (“-“), and adds a line break character between each name. The ampersand (&) operator combines the two cell ranges, and the double quotes (” “) insert a space between the first and last names.
The resulting string will be:
John-Smith
Jane-Doe
Bob-Johnson
Sue-Jones
Example 2: Concatenating text values with a line break and a tab separator
Suppose you have a list of countries in cells A1 through A5: “USA”, “Canada”, “Mexico”, “Brazil”, and “Argentina”. You want to concatenate these countries into a single cell or string with a line break between each country, and a tab separator between the country names and their populations. Here’s the formula you would use:
=TEXTJOIN(CHAR(10), TRUE, A1:A5&CHAR(9)&B1:B5)
This formula concatenates the country names in cells A1 through A5 with their corresponding populations in cells B1 through B5, separated by a tab character (CHAR(9)), and adds a line break character between each country name.
The resulting string will be:
USA 328.2 million
Canada 37.6 million
Mexico 132.3 million
Brazil 214.5 million
Argentina 45.8 million
Can the TEXTJOIN function handle different types of data in Excel, such as numbers and dates?
Yes, the TEXTJOIN function in Excel can handle different types of data, including numbers and dates. When using the TEXTJOIN function to concatenate non-text data, Excel will automatically convert the values to text format before joining them into a string.
Here’s an example of how to use the TEXTJOIN function with numbers and dates:
Suppose you have a list of sales figures in cells A1 through A4, and a list of dates in cells B1 through B4:
A B
-----------------------
100 01/01/2022
200 02/01/2022
03/01/2022
400 04/01/2022
You want to concatenate the sales figures and dates into a single cell or string, separated by a comma and space. Here’s the formula you would use:
=TEXTJOIN(", ", TRUE, A1:A4&" - "&B1:B4)
This formula concatenates the sales figures in cells A1 through A4 with the corresponding dates in cells B1 through B4, separated by a dash and a space character (“- “), and adds a comma and space character (“, “) between each pair. The ampersand (&) operator combines the two cell ranges, and the double quotes (” “) insert a space between the dash and the date.
The resulting string will be:
100 - 01/01/2022, 200 - 02/01/2022, - 03/01/2022, 400 - 04/01/2022
Note that the empty cell in B3 is still included in the resulting string, but the value is converted to an empty string. If you want to exclude any empty cells from the concatenation, you can set the second argument of the TEXTJOIN function to FALSE instead of TRUE.
How do I use the TEXTJOIN function to concatenate text with a custom separator in Excel?
In Excel, you can use the TEXTJOIN function to concatenate text with a custom separator or delimiter. This is useful when you want to join text values together into a single cell or string, but you want to use a specific character or string to separate the values.
Here’s an example of how to use the TEXTJOIN function to concatenate text with a custom separator:
Suppose you have a list of cities in cells A1 through A4: “New York”, “London”, “Tokyo”, and “Sydney”. You want to concatenate these cities into a single cell or string with a forward slash separator (“/”). Here’s the formula you would use:
=TEXTJOIN("/", TRUE, A1:A4)
This formula uses the forward slash character (“/”) as the separator to join the text values in cells A1 through A4. The second argument is set to TRUE, which tells the function to ignore any empty cells in the range.
The resulting string will be:
New York/London/Tokyo/Sydney
Note that the separator is inserted between each pair of values, except for the last value.
You can customize the separator to be any character or string you like. Here are a few more examples:
Example 1: Concatenating text values with a space separator and parentheses
Suppose you have a list of fruits in cells A1 through A5: “apple”, “banana”, “cherry”, “orange”, and “pineapple”. You want to concatenate these fruits into a single cell or string with a space separator and parentheses around each fruit name. Here’s the formula you would use:
=TEXTJOIN(" ", TRUE, "("&A1:A5&")")
This formula concatenates each fruit name in cells A1 through A5 with parentheses around it, and adds a space separator between each pair. The ampersand (&) operator combines the cell range with the parentheses, and the double quotes (” “) insert a space between each pair.
The resulting string will be:
(apple) (banana) (cherry) (orange) (pineapple)
Example 2: Concatenating text values with a hyphen separator and the IF function
Suppose you have a list of prices in cells A1 through A5, but some of the cells are empty. You want to concatenate these prices into a single cell or string with a hyphen separator (“-“), but you don’t want to include any empty cells. Here’s the formula you would use:
=TEXTJOIN("-", TRUE, IF(A1:A5<>"",A1:A5,""))
This formula uses the IF function to check whether each cell in the range A1 through A5 is not empty. If a cell is not empty, its value is included in the concatenation; otherwise, an empty string is used instead. The hyphen character (“-“) is used as the separator, and the second argument is set to TRUE to ignore any empty cells.
The resulting string will be:
10-20-30-40
How can I troubleshoot issues with the TEXTJOIN function not working properly in Excel?
The TEXTJOIN function in Excel is a powerful tool for concatenating text values, but like any formula, it’s possible to encounter issues or errors while using it. Here are some common troubleshooting steps you can take if you’re having trouble with the TEXTJOIN function:
- Check the syntax and arguments of the formula: Make sure that you’ve entered the formula correctly, including the correct cell references and separators. Double-check the function signature and argument order as well.
- Verify that the function is supported in your version of Excel: The TEXTJOIN function was introduced in Excel 2016, so make sure you’re using a version of Excel that supports the function.
- Ensure that the range of cells is valid: If you’re using the TEXTJOIN function to concatenate text from a range of cells, make sure that the range is valid and does not include any empty cells or cells with errors. You can use the IFERROR function to handle errors within the range.
- Check for special characters: If your text values include special characters such as quotes, commas, or other delimiters, you may need to escape or enclose those characters within double quotes.
- Confirm that the delimiter character is correct: If you’re using a custom delimiter, such as a hyphen or forward slash, make sure that the delimiter is specified correctly within the formula. You can also try changing the delimiter to a different character to see if that resolves the issue.
- Use the Evaluate Formula tool: The Evaluate Formula tool in Excel allows you to step through a formula and evaluate each part of the calculation. This can help you identify where the problem lies and debug the formula.
- Try an alternative method: If the TEXTJOIN function is not working properly, you may want to try an alternative method for concatenating text, such as the CONCATENATE function or the “&” operator.
By following these troubleshooting tips, you should be able to identify and resolve any issues you encounter while using the TEXTJOIN function in Excel.
