If you want to download the supporting material used in this article, you can click here.

Concatenate or concatenation means joining two text strings together. The word originates from the Latin expressions con and catena, meaning “chaining together.” In this article, we will explain how to concatenate in Excel.

Concatenation simply means joining two – or more – text strings to create a new one. For this purpose in Excel, you can use the CONCAT() function and the logical operator &, also called the ampersand. Often you need to split the contents of a cell or, conversely, combine data from two or more columns into a single column. Typical examples include joining first names with last names, combining street addresses with cities, or merging dates with times.

There are basically two ways to combine data in a worksheet. First, you can join the values of cells. You could say that we simply “add” the value of one cell to another. The result is a third cell containing the values of the other two cells. In other words, you simply combine the contents of two or more cells.

It is also possible to merge cells. When you merge cells, you “physically” join two or more cells. The result in this case is a larger cell made up of the two cells we merged. This latter example will not be discussed further in this article, as it is not actually possible to combine values in this way. Instead, the focus is on the CONCAT() function and the & operator.

The CONCAT() function is used to join text strings or combine values from multiple cells into one cell. The syntax is very simple:

Here, text can be a text string, a cell reference, or the result of a formula. It is worth mentioning that CONCAT() has a predecessor – the CONCATENATE() function. It has exactly the same syntax and is still available for backward compatibility. However, it is uncertain whether it will continue to exist in later versions of Excel or future updates of Excel 365.

Let’s start with a simple example.

As you can see, the function joins the text strings into a new one. Note that you can also add a space by writing:

It is also possible to use delimiters. To place a comma between two text strings, write:

Again, both give the same result. In this case, a space is also added after the comma.

Often, delimiters are enough, but sometimes you need a line break to separate text strings. A common example is combining address data from separate columns. The issue is that a line break does not correspond to a regular character. Instead, the CHAR() function is used to provide the corresponding ASCII code. For Windows, you use CHAR(10). See the example below where space, comma, and line break are used.

For the line break to work, the first time you enter the formula you must select Wrap Text under the Home tab. If you use the same formula again in a similar way, it won’t be necessary. The CHAR() function can also be used to generate various special characters. The simplest approach, however, is often just to use quotation marks, as with spaces.

When combining a text string with a number or date, you may want to format the result differently depending on your dataset. To do this, you can use the TEXT() function when concatenating. The TEXT() function has two arguments:

The first argument (value) is a number or date to be converted to text, or a cell reference containing a numeric value. In the second argument, you specify the format using the codes that the TEXT() function can interpret. As in the example below:

There is also a newer function called TEXTJOIN(), which combines text from multiple ranges and/or text strings, and also includes a delimiter. If you set the delimiter (separator) to an empty text string, all text will be joined that way.

If you want to do the opposite of concatenation, there are primarily two options:

• Use Text to Columns under the Data tab, or

• Use the functions LEFT(), RIGHT(), or MID().

In the course Excel Functions Guide, there is a lesson on joining text with the ampersand operator. That lesson is just one of eleven lessons in the chapter on text functions.

/ Niklas at Learnesy

“In this lesson, we will look at how to join multiple rows of text using the ampersand, as well as how to find the length of a text string using the LEN() function.

To join two rows of text located in different cells, we use the ampersand between the references to those cells. Between the references, we can also add extra text, such as a space.

To count the number of characters in a text string, we use the LEN() function. It has only one argument: the cell containing the text we want to count. We type equals, LEN, and select the cell. We then see that the cell contains a total of 11 characters.”