CONCATENATE in Excel: How to combine text strings

To concatenate multiple text from different cells in Excel into a word or sentence, you can use function and operator:

• CONCATENATE

• &

The purpose of combining these texts is to make your task easier. So you don’t have to rewrite the texts.

Join text with the CONCATENATE function

CONCATENATE (text1, [text2], …)

text1, text2, and so on are text items to be combined. You can combine a total of up to 253 combined text items. The total number of characters that you can create (concatenated) is up to 32.767 characters.

Example 1: Using the CONCATENATE function

In example 1 above the PIC column is generated from combining text in the ID, Position, and Name columns.

In the above formula, cell E4 is written with the formula =CONCATENATE (B4, “. “, C4, “: “, D4) to produce text 1. Project Manager: Andi

When combining text items or numbers, you can concatenate text taken from specific cells or directly write text into formula. In the example above the text is taken from cells B4, C4, and D4. If the text is not from a cell and is not a Number, you need to add quotation marks.

In another example, in Example 2, if you are concatenating text which consists of letters or words, you need to add quotation marks around the text. If the text is a number, you do not need to enclose it in quotation marks.

Example 2: CONCATENATE (1, “. “, “Engineer”, ” “, “John”)

Note: By default, the CONCATENATE function does not provide spaces between text when you concatenate text. You need to add a space character between the text to separate text1 and other text. This has been implemented in Example 2 above.

Also read how to calculate age in Excel

Join text using the & Operator

Apart from using the Concatenate function, you can also use the & operator to concatenate text from multiple cells.

How to combine text with the & operator is as follows:

text1 & text2 & text3

Examples of combining text using the & operator

In the example above the formula written in cell F is written as F4 = B4 & “. ” & C4 & “: ” & D4

The concatenation result in the example above is the same as the concatenation result in the previous example of the CONCATENATE function.

The number of characters that you can create (concatenated) is up to 32.767 characters.

Join text using the CONCATENATE function and the & Operator

You can also use the CONCATENATE function and the & operator at the same time as in the following example:

Add Line Break or CHAR (10) to the join text

To add line breaks to the text, you can insert CHAR (10) in the function. Don’t forget to enable text wrapping. The results can be seen as in the following example:

The example above can also be created using the & operator. If you use the & operator, here’s the formula:

B5 = B1 & CHAR (10) & B2 & CHAR (10) & B3

Join Text Arrays with CONCATENATE without a Separator

Suppose you want to concatenate text from excel cells from B1 to B10. You cannot use this formula = CONCATENATE (B1: B10), because the value shown is only text on B10. So you need to input individual cells to concatenate the text. If you have data on a lot of cells, it will take time and make your hands sore.

There are other, more practical ways so that your hands don’t get sore entering cell addresses one by one. You can follow these steps:

• Use the TRANSPOSE function, so the formula is = TRANSPOSE (B1: B10)

• Select the whole formula as follows (marked in blue): = TRANSPOSE (B1: B10)

• When selected, press Fn + F9.

• Formula will automatically be converted to values ​​(text) separated by commas in {}.

• Remove the {} sign, and enter the conversion result into the CONCATENATE function.

• The results are as follows:

Join Text Arrays with CONCATENATE with Separator

Suppose you want to concatenate text from excel cells from B1 to B10. So that your hands don’t get sore entering cell addresses one by one you can follow these steps:

• Use the TRANSPOSE function, so that the formula entered is = TRANSPOSE (B1: B10) & ” “

• Select the whole formula as follows (marked in blue): = TRANSPOSE (B1: B10) & " "

• When selected, press Fn + F9.

• Formula will automatically be converted to values ​​(text) separated by commas in {}.

• Remove the {} sign, and enter the conversion result into the CONCATENATE function.

• The results are as follows:

Note: Apart from separators, you can also add other characters such as commas or hyphens. An example is like this:

= TRANSPOSE (B1: B10) & “,”

or

= TRANSPOSE (B1: B10) & “-“

Join Text with CONCAT (Microsoft 365, Excel 2019 or later)

In Microsoft Office 2019 or Microsoft 365, you will find the CONCAT function. In the CONCAT function, you can enter an array of cells directly without using the TRANSPOSE function as in the previous example.

Here’s an example of using CONCAT in Excel:

If you read the CONCATENATE help manual in Excel 2016 (standalone version), it says that Microsoft will likely replace the CONCATENATE function with the CONCAT function in later versions of Excel.

Join Text with TEXTJOIN (Microsoft 365, Excel 2019 or later)

TEXTJOIN has the following formula written:

TEXTJOIN (delimiter, ignore_empty, text1, [text2],…)

I took the explanation of the formula above from one of the Microsoft Support page, as shown in the table below:

For the record, if the concatenated character string exceeds 32.767, it will produce an error value.