Excel 2007 – Merge or join column contents using ampersand

By Techblissonline Staff Updated on 17th September 2014 Filed Under: Excel Tips, Office 2007

Loading...

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 (&).

7 Responses to “Excel 2007 – Merge or join column contents using ampersand”

  1. [...] can also join or merge the excel column contents. Did you like the tutorial on how to split contents of cells in excell 2007 worksheet into multiple [...]

  2. thanks

    it helps me a lot

  3. I am looking to CONCATENATE First and Last names but, I would like to join 2 cells on SheetA to a Cell on SheetB.

    This is what I have but, it does not seem to notice it as a formula.

    =(STUDENTS!D8&”, “&STUDENTS!B8)

    I have a students Sheet called “STUDENTS” and another sheet called “SEPT”. I will also have a sheet for each month. I want to enter in the students First name in Cell B and the last name in Cell D on the Students Page, then I would like to bring them together on the monthly pages so they are only entered once.

    Thanks in advance for your help!

    Mark

  4. =(STUDENTS!B1&”, “&SEPT!D1)

    Try using the above formula in monthly page.You seem to be telling that they are two different pages but you always use the STUDENTS page in the formula.Your formula does not refer SEPT page at all…

  5. Thank you so much!!!! Been looking for this :p

  6. Sometimes we start looking into complex formulas while we have a really simple solution at hand…. great article, very usefull !

  7. My excel sheet columns and rows are all numbers, instead of alphabets and numbers e.g. Cell A1 appears as R2C1, please help.

Leave a Reply

Go to Top