I was working on Microsoft Excel 2010 and I needed to combine or merge the content in two cells into one. While searching the Internet, I found many methods of doing this so I thought of listing down all the methods here so that it may be useful for everyone to know.
Using the concatenate function
The concatenate function can be used to combine or merge two or more cells into one. You can use the values of multiple cells to be combined in the resulting cell. Here’s the syntax of concatenate function. This function is available in all versions of Excel including Excel 2010 and 2007.
=CONCATENATE(cell1, cell2, cell3 ..)
Somehow if you want to select a range and try to use the colon (:) operator, concatenate function will not work correctly in case of text.
The above function will not work correctly so you will need to use the syntax that I have specified first.
Using the & operator
& operator can be used as a concatenation operator in Microsoft Excel. The function of & is to combine the values of two cells and display the final value in the resulting cell. Let’s take an example. I have written "technize", "." and "net" in three different cells and I want to combine them in one cell, I will use the following code in the resulting cell:
Cell1 = C1 : technize
Cell2 = C2 : .
Cell3 = C3 : net
resulting cell = C4 : =C1&C2&C3
Using VBA programming
If you want to merge cells using VBA then you can use the following VBA code to do so:
Sub ConcatenateColumns() 'Click the top cell in the right-hand column of data that you want to concatenate. _ For example, if cells A1:A100 and B1:B100 contain data, click cell B1. Do While ActiveCell <> "" 'Loops until the active cell is blank. 'The "&" must have a space on both sides or it will be 'treated as a variable type of long integer. ActiveCell.Offset(0, 1).FormulaR1C1 = _ ActiveCell.Offset(0, -1) & " " & ActiveCell.Offset(0, 0) ActiveCell.Offset(1, 0).Select Loop End Sub
Do you know of any other method to combine or merge two cells into one in Excel? Please share with us in the comments below.