How to combine cells in Excel without losing data

Combining the values of two cells in Microsoft Excel can have several benefits depending on the specific use case. This can be beneficial when we want to consolidate data from different sources, where it is needed to improve the readability, when we want to create customized labels, titles, or sentences as well as exporting or sharing the data.

There are two methods to accomplish this task. First is the use of a concatenate formula, while other is ampersand (&) operator. The detailed demonstration is as under.

Method 1 – Combine the cells by concatenation 

The Concatenate function in Excel is used to combine or join multiple text strings into a single string. It allows us to concatenate or merge the contents of multiple cells or text values together. The CONCATENATE function can be particularly useful when we want to combine different pieces of information into one cell. 

The formula used is denoted as =CONCATENATE(text1, [text2], …)

[Text1] refers to the first column we want to join.

[text2] refers to the first column we want to join. The data of more than two columns can also be joined by this method. For better visualization , we can add space ,comma or any separator by using “  ” , “ – ” , “ , ”  . If required.

The stepwise demonstration is as under.

Step 1 – Select the desired column

• Select the desired column where we want to combine two or more values. If the desired column is not available in the dataset, then Right click on the desired cell.
•  A dialog box will open. 
• Click on Insert. A second dialog box will be opened. Click on the Entire column. 
•  A new column will be inserted in which we can combine the cells without losing the data. The demonstration is mentioned here.

Step 2 – Use concatenate to combine cells

• Select the desired cell, in this case we selected the cell E2.
• The First data is present in the  C2 and the second column to be joined is present in D2, then the formula applied will be =CONCATENATE(C2, D2).
• As soon as you press the enter key, it joins two cells without losing data. The demonstration is shown above.

Method 2 – Combine the cells by using Ampersands Operator

By utilizing the ampersand operator in Microsoft Excel, we can manipulate and combine text strings and cell values to create more versatile and informative data in our spreadsheets. This can be another way to combine the cells without losing the data. The syntax is: =Text1 & Text2.  Text1 : The first text or value we want to combine

Text2 : The second text or value to be combined with the first one.

The stepwise demonstration is shown above.

Step 1 – Select the desired column or cell and apply ampersands operator

• Select the desired column or cell where we want to combine two or more values.
• In this case, we have first data in the C2 column and second data in D2, then the ampersands operator is applied as =C2&D2.  This results in combining the cells without losing the data
• Then Double click on the right corner of the handle to fill the column if data consists of more than one cell. This is shown as under.

Both the CONCATENATE function and the ampersand operator achieve the same result of joining text strings. By using these ways we can combine cells without losing data in Excel.