How to Combine Cells in Microsoft Excel

In a previous post we showed you how to separate first and last names into two columns using Excel’s text to columns function. But, what if you want to combine cells?  Say you have first and last names in two columns and you really want them together in one? Here we’ll show you how to join (or concatenate) multiple cells into one.

Microsoft Excel: Concatenate Text

According to Wikipedia:

“In formal language theory and computer programming, string concatenation is the operation of joining character strings end-to-end”

So, if we have an excel spreadsheet with one cell containing the word “John” and another containing the word “Smith” we could concatenate the two and create a cell containing the text “John Smith”.

Combine Text Example 1

Let’s say we have this spreadsheet:

and we want to create a column of names with the first and last names together. Since column D is the first open column we’ll place our new list of first and last names there. So, to start with the first row, John Smith, select cell D2. With D2 selected type:

= A2 & B2

When you press enter you’ll see the following result in cell D2:
JohnSmith

But, there’s no space between the names. That’s because excel doesn’t understand that you want a space there unless you tell it that you do. Computers will do exactly as they’re told, with no interpretation. So, let’s create a new formula with a space between the first and last names. With D2 selected, type the new formula:

= A2 & “ “ & B2

Now when you press enter you’ll see the following result in D2:
John Smith

Since this is the result we’re looking for, we now want to copy this formula into rows 3 through 8 (or cells D3 through D8). We can do this in a few ways.

The first way is to use the copy and paste function. Highlight cell D2 and select copy from the home menu:

Then, holding the mouse key down, select cells D3 through D8 and select paste from the home menu:

The other way is to use a built in function of excel’s to copy a cell across a row or down a column. To do this, select cell D2. Then, place your mouse cursor over the bottom right corner of the D2 cell and your mouse cursor should turn into a plus sign. When you see the plus sign, press your mouse button down and hold it while dragging the cursor down the column. You’ll see cells highlight as you drag down. When you see D3 through D8 highlighted, let go. Excel will automatically copy the column to the other cells you’ve highlighted.

Your result will look like:

Combine Text Example 2

Let’s say we start with the same spreadsheet but, instead of creating a column that says John Smith, we want to create a column that says: “Smith, John”.

= B2 & “, “ & A2

The result will look like:

After copying to the rest of the cells like we did earlier the spreadsheet will look like:

Excel Concatenation Rules

If you have other types of cells you want to combine together you can follow the same process. Here are some general rules to follow:

1. Always start the formula with an equal sign (=) to tell excel that you’re typing a formula.
2. After the equal sign, enter the first bit of data you’d like to appear in your resulting cell. You can add the following:
   1. the contents of a cell – to do this, simply enter the column letter and row number of the cell information that you’d like to appear (ie: A2)
   2. text – to add text use double quotation marks around the text you’d like displayed (ie: “sample text“)
   3. formula result – write the formula (without using another equal sign)
3. Add an ampersand (&) to tell excel you want to add more to the cell after what was added in step 2.
4. Add more content by repeating step 2.
5. Repeat steps 3 and 4 as many times as needed.

Concatenation can be used in many ways.  If you have problems getting it to work or need help, leave a comment below.  We’d love to help out.