Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print
Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print

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:

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 »
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 »
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 »
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 »
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 »