In Microsoft Excel 2007, you can concatenate or merge the contents (text or number values) of two cells (or columns of cells), by creating a formula using ampersand (&).
In Microsoft Excel, merging two cells (or columns of cells) will throw a message “The selection contains multiple data values.Merging into one cell will keep the upper-left most data only.” This implies that merge in excel, does not actually concatenate or merge the contents of the cells or columns of cells and you will in fact loose data.So how to concatenate or merge the contents of the cells or columns of cells in Microsoft Excel, whether it be Excel 2007 or Excel 2003 or any earlier version or MS Excel?
How to merge or join or concatenate the contents of columns in Microsoft Excel?
Assume that you have two or more columns of cells, whose content you want to merge or join or concatenate.The columns could hold First and Last names of students in a class, or it could be any data that you want to concatenate or merge.Copying and pasting the names together in a column, is definitely possible.But it is a time consuming task, if you have huge columns of data to join or concatenate.
- Go to a cell, where you want the merged or concatenated contents of cells to appear, in the Excel worksheet.
- Type a formula that concatenates or merges the contents of cells and press ENTER.For example, the formula =A1&” “&B1&” “&C1 merges the contents (values) of cells A1, B1 and C1 with space as a delimiter (separator).
- One can also specify comma as a delimiter (separator), while concatenating or merging the contents (values) of cells. For example, the formula A1&”,”&B1&”,”&C1 merges the contents of cells A1, B1 and C1, with comma as a delimiter (separator).
- You can also concatenate or merge the contents of cells in an Excel worksheet, in any order. For example, the formula B1&”,”&A1&”,”&C1, merges the contents of cells in the order specified.
- You can extend this formula to others cells of columns, whose content or values you want to merge or concatenate, by dragging the right bottom corner of the cell, where you have applied the concatenation or merge or join formula.
We earlier saw how to split column contents in excel worksheets? Now if you have text in two or more columns of your excel worksheet, and you wish to join (concatenate) the contents in another column, do it easily as explained above, by creating a formula that uses the ampersand (&).