Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


Download Practice Workbook

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


4 Handy Approaches to Merge 3 Columns in Excel

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.

Handy Approaches to Merge 3 Columns in Excel


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.
=B5:B11&" "&C5:C11&" "&D5:D11

 Using Ampersand Operator to Merge Columns in Excel

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.

Read More: How to Merge Columns in Excel (4 Ways)


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 

=CONCATENATE(text1,text2,[text3],...)

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.
=CONCATENATE(B5," ",C5," ",D5)

 Utilizing CONCATENATE Function to Merge 3 Columns in Excel

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.

 Utilizing CONCATENATE Function to Merge 3 Columns in Excel


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 

=TEXTJOIN (delimiter, ignore_empty, text1, [text2], ...)

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.
=TEXTJOIN(" ",TRUE,B5,C5,D5)

 Inserting TEXTJOIN Function to Merge 3 Columns in Excel

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.

 Inserting TEXTJOIN Function to Merge 3 Columns in Excel

Read More: How to Merge Two Columns in Excel Without Losing Data


4. Applying VBA Code o 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.

Applying VBA Code to Merge 3 Columns in Excel

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

Applying VBA Code to Merge 3 Columns in Excel

VBA Code Breakdown

  • Firstly, we specify a name for the subject as
Sub Merge_3_Columns()
  • Secondly, we declare variables as
Dim lRow As ListRow
Dim sValue As String
Dim rCol As Range
Dim rTemp As Range
  • Thirdly, we traverse the rows using this statement as
For Each lRow In ActiveSheet.ListObjects("MergeColumns").ListRows
  • Then, we traverse the columns using this statement as
For Each rCol In lRow.Range
  • Now, we append the column values as
sValue = sValue + " " + rCol.Value
  • Besides, we set the variable as a reference to another variable
Set rTemp = rCol
Next rCol
  • After that, we put our range value in a variable as
rTemp.Value = sValue
  • And, we create space for our merged column value as
sValue = ""
Next lRow
  • Finally, we end the VBA macro as
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.

Read More: How to Merge Two Columns in Excel With a Space


Conclusion

In this article, we’ve coveredhandy methods to merge 3 columns in Excel. We sincerely hope you enjoyed and learned a lot from this article. Additionally, if you want to read more articles on Excel, you may visit our website, ExcelDemy. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.


Related Articles

Bishawajit Chakraborty

Bishawajit Chakraborty

I'm Bishawajit Chakraborty. Hello. I graduated from Rajshahi University of Engineering & Technology (RUET) with a degree in Mechanical Engineering. I'm working with ExcelDemy as a Content Developer for Excel & VBA. You can visit our website, Exceldemy if you'd like to read my published articles on MS Excel and VBA.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo