How to use the Concatenate function in Excel

  • The Concatenate function in Excel allows you to join text strings or numbers into a single cell.
  • Data can be concatenated using the ampersand (&) sign or the CONCATENATE function.
  • The CONCATENATE function offers more options and flexibility for concatenating data in Excel.
  • Concatenating is useful in large databases to combine information from different cells.

concatenate

We review another of the functions of the Microsoft Office spreadsheet that can be really practical in some cases. We refer to the Concatenate function in Excel, through which two or more text strings (or two or more numbers) can be joined within the same cell.

This function is especially useful when we work with large databases, since it allows us to combine different results and contents in the same cell. A complex function that has many practical applications.

What is concatenation and what is it used for?

The definition of concatenate that comes in the dictionary is this: "Unite or link some things with others." transferred to Excel, this means joining two or more text strings in the same cell. Concatenation in a cell in an Excel spreadsheet can be done in two different ways: using the ampersand sign (&) or by resorting to concatenate function. We discuss both methods below.

But it is possible that, at this reading point, you are still wondering what is the importance of this function and in what circumstances it can be useful to you. To address this question, the best way to convince ourselves is illustrate it with an example:

Imagine we have a list of customers with first and last names divided into three columns: Column A for the first name, Column B for the first last name, and Column C for the second last name. If we need to gather all the information in a single piece of information (for example, to carry out a massive mailing), thanks to the concatenate function we can have a new column with cells in which these three different pieces of information are gathered.

Let's see below how to execute this action using the ampersand "&" and using the Concatenate function. We will also briefly analyze another different function that we will be able to use to achieve the same or even better results.

Concatenate with the & symbol

In Excel, the ampersand sign (&) It will allow us to make lists of concatenated data in a very simple way. Take the example explained in the previous section. Our goal is to concatenate the first and last names spread across columns A, B, and C into a new cell. In this case, the syntax to apply would be the following:

= A1 & B1 & C1

In this way, in the new cell (it could be, for example, the one that goes horizontally below, D1) the concatenated data of cells A1, B1 and C1 will be displayed.

To make it even more correct and for the data (in the chosen example, the name and surname) to appear conveniently separated, you must write the name of each cell in quotes. Then it would be expressed like this:

=»A1″&»B1″&»C1″

CONCATENATE function

excel concatenate

Another way to do the previous action, much easier and more advisable in the case of dealing with longer data lines, is to use the CONCATENATE function.

When the concatenation is of two or three elements, there are no big differences between one method or another. Instead, when we have a lot of data to concatenate, it is better to use the function. Now imagine that, in addition to the name and surname, we have to concatenate more data from other cells such as date of birth, profession, identification number, age, marital status, etc., write the formula by hand, putting quotation marks and pressing the key & each time, it can be slow and cumbersome. It is then when the true usefulness of this function is revealed, whose maximum limit in the latest versions of Excel is 255 arguments.

How is it done? First we select the cell where we want the result to appear and then we go to the formula bar, where we choose the CONCATENATE function. Next, we add each of the arguments separating them with a semicolon:

=CONCATENATE(A1,B1,C1)

If, as in the previous example, we want the arguments (first name, last name, age, etc.) to be displayed separately for better readability, we must enter double quotes between each of them. The result of the syntax would be this:

=CONCATENATE(A1;» «;B1; » «;C1)

It should be noted that this function also allows concatenate numbers, although Excel will always understand the result as text, which means that it will not be possible to perform calculations with it.

UNICHAINS function

Finally, we will mention another practical function that we can use to concatenate data in Excel. It is available in the Microsoft Office 365 package, starting with the 2019 year version: the UNICHAINS function.

This new feature has practically the same utility as that of CONCATENATE, although its syntax is different. In it, it is necessary to indicate which separation symbol you want to use. In this way, before introducing the arguments we have to write “delimiter” and choose the separation symbol.

It is also possible to indicate if the function is going to include empty cells or not. In short, many more options that make this function much more complete. It is very likely that in the medium term the UNIRCADENAS function will end up replacing the CONCATENATE function within the Excel options.


Leave a Comment

Your email address will not be published. Required fields are marked with *

*

*

  1. Responsible for the data: Miguel Ángel Gatón
  2. Purpose of the data: Control SPAM, comment management.
  3. Legitimation: Your consent
  4. Communication of the data: The data will not be communicated to third parties except by legal obligation.
  5. Data storage: Database hosted by Occentus Networks (EU)
  6. Rights: At any time you can limit, recover and delete your information.