CONCAT(), TEXTJOIN() in Advanced Excel

In Excel, the CONCAT() and TEXTJOIN() functions are powerful tools that help you combine (or concatenate) text from multiple cells into a single string. These functions are particularly useful when you need to combine names, addresses, or any other information spread across different cells into one.

CONCAT() Function

The CONCAT() function is used to combine two or more text strings into one. This function is available in Excel 2016 and later versions.

CONCAT() Syntax

CONCAT(text1, text2, ...)
  • text1, text2, ...: These are the text strings or cell references that you want to combine.

Real-Time Example of CONCAT()

Scenario: You have a list of first names in column A and last names in column B, and you want to combine them into full names in column C.

        A         B
        John      Doe
        Mary      Smith
        James     Brown

Steps:

  1. In cell C1, enter the following formula:
    2. =CONCAT(A1, " ", B1)
  2. Explanation: This formula combines the first name and last name in columns A and B, with a space between them. For "John" and "Doe", the result will be "John Doe".

TEXTJOIN() Function

The TEXTJOIN() function is more flexible than CONCAT() because it allows you to specify a delimiter (such as a comma or space) between the text strings and can ignore empty cells.

TEXTJOIN() Syntax

TEXTJOIN(delimiter, ignore_empty, text1, text2, ...)
  • delimiter: The character or characters you want to use to separate the text strings (e.g., a space, comma, semicolon).
  • ignore_empty: A boolean value (TRUE or FALSE). If TRUE, empty cells will be ignored.
  • text1, text2, ...: These are the text strings or cell references that you want to combine.

Real-Time Example of TEXTJOIN()

Scenario: You have a list of first names, middle names, and last names, and you want to combine them into a full name in column D. You also want to separate the names with spaces, but ignore any empty middle names.

        A         B         C
        John      Michael   Doe
        Mary      (blank)   Smith
        James     David     Brown

Steps:

  1. In cell D1, enter the following formula:
    2. =TEXTJOIN(" ", TRUE, A1, B1, C1)
  2. Explanation: This formula combines the first name, middle name, and last name with spaces in between. The "TRUE" argument tells Excel to ignore the empty middle names. For "John", "Michael", and "Doe", the result will be "John Michael Doe", for "Mary", "(blank)", and "Smith", the result will be "Mary Smith".

Key Differences Between CONCAT() and TEXTJOIN()

  • CONCAT(): Combines text strings without any delimiter unless you manually specify one.
  • TEXTJOIN(): Allows you to specify a delimiter between the text strings and can ignore empty cells. This makes it more flexible for combining text.

Additional Real-Time Example of TEXTJOIN() with Data from Multiple Cells

Scenario: You want to create a list of items from columns A, B, and C into one cell with commas separating the items.

        A        B        C
        Apple    Banana   Cherry
        Grape    Mango    Kiwi

Steps:

  1. In cell D1, enter the following formula:
    2. =TEXTJOIN(", ", TRUE, A1:C1)
  2. Explanation: The formula will combine all the items in the range A1:C1, separating them with commas. For the first row, the result will be "Apple, Banana, Cherry".

Conclusion

The CONCAT() and TEXTJOIN() functions are both powerful tools in Excel for combining text strings from multiple cells. While CONCAT() is simpler and combines values without delimiters, TEXTJOIN() offers greater flexibility with delimiters and the option to ignore empty cells. Both functions help automate and streamline the process of merging data, making it easier to create custom reports, lists, and messages.




Advertisement