How to Merge 3 Columns in Excel (4 Easy Ways)

You may combine your data and make your sheet look more organized by learning how to combine numerous columns in Excel without losing data. In this article, we will show you how to merge 3 columns in Excel.

How to Merge 3 Columns in Excel: 4 Easy Ways

You can save time by using numerous columns in Excel rather than manually merging them. So, in the following methods here, you will learn how to merge 3 columns in Excel by using the ampersand operator, utilizing the CONCATENATE function, inserting the TEXTJOIN function, and applying the VBA code. Let’s suppose we have a sample data set below here.

1. Using Ampersand Operator to Merge 3 Columns

You can combine text elements using the ampersand (&) operator without using a function. When generating strings, the ampersand operator is frequently a faster and simpler method to use than the other methods. Here, we will use this operator to merge 3 columns in Excel.

Step 1:

• Firstly, select the E5 cell.
• Then, write down the following formula.

Step 2:

• Now, press ENTER to apply the formula.
• Consequently, the first three columns have been merged in the E5 cell.
• Then, drag the Fill Handle tool from the E5 cell to the E11 cell.

Step 3:

• Finally, the E column will display the cell values for all three combined columns, as can be seen in the screenshot down here.

2. Utilizing CONCATENATE Function to Merge 3 Columns in Excel

Another practical method for combining two or more text strings into one is to use the CONCATENATE function. You will discover how to merge 3 columns using this technique and the CONCATENATE function. The general syntax of the CONCATENATE function is shown below.

Formula syntax of the CONCATENATE function 

Arguments of the CONCATENATE function

• Text1 represents the first text value to merge together.
• Text2 represents the second text value to merge together.
• Text3 represents the third text value to merge together (optional).

Step 1:

• Firstly, select the E5 cell.
• Then, type the following formula with the CONCATENATE function.

Step 2:

• Now, hit ENTER to apply the formula.
• Consequently, the E5 cell conveys the first three columns’ first cell values, which have been merged below.
• Finally, drag the Fill Handle tool from the E5 cell to the E11 cell.

Step 3:

• As a result, you can see in the screenshot below, that the E column will show the cell values for all three merged columns.

3. Inserting TEXTJOIN Function to Merge 3 Columns

It appears that a more effective solution has developed with the introduction of the TEXTJOIN function and allowing you to merge multiple columns in a more flexible manner with any delimiter in between.

Formula syntax of the CONCATENATE function

Arguments of the CONCATENATE function

• The delimiter displays the separator in between text.
• Ignore_empty indicates whether or not to ignore blank cells.
• Text1 represents the first text value or range.
• Text2 represents the second text value or range (optional).

Step 1:

• Firstly, choose the E5 cell.
• Then, write down the following formula.

Step 2:

• Now, hit ENTER to apply the formula.
• Therefore, the first three columns’ first cell values will merge here in the E5 cell.
• Lastly, use the Fill Handle tool from the E5 cell to the E11 cell.

Step 3:

• Lastly, the E column will represent all the three merged columns here.

4. Applying VBA Code to Merge 3 Columns in Excel

VBA is a programming language that may be used for a variety of tasks, and different types of users can use it for those tasks. Using the Alt + F11 keyboard shortcut, you can launch the VBA editor. In the last section, we will generate a VBA code that makes it very easy to merge 3 columns in Excel.

Step 1:

• Firstly, we will open the Developer tab.
• Then, we will select the Visual Basic command.

Step 2:

• Here, the Visual Basic window will open.
• After that, from the Insert option, we will choose the new Module to write a VBA code.

Step 3:

• Now, paste the following VBA code into the Module.
• Then, to run the program, click the “Run” button or press F5.

Sub Merge_3_Columns()
'Declaring variables
Dim lRow As ListRow
Dim sValue As String
Dim rCol As Range
Dim rTemp As Range
'Traversing rows using this statement
For Each lRow In ActiveSheet.ListObjects("MergeColumns").ListRows
'Traversing columns using this statement
For Each rCol In lRow.Range
'Appending column values
sValue = sValue + " " + rCol.Value
Set rTemp = rCol
Next rCol
rTemp.Value = sValue
sValue = ""
Next lRow
End Sub

Sub Merge_3_Columns()

Dim lRow As ListRow
Dim sValue As String
Dim rCol As Range
Dim rTemp As Range

For Each lRow In ActiveSheet.ListObjects("MergeColumns").ListRows

For Each rCol In lRow.Range

sValue = sValue + " " + rCol.Value

Set rTemp = rCol
Next rCol

rTemp.Value = sValue

sValue = ""
Next lRow

End Sub

Step 4:

• Finally, as you can see, the  E column will demonstrate all three merged columns’ cell values in the below image here.

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it by yourself.

Conclusion

In this article, we’ve covered 4handy methods to merge 3 columns in Excel. We sincerely hope you enjoyed and learned a lot from this article. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.

<< Go Back to Columns | Merge | Learn Excel