Combine data from multiple cells into one (Excel 2000/2001/2002/2003/2004/2007)

Microsoft Excel makes it easy to combine information stored in separate cells into a single cell. There are two ways to concatenate information--using the CONCATENATE function or using ampersand (&) concatenation operator. To demonstrate, let's say that a worksheet stores name information in two columns. First names are stored in column A and last names are stored in column B. A project you're working on requires that the data is combine into a "Last Name, First Name" format.

To examine both concatenation techniques, enter a first name in column A and a last name in column B, as we described. Then, in cell C1, enter the following formula:

=CONCATENATE(B1,", ",A1)

Notice that we combined cell references with a literal text string, the comma and space that separate the last and first names. Although we didn't include any, you can also concatenate numeric data. The CONCATENATE function can accept up to 30 arguments.

To produce the same result using the concatenation operator, enter the following formula in cell D1:

=B1&", "&A1

Note that this approach can also concatenate numeric data, but it isn't limited to 30 items. You can use these data-combining techniques in Excel 2007 the same way you use them in earlier versions.