How to merge three columns in Excel
In Excel, If you have related information spread across multiple columns, merging them into a single column can help consolidate and organize the data for easier analysis or presentation. This can be helpful when working with lists or databases that require concatenation or combination.
In this tutorial, we’ll discuss the two most convenient methods to merge data from three columns into a single column.
METHOD 1 – Using the Ampersand function
In this method, we’ll use the Ampersand function to merge data from different columns into a single column.
Consider the following data set containing the first name, middle name, and last name of seven people in three different columns:
STEP 1 – Create a new column for combining data
- Move your cursor to the top cell of the column beside the column of “Last Name”.
- Select the column and write down the heading Full Name.
- A new column will be created for combining data.
STEP 2 – Apply the Ampersand Function
- Move your cursor to the cell of the created column in which the name of the first person is to be combined.
- The required formula in this case is:
=A2&B2&C2
- Write down the above-mentioned formula in the cell.
- The first name, middle name, and last name of the first person will be combined into a single cell.
STEP 3 – Use the AutoFill Handle to merge all the data into a single column
- Select the cell that contains the combined data.
- Move your pointer to the bottom right corner of the cell.
- Drag the AutoFill Handle to the last cell where you want to merge data.
- All the data in the three columns will be merged into a single column.
METHOD 2 – Using the Concatenate function
In this method, we will use the Concatenate function to merge the data in three columns.
We will let the same data set as in the above case:
STEP 1 – Create a new column for combining data
- Move your cursor to the top cell of the column beside the column of “Last Name”.
- Select the column and write down the heading Full Name.
- A new column will be created for combining data.
STEP 2 – Apply the Concatenate function
- Move your cursor to the cell of the created column in which the name of the first person is to be combined.
- The required formula to combine the name in this case is:
=CONCATENATE(A2,B2,C2)
- Write the given formula in the selected cell.
- The first name, middle name, and last name of the first person will be combined into a single cell.
STEP 3 – Use the AutoFill Handle to merge all the data into a single column
- Select the cell that contains the consolidated data.
- Move your pointer to the bottom right corner of the cell.
- Drag the AutoFill Handle to the last cell where you want to merge data.
- All the data in the three columns will be merged into a single column.