How to Join Text in Excel (6 Effective Methods)

While working in Excel, we often need to join multiple text strings together. Joining texts manually can be a time-consuming and inefficient way to do the task. Microsoft Excel is quite versatile software that provides us with a number of ways to join text in more efficient manners. In this article, we will discuss six effective methods to join text in Excel. So, let’s start this article and explore these methods.

Download Practice Workbook

6 Efficient Ways to Join Text in Excel

In this section of the article, we will learn six efficient ways to join text in Excel. Let’s say, we have the Employee List of ABC Company as our dataset. In the dataset, we have the First Name and the Last Name of the employees. Our goal is to join these two text strings and display the Full Name.

Not to mention, we used the Microsoft Excel 365 version for this article; however, you can use any version according to your preference.

1. Using CONCAT Function

Using the CONCAT function is one of the most efficient ways to join text. It is to be noted that the CONCAT function is available from Excel 2016 and onwards. Now, let’s follow the steps mentioned below to do this.

Steps:

• Firstly, use the following formula in cell D5.

Here, cell B5 represents the first cell of the First Name column, and cell C5 indicates the first cell of the Last Name column.

• Following that, press ENTER.

Consequently, you will have the Full Name of the first employee as shown in the following image.

• Finally, use Excel’s AutoFill feature to get the rest of the outputs.

Read More: How to Join Names in Excel (7 Quick Methods)

2. Utilizing CONCATENATE Function

As the CONCAT function is not available in all versions of Excel, the CONCATENATE function is available for the earlier versions of Excel. Let’s use the instructions outlined below to join text in Excel using the CONCATENATE function.

Steps:

• Firstly, insert the formula given below in cell D5.

• After that, press ENTER.

As a result, you will have the Full Name of the first employee in cell D5.

• Lastly, use Excel’s AutoFill option to get the remaining outputs as demonstrated in the following picture.

Read More: How to Use Join Formula in Excel (6 Practical Examples)

3. Applying TEXTJOIN Function

Applying the TEXTJOIN function is another efficient way to join text in Excel. Let’s use the procedure discussed in the following section to do this.

Steps:

• Firstly, apply the following formula in cell D5.

• Then, hit ENTER.

As a result, you will have the following output as shown in the following image.

• Now, use Excel’s AutoFill feature to get the Full Names of the remaining employees.

4. Employing Ampersand Operator

Now, we will use the Ampersand Operator of Excel to join text strings in Excel. Now, let’s follow the steps mentioned below to do this.

Steps:

• Firstly, use the following formula in cell D5.

• Then, press ENTER from your keyboard.

Consequently, you will have the Full Name of the first employee in cell D5.

• Finally, use Excel’s AutoFill feature to get the remaining outputs as shown in the image below.

5. Utilizing Flash Fill Feature

Utilizing the Flash Fill option is another smart way to join text in Excel. The Flash Fill tool allows us to automatically fill out the data when it detects a pattern. Now, let’s follow the instructions outlined in the following section.

Steps:

• Firstly, type in the Full Name of the first employee as shown in the following image.

• Now, select cell D5.
• After that, go to the Home tab from Ribbon.
• Then, click on the Fill option.
• Subsequently, select the Flash Fill option from the drop-down.

That’s it! You will have the Full Names of all employees as demonstrated in the image below.

6. Incorporating VBA Macro Option

In this section of the article, we will incorporate the VBA Macro feature of Excel to join text. So, let’s follow the steps discussed in the following section to do this.

Steps:

• Firstly, go to the Developer tab from Ribbon.
• After that, choose the Visual Basic option from the Code group.

As a result, the Microsoft Visual Basic for Applications window will appear on your worksheet.

• Now, go to the Insert tab from the Microsoft Visual Basic for Applications window.
• Following that, choose the Module option from the drop-down.

• Subsequently, write the following code in the newly created Module.

Sub joining_text()
Dim initial_rowN As Long, final_rowN As Long
With Sheets("VBA")
final_rowN = .Range("B" & Rows.Count).End(xlUp).Row
For initial_rowN = 5 To final_rowN
Sheets("VBA").Cells(initial_rowN, 4) = _
.Cells(initial_rowN, 2) & " " & .Cells(initial_rowN, 3)
Next initial_rowN
End With
End Sub

• After writing the code, click on the Save option.

• Then, use the keyboard shortcut ALT + F11 to return to the worksheet.
• After that, use the keyboard shortcut ALT + F8 to open the Macro dialogue box as shown in the following picture.

• Now, in the Macro dialogue box, select the joining_text option.
• Finally, click on Run.

Consequently, you will have the Full Names of all of the employees as demonstrated in the picture below.

Read More: How to Create Cross Join in Excel (3 Simple Ways)

Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it yourself.

Conclusion

So, these are the most common and effective methods you can use anytime while working with your Excel datasheet to join text in Excel. If you have any questions, suggestions, or feedback related to this article, you can comment below. You can also have a look at our other useful articles on Excel functions and formulas on our website, ExcelDemy, a one-stop Excel solution provider.

Related Articles

• How to Perform Left Outer Join in Excel (2 Easy Methods)
• Create Full Outer Join in Excel (2 Ideal Examples)
• How to Join Tables in Excel (6 Suitable Methods)
• Perform Left Join in Excel (3 Easy Ways)
• How to Join Pivot Tables in Excel (with Easy Steps)
• Perform Outer Join in Excel (2 Easy Ways)
• How to Inner Join in Excel (2 Easy Methods)