Combining cells in Excel

Combining cells in Excel is something we frequently need to do. And there are multiple ways to do it, depending on what exactly you need to do. One is easier to remember than the others, but let’s step through them.

I find the easiest-to-remember way to combine cells is to use the & operator with the cells you want to combine, along with any additional text. For example, the formula =A1&” “&B1 will combine the cells A1 and B1 with a space in between them.

Combining multiple cells in Excel

My favorite way to combine cells in Excel is to use the ampersand operator, because it’s easy to type and easy to remember. Here’s how it works.

Consider the following scenario. You have an address list in some Excel-readable format. Column 1 contains a first name, column 2 contains a last name, column 3 contains a street address, column 4 contains a city, column 5 contains the state, and column 6 contains the ZIP code.

It looks something like this:

To combine their first and last names, enter this formula in the cell to the right of the ZIP code:

=A2&” “&B2

Then fill down. Now you have first and last names together in the last cell.

You can do more than two cells too. How about combining the name and address on a single line? You can use this formula to do that:

=A2&” “&B2&”, “&C2&”, “&D2&” “&E2&” “&F2

That gives you a cell that looks like this:

Ed Smith, 123 Anywhere, Springfield IL 55555

Pretty slick, right? But admittedly, it has some limitations, so let’s look at a couple more use cases.

Merging cells to make comma-separated lists in Excel

Sometimes I need to take a column out of a data export (say, Qualys or Tenable data) and reformat it a bit so I can use it in a one-off Python script. There are lots of time-consuming ways to do this, but you can do it quickly in Excel, using the & operator. Let’s say I need to turn a column of asset IDs into a comma-separated list I can use in Python. That means I need to enclose my ID in quotes and add a comma at the end. Well, I could use char(34) for quotes but here’s another possible solution that doesn’t involve having to remember character codes:

I paste my column of data into column B. Yes, column B. Then in column A, I enter my opening quotation marks. And in column C, I enter my closing quotation mark followed by a comma. My formula =A2&B2&C2 mashes them together. Fill down, delete the comma from the end of the last cell in column C, and I’m good to go. I have a nice, Python-friendly comma separated list in column D that I can copy out of Excel and paste into my script.

If your use case is a little different, place whatever you need in column A and column C to wrap your data.

You can do this fairly quickly in a good text editor, to be certain. But I can do it faster in Excel and then I don’t have to argue with IT staff about what text editors I’m allowed to use. That’s a win-win.

Merging cells in your rows with cells elsewhere in the sheet

By default, when you copy a cell formula, Excel increments the cell numbers to match the current row. That’s good when all the cells you want to merge are in the same row.

But sometimes you might want to merge cells in multiple rows with one cell elsewhere in the sheet. In my comma-quotes example above, for example, I could put a single quote out of the way somewhere, say, in cell A10, and a quote and a comma in cell A11. To lock those cells into the formula,insert a $ between the letter and the number, like this:

=A$10&B1&A$11

Then fill down.

Concatenate cells in Excel

Excel also has a concatenate function that performs much the same thing. I prefer to use the & operator because it’s faster than typing concatenate. But if you find it easier to remember, there’s no reason not to use it. The formula in my first example to put the name, and mailing address on a single line changes to this:

=concatenate(A2,” “,B2,”, “,C2,”, “,D2,” “,E2,” “,F2)

And in my comma-separated list example, you can use this formula:

=concatenate(A2,B2,C2)

Locked cells work with concatenate too:

=concatenate(A$10,B2,A$11)

Concat vs Concatenate in Excel

Starting in Excel 2016, you can use a new function called concat. Concat just merges a range of cells together. It’s most useful for my second example, where I put all my formatting characters in adjacent cells. Stay with me for a minute to check out the formula:

=concat(A2:C2)

That’s pretty nice.

And it’s not bad for the mailing list example either. Depending on how you want to format your text, you may find it easier to break up the columns, add your additional formatting, and then use the concat function to mash it all together.

If all your data is in adjacent cells and in the order you want, and you don’t need locked cells, the concat function gives a nice, short and simple way to merge it all.