Column Merge In Excel

Last Updated :

-

Blog Author :

WallStreetMojo Team

Edited by :

Sheeba M

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

What Is Excel Column Merge?

Column Merge in Excel merges cell values in two or more cells in Excel. We must use the formula in every column cell by dragging or copy-paste the formula in each column.

Excel Column Merge can be performed using the ampersand (&) and the CONCAT function in Excel.

For example, we have a list of candidates with the first name in one column and the last name in another. The requirement here is to get the full name of all candidates in one column. We can do this using the Column Merge in Excel.

Table of contents
  • Column Merge in Excel helps users to combine or merge 2 or more cell values like first, middle, or last names, or Names with employee IDs, or names with respective locations.
  • We use the Ampersand (&) and CONCAT() ways to combine cell values and display the results. We can also use the delimiters, such as space, comma, dot, hyphen, etc.
  • The CONCAT function works with Excel 2016 or later versions. If we are working on the earlier version of Excel, we can use the CONCATENATE function.

How To Merge Multiple Columns In Excel?

We can merge the cells using two ways.

  1. Using the CONCAT Function.
  2. Merge the Cells by Using the ā€œ&ā€ Symbol.

Examples

We will consider specific examples for the above-mentioned methods.

Example #1 ā€“ Using the CONCAT Function

We have Ram and Kumar in the last name column in the first name column. Now, we need to merge the value in the full name column. So here we use =CONCAT(A2,ā€œ ā€,B2). The result is shown below.

Merge Column Example 1

We have noticed that we have used double-quotes (ā€œ ā€) between the A2 and B2. That is used to give space between the first and the last name. If use double-quotes without space as ā€œā€ in the function, then the result will be Ramkumar.

Merge Column Example 1-1

Example #2 ā€“ Merge the Cells by Using the ā€œ&ā€ Symbol

In the following image, we will merge the cells using the ā€œ&ā€ symbol with the CONCAT function.

Merge Column Example 2

We can merge the cells using the ā€œ&ā€ symbol in the formula. So, the formula will be =A2&ā€œ ā€&B2. The result is Kapil Gupta, as shown below.

Merge Column Example 2-1

Let us merge the value into two columns.

Example #3

We have a list of first and last names in two columns, A and B, as shown in the image below. Now, we require the full name in column C.

Merge Column Example 3

Follow the below steps:

  • First, select cell C2.
  • Then, insert the formula =CONCAT(A2,ā€œ ā€,B2), and press ā€œEnterā€.
  • Next, drag the formula from cell C2 to C11 using the fill handle, i.e., if we have the first name and last name till the 11th row, then paste the formula till C11.
Merge Column Example 3-1

The output is shown above.

Example #4

Suppose we have a list of 10 students whose first, middle, and last names are in columns A, B, and C, and we need the full name in column D.

Example 4

Follow the below steps:

  • First, select cell D2.
  • Then, insert the formula =CONCAT(A2,ā€œ ā€,B2,ā€œ ā€,C2), and press ā€œEnterā€.
  • Next, drag the formula from cell D2 to D11 using the fill handle, i.e., if we have the first, middle, and the last names till the 11th row, then paste the formula till D11.
Example 4-1

The output is shown above.

Example #5

We have a list of first and last names in two columns, A and B, below. Next, we need to create a user ID for each person.

Note: Space is not allowed in a user ID.

Example 5

Follow the below steps:

  • First, select cell C2.
  • Then, insert the formula =CONCAT(A2,ā€œ.ā€,B2), and press ā€œEnterā€.
  • Next, drag the formula from cell C2 to C11 using the fill handle, i.e., if we have the first and the last name till the 11th row, then paste the formula till C11.
Example 5-1

The output is shown above. Similarly, we can merge the value using some other character, i.e., (dot (.), hyphen (-), the asterisk (*), etc.

Important Things To Note

  • CONCAT function works with Excel 2016 or above version. If we are working on the earlier version of Excel, we need to use the CONCATENATE function.
  • It is not mandatory to use any space or special character while merging the cells. We can merge the cells in excel or columns without using anything.

Frequently Asked Questions (FAQs)

1. Define Column Merge in Excel.

Column Merge in Excel is a features in Excel that merges multiple cell values to get a new merged value. We must enter the formula in every column cell, drag, or copy-paste the formula. We can carry out the merging using the ampersand (&) and the CONCAT function in Excel.

2. Name the different methods to Merge Multiple Columns In Excel?

We can merge the cells using two ways.
1. Using the CONCAT Function:-
We use the formula as follows:
ā€¢ In a dataset, select an empty cell for the output.
ā€¢ Enter the formula according to the following syntax.
Concat Formula
ā€¢ Enter the arguments as cell values or cell references.
ā€¢ Close the brackets, and press the ā€œEnterā€ key.
2. Merge the Cells by Using the ā€œ&ā€ Symbol.
We use the formula as follows:
ā€¢ In a dataset, select an empty cell for the output.
ā€¢ Enter the formula according to the following syntax.
=cell_value1,&ā€œ ā€&,cell_value2,&ā€œ ā€&,cell_value3, and so onā€¦

ā€¢ Enter the arguments as cell values or cell references.
ā€¢ Close the brackets, and press the ā€œEnterā€ key.

3. Why is Column Merge not working?

A few reasons that Column Merge may not work are,
ā€¢ In the ampersand method (&), we did not give space in-between the double-quotes.
ā€¢ In the CONCAT() method, we did not give a delimiter as a space or a dot to separate the merged values.

Download Template

This article must help understand Column Merge in Excel with its formulas and examples. You can download the template here to use it instantly.