The CONCAT is a Text function that joins strings together. When using concatenate in Excel, you can enter static strings, a range of cells to join, and individual cell references. In this guide, we’re going to show you how to use the CONCAT function to merge cells in Excel and also go over some error handling methods.

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.