CONCAT

Merge series of multiple ranges and/or text strings in Excel
Merge multiple ranges and/or text strings

Rational

Merge series of multiple ranges and/or text strings.

Description

The Excel CONCAT function display text by combining multiple ranges, array and/or strings. For example, to connect two separate texts, Mike & Tyson, the formula can be =CONCAT(“Mike “, “Tyson”) returns “Mike Tyson”, note space after “Mike ”.

Syntax

=CONCAT(text1, [text2], …)

Parameters

text – One or more text string to join.

Return Value

Merged text

Key Notes

  • The CONCAT function can also merge data between table, array not just cells.
  • It doesn’t provide the delimiter or Ignore Empty arguments.
  • In some case, the ampersand character (&) can also be an effortless alternative to the CONCAT function, like =”Indian “& “Ocean” returns “Indian Ocean”.
  • The user can input a maximum of 253 text arguments in this formula. Moreover, the merged text should not exceed 32767 characters (cell limit) otherwise it will return the #VALUE! error.
  • Introduced in Excel 2016 (Office 365), the CONCAT function is a versatile version of CONCATENATE function.
for example, suppose that you have two columns with text and you wish  to combine both columns strings like here you have A and B columns (image above) for that we will apply formula =CONCAT(C15:C20,D15:D20) it return LEARNEXCEL.

Keep Reading Similar Functions:

Repeat same text in Excel

REPT

The Excel REPT function repeat a text for a specified number of times. For example, =REPT(“M”,3) returns “MMM”.

Read More »
To replace part of a text string in Excel

REPLACE

The REPLACE function substitute part of a text string with a new text string. I.e., =REPLACE(“Michael Jackson”,9,6,”Jorda”) returns “Michael Jordan”.

Read More »
Substitutes new text for old text in a text string in Excel

SUBSTITUTE

The SUBSTITUTE function replace the specific text in a text string that occurs in a specific location. i.e, =SUBSTITUTE(“Butter”,”t”,”l”,2) return “Butler”.

Read More »
Merge multiple text strings in Excel in Excel

CONCATENATE

The CONCATENATE function display text by merging multiple text strings. I.e., combine separate texts, =CONCATENATE(“Indian “, “Ocean”) returns “Indian Ocean”.

Read More »
Join multiple texts with a delimiter in Excel

TEXTJOIN

The TEXTJOIN function allows concatenating (join) the text from multiple ranges with a delimiter between each text value. E.g., =TEXTJOIN(” ,”,TRUE,”Blue”,”Green”,”White”,”Black”) return ” Blue ,Green ,White ,Black”.

Read More »