Multiple Ways to Merge Columns in Excel

In this blog, we would learn how to merge columns in Excel. We would go through multiple ways including the formula to merge columns in Excel. Before starting with this blog, let us get one sample data ready.

Sample Data

Suppose you have an excel worksheet in which the forename is in one column, middle name in the next one, and the surname or the last name in the third column. The task now is to merge these into one column with space in between. 

Refer to the image below:

Merge and Center – Whether It Works?

The ‘Merge and Center’ feature is an inbuilt excel tool provided by Microsoft in the ribbon options to merge and combine the cells (by row or by column) in Excel.

This feature is available under the ‘Home’ tab in the ribbon bar. ‘Home’ tab > ‘Alignment’ group > ‘Merge and Center’ button as shown in the image below:

However, regrettably, this feature would not help you to combine the data in cells. It would only merge the cells (but not the data inside the cell).

Let us look into it by following the undermentioned steps:

Firstly, you need to individually select the cells that you want to combine and then, click on the ‘Merge and Center’ button.

As soon as you click on this button, the excel would pop out a message box as below:

Ignore the message for a while and click on OK to exit. Now, see what has happened with your data in the cells A2 and B2.

Did you notice that although it combined and merged the cells into one, however, it did not keep or merge our data? Data is lost. It kept only the first name and removed the last name.

This is what the message box that we ignored had warned us about. 

So, what is the solution for it? How to achieve our results?

Multiple Ways to Merge Columns in Excel

As we move along this blog, we would go through some formulas which can be used to combine the data in columns in Excel. We would also go through another way without using any formula.

1. Formula to Combine Cells in Excel
2. Merging Columns Using Notepad

Let us now learn each of these methods in detail.

Using Formula to Combine Cells in Excel

In this section of the blog, we would go through multiple formulas to combine the data in cells or columns in excel.

The first one is by using the & (ampersand). The ampersand symbol is used to merge the data in different cells in Excel. Use the steps below:

Select the cell where you want to get the full name (cell C2) and enter the following formula:

=A2&” “&B2

The result of the formula is-

Explanation – As mentioned earlier, the ampersand symbol & is used to join the texts in cells in Excel. The reference of A2 and B2 in the formula represents the first and last name respectively and “ “ represents the space character. Ampersand symbol joins them. In a nutshell, by =A2&” “&B2 we are asking excel to take the first name, then insert a space character and finally, take the last name and combine them all.

Finally, copy the cell C2 and paste it to the other cells in column C.

Another formula to join text in excel is the CONCATENATE formula. Enter the following formula in cell C2:

=CONCATENATE(A2,” “,B2)

By using CONCATENATE formula in Excel, we are telling – Hey! Mr. Excel, concatenate (meaning combine) the text in cell A2, a blank (represented by “ “), and the text in cell B2 into cell C2.

If you are using Microsoft Office 365, then TEXTJOIN formula would be a life saver, if you have combine many cells.

=TEXTJOIN(“ “,TRUE,A2:B2)

Kindly note that the above formula is available in Office 365 version of excel and not in the previous versions.

Once, you use any formula, make sure to paste it as values using Paste Special feature.

Merge and Combine Cells Data Using Notepad

Once I asked my friend whether does excel have any way to combine the cells without using any formula, his answer was NO. Excel does not have an inbuilt feature to merge the data in different cells.

However, he insisted me to copy the data into notepad, do some magic tricks over there and again paste it back to Excel. I asked him the procedure and he gave me the following steps:

Select your data and use Ctrl + C keyboard shortcut to copy it.

Then, open a Notepad application and paste it over there using Ctrl + V keyboard shortcut as shown in the image below:

Now, in the notepad, simply select the area represented by a tab character, and copy it using Ctrl + C. Below screen demonstrates it.

Be in the notepad itself and press Ctrl + H to open the notepad’s ‘Replace’ dialog box.

In the ‘Find What’ input area, press Ctrl + V to paste the copied TAB character. In the ‘Replace With’ input area, press SPACE character as a delimiter and click ‘Replace All’ button.

As a result, you would notice that excel replaces the tab character with the space character as shown in the image below:

Finally, select all the names from the notepad and use Ctrl + C to copy them. Go to Excel and use Ctrl + V to paste it to the desired location (cell C2)

The benefit of using this technique is that you can merge the data in thousands of cells in columns quickly. The formula method, in this case, would not be viable as if you use the formula you need to give reference to all the thousand cells one by one.