CONCAT function and TEXTJION in excel with help of an example

TEXTJOIN Function in Excel With Help Of An Example

The TEXTJOIN function in Excel is a newer function that was introduced in Excel 2016. It allows you to combine a range of cells into a single string of text, with a specified delimiter between each cell. This can be useful for creating lists or concatenating data from multiple cells into a single cell.

How to use the TEXTJOIN function:

The syntax for the TEXTJOIN function is as follows:

=TEXTJOIN(delimiter, ignore_empty, cell1, cell2, ...)

- "Delimiter" is the character that you want to use to separate the cells. This can be a text string, such as a comma or a space, or it can be a reference to a cell that contains the delimiter.

- "Ignore_empty" is an optional argument that specifies whether to ignore empty cells or not. If this argument is set to TRUE, empty cells will be ignored; if it is set to FALSE, empty cells will be included as empty strings in the resulting text.

- "Cell1, cell2, ..." are the cells that you want to combine. These can be individual cells, cell ranges, or arrays.

Examples:

Here are a few examples of using the TEXTJOIN function in Excel:

Example 1:

Suppose you have the following data in cells A1:A5:

A1: "apple"
A2: "orange"
A3: "" (an empty cell)
A4: "banana"
A5: "grape"

To combine these cells into a single string of text with commas as the delimiter, you could use the TEXTJOIN function as follows:

=TEXTJOIN(", ", TRUE, A1:A5)

This would return the text string "apple, orange, banana, grape" in the cell where the formula is entered, ignoring the empty cell in A3.

Example 2:

Suppose you have the following data in cells A1:A5:

A1: "apple"
A2: "orange"
A3: "" (an empty cell)
A4: "banana"
A5: "grape"

To combine these cells into a single string of text with semicolons as the delimiter, you could use the TEXTJOIN function as follows:

=TEXTJOIN("; ", FALSE, A1:A5)

This would return the text string "apple; orange; ; banana; grape" in the cell where the formula is entered, including the empty cell in A3 as an empty string.

Example 3:

Suppose you have the following data in cells A1:A5:

A1: "apple"
A2: "orange"
A3: "banana"
A4: "grape"
A5: "strawberry"

To combine these cells into a single string of text with dashes as the delimiter, you could use the TEXTJOIN function as follows:

=TEXTJOIN(" - ", TRUE, A1:A5)

This would return the text string "apple - orange - banana - grape - strawberry" in the cell where the formula is entered.

Comparing TEXTJOIN to CONCAT:

The CONCAT function in Excel is similar to the TEXTJOIN function, in that it allows you to combine cells into a single string of text. However, there are a few key differences between the two functions:

• The CONCAT function requires you to list each cell or range separately, while the TEXTJOIN function allows you to specify a range of cells all at once. This can make the TEXTJOIN function more efficient and easier to use when working with large data sets.
• The CONCAT function does not have an option to ignore empty cells, while the TEXTJOIN function does. This means that you would need to use an additional function, such as IFERROR, to handle empty cells when using CONCAT.
• The TEXTJOIN function is only available in Excel 2016 and later, while the CONCAT function has been available in earlier versions of Excel.

Considering these differences, the TEXTJOIN function may be the more convenient choice when working with large data sets that may contain empty cells, and when using a more recent version of Excel. However, the CONCAT function may still be a useful option in other cases, such as when working with earlier versions of Excel or when you want more control over the individual cells or ranges being combined.

Additional Tips:

Here are a few additional tips for using the TEXTJOIN function in Excel:

• You can use the TEXTJOIN function to combine cells with other types of data, such as numbers, in addition to text.
• The delimiter can be any text string, including a space, a comma, a semicolon, or even a line break (using the CHAR function).
• You can use the TEXTJOIN function to combine cells from multiple worksheets or even multiple workbooks.
• You can use the TEXTJOIN function in combination with other functions, such as IF or SUM, to create more complex formulas.

I hope this blog has been helpful in explaining the TEXTJOIN function in Excel. Let me know if you have any questions or if you'd like to see any additional examples.

Next - Use of Left Right and Mid Functions