CONCAT Function in Excel

In the dynamic realm of Excel functions, the CONCAT function stands out as a versatile tool for seamlessly joining strings. Whether you're working with static values, cell references, or entire ranges, CONCAT empowers you to merge them effortlessly. This guide delves into the intricacies of the CONCAT function in Excel, exploring its syntax, supported versions, and providing hands-on examples. As we navigate through the merging capabilities, we'll also touch upon essential error handling methods. If you're keen on optimizing your string manipulation skills in Excel, read on to master the art of CONCAT.

Supported versions

Excel 2016 and later

CONCAT Function Syntax

CONCAT(text1, [text2], ...)

Arguments

text1	The static values, cell references, or range references for the strings to be joined.
[text2, …]	Optional. Additional strings - up to 255 items can be joined together.

Examples

How to merge cells using ranges

=CONCAT(B3:D3)

function merges all strings in the cells of the range B3:D3. Please note that the CONCAT function doesn't support delimiters. For example, if there were no space characters in front of Text 2 and Text 3, the output would be "By thePower of Grayskull...I Have the Power!".

How to merge cells using cell reference and static text

The CONCAT function can be used with single cells, as well as formulas or static arguments. The CONCATENATE function works the same way.

In the following formula, cells B7 and C7 are merged with a new line character (CHAR(10)) and the text ends with an exclamation mark (!).

=CONCAT(B7,CHAR(10),C7,"!")

Download Workbook

Summary and Tips

The CONCAT function is a replacement for the CONCATENATE function. You can't join an array of strings using the CONCATENATE.
Although Excel continues to support the CONCATENATE function, Microsoft recommends not using this function as it might be retired in a future update.
Use the TEXTJOIN function to include delimiters between joined strings.

Issues

If the return string exceeds the maximum allowed character for an Excel cell, the formula return #VALUE! error. The limit is 32,767 at the time of writing this article.

In conclusion, mastering the art of concatenation in Excel opens up a world of possibilities for efficient data handling. The CONCAT function, with its straightforward syntax and diverse application, proves to be an invaluable asset for anyone navigating the complexities of Excel workbooks. As we bid farewell, remember that while CONCATENATE may still linger in the Excel toolbox, the CONCAT function emerges as a recommended successor, promising enhanced functionality and compatibility.