How to combine two columns in Excel while preserving data

Emre Çitak
May 20, 2023
Updated • May 18, 2023
Tutorials
|
2

When it comes to managing data in Microsoft Excel, certain tasks are intuitive, such as inserting currency. However, there are other operations that may be less straightforward, like combining columns. In this article, we will explore various methods to merge two columns in Excel while ensuring that all data remains intact.

Whether you have imported data from an external source or received input from collaborators, we've got you covered with step-by-step instructions on how to combine columns in Excel.

How to combine columns in Excel

There are two primary methods to merge columns in Excel while preserving data. The first approach involves utilizing the ampersand operator, which allows you to combine cells by simply using the "&" symbol within a formula. This method provides flexibility to add spaces, hyphens, or any desired characters between the merged data.

Alternatively, you can leverage the CONCAT function, which offers a more streamlined way to merge cells and columns without the need for extra typing. With the CONCAT function, you can easily merge cells and include spaces or other characters within the formula.

These two approaches provide different options for merging columns in Excel, allowing you to choose the method that best suits your specific requirements.

How to combine two columns in excel
There are two methods to combine two columns in Excel - Image: Microsoft

Using the ampersand operator

The ampersand symbol (&) is not only a shortcut for the word "and," but it also serves as a powerful tool for merging data in Excel.

Merge cells in columns

Start by navigating to the first row of your new column, where the merged data will reside. Enter the formula "=A2&B2" if you want to merge cells A2 and B2. Press Enter to combine the data into a single string.

If you want to include a space or a hyphen between the merged data, you can modify the formula accordingly. For example, "=A2&" "&B2" would insert a space, and "=A2&"-"&B2" would add a hyphen.

Copy the formula

Once you have merged the cells in the first row, you can easily copy the formula to the remaining rows in the merged column. Simply select the cell containing the formula and double-click the Fill Handle (represented by a plus sign) located at the bottom right corner of the cell.

The formula will automatically copy down the column, extending it to match the number of rows in your data.

Using the CONCAT function

An alternative approach to merging columns in Excel is to utilize the CONCAT function. This function simplifies the process and eliminates the need for manual typing.

Merge cells in columns

Start by selecting the first row of your new column, where you want to combine the data. Enter the formula "=CONCAT(A2,B2)" to merge cells A2 and B2. Press Enter to create a single string without any spaces.

If you wish to insert a space or a hyphen between the merged data, modify the formula accordingly by adding quotation marks and the desired character. For example, "=CONCAT(A2," ",B2)" would insert a space, and "=CONCAT(A2,"-",B2)" would include a hyphen.

Copy the formula

Once you have entered the formula in the first row, you can easily copy it to the remaining rows in the merged column. Select the cell containing the formula and double-click the Fill Handle located at the bottom right corner of the cell.

The formula will automatically extend down the column, adapting to the number of rows in your data.

Merging columns in Excel can be accomplished through the use of either the ampersand operator or the CONCAT function. By following the step-by-step instructions provided in this article, you can combine two columns while preserving all data.

Whether you choose the ampersand operator or the CONCAT function, these methods will allow you to efficiently manage your data in Excel.

Advertisement

Previous Post: «
Next Post: «

Comments

  1. Tony said on May 21, 2023 at 2:21 am
    Reply

    I knew about CONCAT, but not &. I guess I’ve learned something today!

  2. okiaulx kaji said on May 20, 2023 at 3:17 pm
    Reply

    this page is dead. just accept it and close it. should have never sold it…

Leave a Reply

Check the box to consent to your data being stored in line with the guidelines set out in our privacy policy

We love comments and welcome thoughtful and civilized discussion. Rudeness and personal attacks will not be tolerated. Please stay on-topic.
Please note that your comment may not appear immediately after you post it.