How to Combine Data in Excel 2013 Using the CONCATENATE Function

How to Combine Data in Excel 2013 Using the CONCATENATE Function
Page content

All too often, you have text scattered across several cells that need to be combined. Perhaps that arrangement was by design or resulted from errors when importing text. In any case, you want to combine the text, such that it appears in a single, well-formatted cell. Retyping the data would be tediously impossible on huge datasets. Thankfully, Excel 2013 provides concatenation abilities that enable you to combine current text in a worksheet and even add in more.

As an example, say your data separates employees’ first and last names and the department in which they work. This arrangement works well for the data, because you can conveniently sort by any of the columns. However, you might also need a field that combines each value, such that a single cell contains first and last names, separated by a space, and introduces their respective departments.

Merging Cell Values

Use the CONCATENATE function to merge the contents of several cells quickly using the following format:

=CONCATENATE(Reference1,Reference2,Reference3)

However, this simple format doesn’t add spaces or symbols between values, so the results run together, as in this example:

Adding Your Own Text

To make the output more legible, add your own text to the formula in place of cell references. Just make sure to put quotation marks around text, so Excel doesn’t confuse quotes and references:

=CONCATENATE(Reference1,”Quote1”,Reference2,”Quote2”,Reference3)

In this example, a space is added between the first and last names and a delineator ( - ) before the department:

Using the Alternative “&”

Excel also recognizes the ampersand (&) between references and quotes to combine values just as the CONCATENATE function does, so you can use either. You might find that the latter function is neater when combining numerous cells and text values, but the ampersand alternative is quicker for just a few values. The format is as follows:

=Reference1&”Quote1”&Reference2&”Quote2”&Reference3

The original example could be reconstructed using ampersands as follows:

Copying the Formula

Once you have the formula constructed, you can quickly copy it by dragging the bottom right Fill Handle to the last row in the data set:

When you do so, the formulas are automatically incremented, so the data in each row is concatenated: