CONCATENATE

Merge multiple text strings in Excel in Excel
Merge multiple text strings

Rational

Merge multiple text strings.

Description

The Excel CONCATENATE function display text by merging multiple text strings. For example, to combine two separate texts – Indian & Ocean, the formula can be =CONCATENATE(“Indian “, “Ocean”) returns “Indian Ocean”. Note: space after Indian.

Syntaxt

= CONCATENATE(text1, [text2], …)

Parameters

text – One or more text string to join.

Return Value

Concatenated text

Key Notes

  • The CONCATENATE function combines multiple strings but not useful with text’s in range or array.
  • The function doesn’t provide the delimiter or ignore empty arguments.
  • The ampersand character (&) is an alternative to CONCATENATE.
  • The user can input a maximum of 255 text arguments in this formula.
  • Moreover, the Concatenated text should not exceed 8,192 characters (cell limit) otherwise it will return the #VALUE! error.
  • Introduced in Excel 2016 (Office 365), the CONCAT function replaced the CONCATENATE function. Although the CONCATENATE function is still available for backward compatibility but using CONCAT function is advisable.
for example, suppose that you have two columns with text and you wish  to combine both columns strings its not possible with CONCATENATE function for this we use CONCAT function.

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 »
Merge series of multiple ranges and/or text strings in Excel

CONCAT

The CONCAT function display text by combining multiple ranges, array and/or strings. I.e., =CONCAT(“Mike “, “Tyson”) returns “Mike Tyson”, note space after “Mike ”.

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