Merge Columns in Excel Without Losing Data

In this article, we share how to merge columns in Excel without losing data. Feel free to check it out.

I. Merging Columns in Excel Without Losing Data

1. Utilize Merge Cells for Excel to merge columns

Using Merge Cells for Excel utility to merge columns is the quickest and simplest way to merge columns in Excel without losing data. Instead of the Merge & Center feature, which merges cells without retaining full data, the Merge Cells for Excel utility helps you merge multiple columns into one column, merging rows or columns without losing data.

To use the Merge Cells for Excel utility, download the add-on here: https://www.ablebits.com/excel-merge-cells/index.php

After downloading and installing the Add-On into Excel, you can merge columns as follows:

Step 1: Select the columns you want to merge.

Step 2: Choose the Ablebits Data tab, select Merge Cells, where you have 3 merging options to choose from:

• Merge Columns into One: Merge columns into one
• Merge Rows into One: Merge rows into one
• Merge Cells into One: Merge cells into one

You select Merge Columns into One to merge columns into one.

Step 3: The Merge Cells dialog box appears, where you can set up the options as desired.

- Select the range: The data range to be merged

- How to merge: Merging method

- Combine with: Separator when combining data from two columns

- Place the results to: Where to display the merged data

- Clear cell contents: Remove the data from the selected cells after merging

- Skip empty cells: Ignore empty cells

- Wrap text: Automatically wrap text when cells are not wide enough

- Back up this worksheet: Create a backup of this spreadsheet

After setting up, click on Merge to combine the columns.

Immediately, the selected columns will be merged without losing any data.

2. Merge cells in Excel using Justify

Using Justify is a method to merge columns in Excel without losing data, where data within the rows of a column is quickly merged, and the merged data is placed in the top cell of the column.

Note: To merge data columns using the Justify feature, ensure the following:

- The data to be merged is all in the same column

- The data in the cells to be merged must be in text format (numeric or formula formats will not merge)

- The selection area for merging should not contain empty cells

To merge data columns, follow these steps:

Step 1: Select the data cells in the column to be merged, adjust the column width wide enough to display the merged data fully.

Note: Ensure appropriate column width adjustment, otherwise merging columns in Excel with Justify won't be possible.

Step 2: Choose Home, select Fill → Justify under Editing

All data will be merged into the first cell of the column as shown below.

II. How to merge columns in Excel while preserving original column data

1. Merge 2 columns in Excel using CONCATENATE function

Using the CONCATENATE function is a quick and data-preserving way to merge columns in Excel. To merge columns in Excel with Concatenate function, follow these steps:

Step 1: In the cell where you want to display the merged column data, enter the formula =CONCATENATE(A2,' ',B2)

Where: A2, B2 are the two cells you want to merge; “ “ is adding a space between the two cells to be merged. You can also use any other delimiter of your choice and enclose it in double quotes. Then press Enter to execute the formula.

In this way, you can quickly merge columns in Excel without losing data.

Step 2: Drag the mouse to copy the formula down to the rows below to merge columns, you will get the following result.

2. Merge 2 columns in Excel using the & operator

Instead of using the CONCATENATE function, you can directly use the & operator to merge columns in Excel without losing data. Here's how:

In the first cell to merge, enter: = A2&' '&B2

Where:

• & is the operator used to concatenate data
• A2, B2 are references to the cells to be merged
• “ “ is the space used to concatenate between the referenced cells

Press Enter to execute the column merge.

Copying the & operator formula down the cells below will merge the data in two columns into one column without data loss.

Thus, TechTips.com has just shared with you simple and quick ways to merge columns in Excel without data loss. You can refer to and choose the appropriate method for your column merging purposes in Excel. Hope this article will be helpful to you in your data handling process in Excel. Thank you for your interest and for following this article.