combine cells in Excel

There are a couple of ways to combine cells in Excel. Technically, this is called concatenation from the verb concatenate-- to join or link things together in a chain or series.

Excel offers two main ways to do this:

1. using the "&" operator or
2. the concatenate function

Before you begin, make sure that the column you are working with accepts formulas. I ran into this working on a recent worksheet for someone else and spent at least 10 minutes wondering why the column wasn't accepting formulas. Check the format of the column by clicking on the column to highlight it and then right-click and choose "Format cells." Make sure that "General" is chosen. 

Or if you're using Office 2010, just check the the format in the Excel Ribbon:

Combine cells using the "&" operator:

Much of this comes with the help of this post.

Combine cells containing text:

For example, if you want to combine two columns to make one name, you could use the formula =A2&B2:

This is the result: 

But the final name doesn't look that great because there's no space between the first and last name. You can add a space just by changing the formula to =A2&" "&B2. The result would be this:

Notice that Katie Smiles has a nicely formatted name, with a space between the first name and the last name.

You can even add additional text.

Combine cells with numbers and text:

There aren't really any special notes for this, just do it the same way as above.

Combine cells containing dates and text:

This actually requires special formatting. If you don't format the formula, the column containing dates will be treated as numbers. For example:

Notice that just using the formula "&", the date in column A gets treated as a number. But formatting the formula, I get the following:

Instead of just &A2 the formula becomes &TEXT(A2,"mm/dd/yyy").

In this case TEXT is the function, A2 is the argument and refers to the cell, and "mm/dd/yyyy" tells Excel what format you want A2 to be in. In this case, you want the date interpreted as numeric month, numeric day, and full year. 

Here are some common Excel formats:

Function	Formula
TODAY function returns the current date: (dddd indicates you want the full weekday name)	=TEXT(TODAY(),"dddd")
Format a date as 12/31/04:	=TEXT(A2,"mm/dd/yyyy")
Format a number as currency:	=TEXT(A3,"$#,##0.00")&" USD"
Format a number as a percentage:	=TEXT(A4,"0.00%")
Format a number as a fraction:	=TEXT(A5,"# ?/?)

You may also want to view this post to see date formats in Excel.

Combine cells using concatenate:

I actually prefer to use the concatenate function because I find it easier than constantly inserting "&"s. It works in almost exactly the same way and same format-- but without the "&"s. Here's an example:

Dates must also be formatted and in a similar way as above when you use the concatenate function.