How to use TEXTJOIN function in Excel?

This submit explains how to use TEXTJOIN function in Excel. TEXTJOIN is a Textual content function in Excel that joins (or concatenates) textual content strings from two or extra cells and separates the mixed worth utilizing a delimiter. It’s a extra highly effective different to the concatenation operator (&) or the CONCATENATE function, that are two different methods of concatenating strings in Excel. TEXTJOIN function is out there in Excel for Workplace 365, Excel 2019, and Excel 2021.

The syntax of the TEXTJOIN function is as follows:

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

The place, 

• delimiter is the character that separates the concatenated values. This can be a required argument. It takes a textual content string enclosed inside double quotes or a reference to a textual content string as its worth. Comma (“,”), area (” “), and hyphen (“-“) are some of the examples used as delimiters in the TEXTJOIN function. An empty string (“”) could also be specified to use no delimiter.
• ignore_empty takes both TRUE or False, the place TRUE is the default worth. It tells Excel whether or not to embrace or ignore empty cells in the resultant string. False retains the empty values and True ignores them. That is additionally a required argument.
• text1 takes the principle string worth that wants to be merged. This argument can also be required. It may be a textual content string, a reference to the cell containing the textual content string, or an array of strings (a reference to a cell vary).
• [text2], … are further (and optionally available) textual content values that want to be merged with the principle string. You might be part of up to 252 strings collectively, together with text1.

How to use TEXTJOIN function in Excel?

Allow us to see how to use the TEXTJOIN function in Excel.

Instance 1: Utilizing TEXTJOIN utilizing a single delimiter

Suppose we now have a workbook with worker knowledge (worker names) of a company, as proven in the above picture. Column A comprises the primary names of the workers, column B comprises center names of the workers, and column C comprises final names of the workers. To affix these values as full names, we will use the TEXTJOIN function in cell D3 as:

=TEXTJOIN(" ", TRUE, A3:C3)

For the primary row (Row 3), the function will return ‘Laxman+” “+(empty_value)+” “+Singh’

Since the ignore_empty argument is set to TRUE, it will ignore any empty values within the specified cell range. So the final result will be:

‘Laxman+” “+Singh’, which becomes ‘Laxman Singh’, as shown in the above image.

On the other hand, if we set ignore_empty as FALSE, the function will return ‘Laxman  Singh’, which includes 2 spaces, taking into consideration the empty value as well.

Tip: To repeat the same formula for all the rows, you can take your mouse pointer to the bottom-right corner of cell D3 (which contains the TEXTJOIN formula), and as the pointer turns into plus (+) sign, click and then drag the pointer till cell D6. The same formula will apply to cells D4 through D6 when you release the mouse pointer.

Example 2: Using TEXTJOIN using multiple delimiters

Now suppose we have another datasheet where Column A contains last names of the employees, column B contains first names of the employees, and column C contains middle names of the employees of an organization, as shown in the above image. If we want the full names in the format Last name,First name Middle name, we may use the TEXTJOIN function as follows:

=TEXTJOIN({","," "}, TRUE, A3:C3)

Here, we have supplied more than 1 delimiter (comma and space) to join the string values. So the function will pick cell value A3 (Singh), concatenate it with a comma character(Singh+”,”), then decide cell worth B3 and concatenate it with area character(Singh+”,”+Laxman+” “), and then concatenate this entire value with the value of cell C3(Singh+”,”+Laxman+” “+””). The outcomes will now seem in the specified format(Singh,Laxman ), as proven in the above picture.

Notes:

1. If the size of the resultant strings exceeds 32767 characters (which is the restrict of a cell in Excel), the TEXTJOIN function will return #VALUE! error.
2. If delimiter argument shouldn’t be provided or is empty,(“”), the function will be part of the cell values and not using a delimiter.
3. To merge date values with string values, you want to first convert the date into textual content format utilizing the TEXT function.
4. To insert line break between string values, use CHAR(10) because the delimiter and activate the ‘Wrap Text’ format for the cell. 10 is the ASCII character code for linefeed.
5. A quantity provided as delimiter or textual content in the TEXTJOIN function is transformed to textual content throughout the concatenation course of.

That is how one can use the TEXTJOIN function to mix knowledge from a variety of cells. When you want to reverse the adjustments, you possibly can use the TEXTSPLIT function to break up the string on the premise of given delimiters. 

Hope you discover this convenient.

Why is my TEXTJOIN formulation not working?

The TEXTJOIN formulation is probably not working in your system if you happen to’re having a model of Excel that doesn’t help this formulation. TEXTJOIN is barely obtainable in Excel 2019 and later. You can too use it if you happen to use Excel for Workplace 365. On another model (Excel 2016 or earlier), it’s going to present the #NAME? error. 

How do I mix two textual content cells in Excel?

You’ll be able to mix two textual content cells in Excel utilizing the TEXTJOIN function. Irrespective of whether or not you need to be part of two or greater than two cells, you possibly can use this function to get the job achieved. An in depth information on TEXTJOIN function is talked about right here and it is strongly recommended to observe it.

Learn Subsequent: Convert Handwritten Math to Textual content utilizing Ink to Math in Workplace.