**What Is Excel Column Merge?**

Column merge in Excel is an option to combine two or more columns of data into one column. And while one can merge columns within a worksheet, merging columns across multiple worksheets and files is feasible.

Users can merge columns in Excel to organize and format data spread across multiple columns into one column, which helps sort data and remove duplicate entries.

For example, the table below contains ten students’ first and last names.

And the requirement is to display each student’s full name using their first and last names in column F. Then, we can perform **two column merge in Excel** by using the **CONCAT() **or the ‘**&**’ symbol in the target cells to obtain the required outcome.

In the above **two column merge in Excel** example, the **CONCAT()** accepts three inputs, a student’s first name, a space character, and the student’s last name.

Alternatively, we can use the ‘**&**’ symbol-based **two column merge in Excel formula** to combine each student’s first name, a space character, and the last name.

Both methods merge the two columns, D and E, data in each row with a space character in between to display each student’s full name in column F.

##### Table of contents

###### Key Takeaways

- Column merge in Excel is a method that helps concatenate the data in multiple columns across one or more sheets and files and display the combined data in one column.
- Users can merge columns in a spreadsheet to logically organize massive datasets in fewer columns.
- We can use the
**CONCAT()**and the ‘**&**’ operator to merge columns in Excel. - We can supply cell values and references as inputs to the column merge formulas. And we can manipulate column values and merge them with texts, constants, and special characters according to our data concatenation requirements.

**How To Merge Multiple Columns In Excel?**

We can combine multiple columns in Excel by using the following methods:

**Using the CONCAT Function****Merge the Cells by Using the “&” Symbol**

**Method #1 – Using The CONCAT Function**

The **CONCAT()** combines specified values from multiple ranges without delimiters.

And the steps to perform column merge in Excel by applying the **CONCAT()** are as follows:

- Choose a target cell and select the
**Formulas**tab – the**Text**drop-down – select the**CONCAT**function.

- The
**Function Arguments**window will open.

Here, we must update the mandatory **Text1** field, followed by the required number of optional fields **Text2, Text3, **and so on, as values or Excel cell references to values.

Please note that the **CONCAT()** can accept up to 255 arguments as input. And if the resulting concatenated value contains more than 32767 characters, the function output will be the **#VALUE!** error in Excel.

- Click
**OK**in the**Function Arguments**window to view the**CONCAT()**output in the target cell.

Alternatively, we can directly enter the **CONCAT()** in the target cell using the following steps:

- Choose a target cell to display the output.
- Enter
**=CONCAT(**in the target cell. [ Alternatively, enter**=C**or**=CON**and double-click the**CONCAT**function from the Excel suggestions.] - Supply the arguments as cell values or references to the values and close the brackets.
- Press
**Enter**to execute the function and view the required column merge in Excel output.

Ensure the supplied arguments are without any syntax errors in both methods. Otherwise, we may face a scenario of the** column merge in Excel not working**.

**Example #1**

The table below lists product codes, inventory codes, and products.

And the requirement is to create a product identification code in each row of column D by merging columns A: C data using the **CONCAT()**. Then, the steps are as follows:

**Step 1: **Choose the target cell D2 and select the** Formulas **tab – **Text **– **CONCAT** function.

**Step 2: **Enter the argument values, as shown below.

And once we click on the second argument field, the third argument field will appear.

So, we can update as many argument fields according to the requirement, as depicted below.

**Step 3: **Click **OK** in the **Function Arguments** window to view the **CONCAT() **output in the target cell D2.

[ Alternatively, select cell D2 and type the **CONCAT()**.

**=CONCAT(A2,”_”,B2,”_”,C2)**

And pressing **Enter** will result in the required output.]

The **CONCAT()** accepts the cell references to the columns A:C cells, with underscores in double quotes in between. And it merges the three columns’ cells to return the output as **APP_3475_iPhone 14 New**.

**Step 4: **Select cell D3 and enter the **CONCAT()**.

*=CONCAT(“APP”,”_”,3474,”_”,”iPhone 13″)*

**Step 5: **Press **Enter** to view the **CONCAT() **return value.

The **CONCAT()** accepts the values in the columns A:C cells, A3:C3, with text values within double quotes and the number value without double quotes. And it combines the cells in the three columns to return the output, **APP_3474_iPhone 13**.

**Step 6: **Choose cell D4 and enter the **CONCAT()**.

*=CONCAT(A4:B4&”_”,C4)*

Next, select the first argument in the **CONCAT()**, **A4:B4&”_”**, and press **F9** to convert the cell range reference to values.

Next, press **Enter** to view the outcome, as shown below.

When we select the first argument, **A4:B4&”_”** inside the **CONCAT() **and press **F9**, it shows the cells A4 and B4 values with appended underscore symbols as an array. And then, when we press **Enter** to execute the resulting formula, it combines the array values and the third column cell C4 value to return the value **APP_3473_iPhone 12**.

The above formula will be useful when we need to include the same symbol between a range of column values.

#### Method #2 – Merge The Cells By Using The “&” Symbol

The steps to perform column merge in Excel by using the ‘**&**’ symbol are as follows:

- Choose a target cell to display the output.
- Enter the ‘
**=**’ symbol, the first column cell value or reference to the value, the ‘**&**’ symbol. And then, enter the remaining columns’ cell values or cell references to the values and required characters, with the ‘**&**’ symbol between each value. - Press
**Enter**to view the column merge in Excel formula output.

**Example #2**

The table below contains the username, domain name and extension details.

And the requirement is to generate an e-mail address based on the given data and display the outcome in cell D2.

Then, here is how to use the ‘**&**’ symbol to merge the given data in cells A2:C2 in columns A, B, and C and achieve the desired output in the target cell D2.

**Step 1: **Select cell D2 and enter the following formula.

*=A2&”@”&B2&”.”&C2*

**Step 2: **Press **Enter** to view the formula output, **allen1990@gmail.com**

Likewise, we can directly enter the values specified in cells A2:C2 to merge the corresponding columns of data using the ‘**&**’ symbol.

And as the given cell values and special characters are texts, we must provide them in double quotes. Otherwise, we will face the condition of **column merge in Excel not working**.

*=”allen1990″&”@”&”gmail”&”.”&”com”*

The ‘**&**’ symbol concatenates the specified values. And thus, the output appears as the required columns merged.

**Example #3**

The table below lists the top best sellers and their author details.

And the requirement is to merge the columns A and B data and display the output in column C in the column C title format. Then, we can use the **CONCAT() **in the target cells and obtain the required output.

**Step 1: **Choose the target cell C2, enter the **CONCAT()**, and press **Enter**.

*=CONCAT(A2,”: “,B2)*

**Step 2: **Update the formula in cells C3:C6 using the Excel fill handle.

The **CONCAT()** in each row accepts three inputs, the corresponding column A cell value, ‘**: **‘, and column B cell value. And it concatenates the three specified values to return an output that shows the two columns, A and B, merged.

We can also use the ‘**&**’ symbol to create a **two column merge in Excel formula** in the target cells and achieve the same output as above.

And for that, choose cell C2, enter the following formula, and press **Enter**.

*=A2&”: “&B*2

And then utilize the fill handle to enter the formula in the remaining target cells.

The ‘**&**’ symbol combines the three input values in the specified order to result in an output that shows the two columns, A and B, merged into one column, C.

##### Example #4

We shall see the steps to merge columns in different sheets.

Consider we have two worksheets in a workbook, **Values **and **Arithmetic Operators**. While the first sheet contains two sets of values, the second lists arithmetic operators.

And we must merge columns A and B values of the **Values** sheet with column A data in the **Arithmetic Operators** worksheet to show and perform the specified arithmetic operations. And assume the target cells are in column C of the **Values** sheet.

Then, we can use the **CONCAT() **in the target cells to show the output as the merged column values.

**Step 1: **Select cell C2, enter the **CONCAT()** as shown below:

*=CONCAT(A2,” “,*

Next, go to the second sheet and click cell A3 to select the required arithmetic operator.

*=CONCAT(A2,” “,’Arithmetic Operators’!A3*

And then, add a comma, click the **Values** sheet tab, and select cell B2 to choose the second value.

** =CONCAT(A2,” “,’Arithmetic Operators’!A3,” “,Values!B**2

And then, enter a comma, ‘ **= **‘, with a space character before and after it, and the last argument as the formula to perform the corresponding arithmetic operation.

*=CONCAT(A2,” “,’Arithmetic Operators’!A3,” “,Values!B2,” = “,(A2+B2))*

Finally, press **Enter** to execute the function.

Likewise, we can update the formulas in the remaining target cells.

**Step 2: **Choose cell C3, enter the **CONCAT()**, and press **Enter**.

*=CONCAT(A3,” “,’Arithmetic Operators’!A4,” “,Values!B3,” = “,(A3-B3))*

**Step 3: **Choose cell C4, enter the **CONCAT()**, and press **Enter**.

*=CONCAT(A4,” “,’Arithmetic Operators’!A5,” “,Values!B4,” = “,(A4*B4))*

**Step 4: **Choose cell C5, enter the **CONCAT()**, and press **Enter**.

*=CONCAT(A5,” “,’Arithmetic Operators’!A6,” “,Values!B5,” = “,(A5/B5))*

The **CONCAT()** in each row accepts seven inputs. And since we refer to the column A value from the **Arithmetic Operators** sheet in the **Values** sheet, the second sheet name appears before the corresponding cell reference. And as we return to the **Values** sheet to refer to column B cell, the first sheet name appears before the cell reference, after which we can complete the formula as usual.

First, the last argument in the **CONCAT()** executes to return the specified arithmetic operation output. And then, the **CONCAT()** concatenates the inputs to return the output as the merged column values.

##### Example #5

The table below lists the top US stocks and their price change percentages.

And the requirement is to show the given data as a comment in each row of column C. Then, we can use the ‘**&**’ symbol to merge columns A and B values to show the required comments.

**Step 1: **Select cell C2, enter the following formula, and press **Enter**.

*=”The price change percentage in the Stock “&A2&” is: “&TEXT(B2,”0.00%”)&”.”*

**Step 2: **Use the fill handle to enter the formula in the remaining target cells.

First, the **TEXT** Excel function displays the price change value as a percentage, according to the specified number format. And then, the ‘**&**’ symbol merges the given texts and the two columns, A and B, values in the specified order to return the required comment in each target cell.

Please note that when we use the **CONCAT()** or the ‘**&**’ symbol to merge columns, the output shows numeric values in the number format interpreted by Excel. For example, a percentage will appear in the decimal format, and a date value will display as a serial number.

Thus, using functions like **TEXT** to display values in the desired format is important.

**Important Things To Note**

- The formulas to column merge in Excel are case-sensitive. So, ensure to provide the column text values in the appropriate cases for merging.
- Consider we need to merge columns with cells containing numbers such as percentages, dates, and time values. Then, use the
**TEXT()**in the column merge formula to display the numbers in the required formats in the output. - The
**CONCAT()**is available from Excel 2016 onwards. So, if the Excel version is older than 2016, use the**CONCATENATE Excel**function to merge columns. - The
**CONCAT()**can accept up to 255 argument values. And if its return value contains over 32767 characters, the**CONCAT()**output is the**#VALUE!**error.

### Frequently Asked Question (**FAQs)**

**1. How to merge 4 columns in Excel?**

We can merge 4 columns in Excel by applying the **CONCAT()**.

For example, the table below shows an employee’s name, birth month, and the date and month of joining the company.

And the requirement is to create an employee ID using the first two letters of the employee name and birth month, joining date, and the first two letters of the joining month. Assume the target cell is E2.

Then, we can use the **CONCAT()** with **LEFT()** in the target cell to obtain the required output.**Step 1:** Choose cell E2, enter the **CONCAT()**, and press **Enter**.*=CONCAT(LEFT(*A*2,2),LEFT(*B*2,2),*C*2,LEFT(*D*2,2))*

First, the three instances of the **LEFT()** return the first two letters of the cells A2, B2, and D2 values, **JO**, **JA**, and **AP**. Next, the **CONCAT() **combines the manipulated columns A, B, and D values and column C value in the order A to D to merge them. And it returns the required employee ID, **JOJA5AP**, in the target cell.

**2. How to merge a date and time column in Excel?**

We can merge a date and time column in Excel utilizing the ‘**&**’ symbol with the **TEXT()**.

For example, the table below shows a report ID and its generation date and time.

And the requirement is to update the report generation details as a statement in cell D2. Then, we can merge columns A, B, and C values using the ‘**&**’ symbol.**Step 1: **Select cell D2, enter the following formula, and press **Enter**.**=”Report “&A2&” Generation Date & Time: “&TEXT(B2,”DD-MMM-YYYY”)&” “&TEXT(C2,”HH:MM:SS”)**

First, the two

**TEXT()**instances display the date and time values according to the specified date and time formats. Otherwise, the date and time will appear as a serial number and decimal in the output.

And then, the ‘

**&**’ symbol merges the specified texts and the column values to return the required output in the target cell.

**3. How do I merge two columns in Excel using Flash Fill?**

You can merge two columns in Excel using **Flash Fill** in the following way, which we will see with an example.

The table below shows two columns, A and B, listing the Microsoft applications and their official colors.

And the requirement is to merge columns A and B data using **Flash Fill** and display the concatenated data in column C.**Step 1:** Choose cell C2, enter or copy the cell A2 value, enter ‘**:**’, enter or copy the cell B2 value, and press **Enter**.**Step 2:** Select cell C2 or the cell below it. And then, select the **Data** tab – **Flash Fill** function or use the keys **Ctrl **+ **E** to access the **Flash Fill **feature.

The **Flash Fill **feature will immediately update the remaining target cells using the same filling pattern we applied to populate cell C2.

However, this method will not work if we use the column merge formulas to update cell C2.

### Download Template

This article must be helpful to understand the **Column Merge In Excel**, with its formula and examples. You can download the template here to use it instantly.

### Recommended Articles

This has been a guide to What Is Column Merge In Excel. We explain how to merge them using two different methods with examples and points to remember. You can learn more from the following articles –

## Leave a Reply