How to merge columns without losing data in Excel

Many users reported that when they tried to merge and combine columns in Microsoft Excel, they lost data, except for that in the leftmost column. To counter this problem, you should use a formula. We will show you the way in this post.

Combine columns without losing data in Excel

You can merge and combine columns without losing data in Excel by using either of the following two methods:

1. Use an operator
2. Use of the CONCATENATE formula.

Let's see these two methods in detail.

1) Using the operator

You can merge and combine columns without losing data in Excel with the help of an operator. The syntax for the same would be:

=&&&...

When using this formula, make sure that the first cells with the text of each column are on the same line. Similarly, if a cell is empty in one of the columns' cells, it will be ignored during the merge.

For example. Suppose you need to merge 3 columns of text. The columns are columns A, B and C. In the three columns, the first line with the text is line 3 (it is important that the first texts of each column are in the same line). Also, suppose that the texts in the columns are up to row 7.

The merge formula will then be:

=A3&B3&C3

Enter this formula at line 3 of the column where you need the merged text. For example, if you need the merged text in column E, place the formula in cell E3. Press Enter to get the merged text of cells A3, B3, and C3 in cell E3.

Click anywhere outside cell E3, and then return to cell E3 to highlight the Fill option. Then drag the formula through the column to cell E7. This will help you display the merged text of all columns in column E.

However, the problem is that the merged text would have no space between the original texts. If you want to add spaces, modify the formula and do the following:

=A3&" "&B3&" "&C3

Enter this formula in cell E3 and use the Fill option to expand it on the column.

2) Using the CONCATENATE formula

To use the CONCATENATE formula for this problem, the syntax would be:

=CONCATENATE(:)

For example. In the above example, the formula would become = CONCATENER (A3: C3)

In this case, A3: C3 would represent the column range. The CONCATENATE formula is particularly useful when you have to use a large number of columns and it is not possible to mention them individually.

I hope it helped.