# How to Use Join Formula in Excel (6 Practical Examples)

In this tutorial, I am going to show you 6 practical examples to use the join formula in Excel. You can quickly use these methods even in large datasets to join multiple columns in various ways. Throughout this tutorial, you will also learn some important excel tools and functions which will be very useful in any excel related task.

## 6 Practical Examples to Use Join Formula in Excel

We have taken a concise dataset to explain the steps clearly. The dataset has approximately 6 rows and 3 columns. Initially, we are keeping all the cells in General format. For all the datasets, we have 3 unique columns which are Employee, Position, and Joined. Although we may vary the number of columns later on if that is needed.

### 1. Using Ampersand Operator

In this first method, we will see how to quickly join texts in Excel using the Ampersand formula. Follow the steps below to do this.

Steps:

• First, go to cell D5 and insert the following formula:
`=B5&", "&C5`
• Now, press Enter and drag the Fill handle down to copy this formula.

• As a result, this formula will join the separate texts together.

### 2. Join Formula Using Excel CONCATENATE Function

We can also use the CONCATENATE function in Excel to apply the join formula very easily. Below are the steps to do this.

Steps:

• To begin with, double-click on cell D5 and enter the below formula:
`=CONCATENATE(B5,", ",C5)`
• After that, press Enter to confirm and copy this formula to the cells below.
• Then, drag Fill Handle to copy this formula to the cells below.

• Finally, you should see the end result below.

### 3. Applying CONCAT Function

Another option we can use to use the join formula in our dataset is to apply the CONCAT function in Excel which works similarly to the previous function.

Steps:

• To begin this method, double-click on cell D5 and insert the formula below:
`=CONCAT(B5,", ",C5)`
• Now, press the Enter key and use Fill Handle to apply this formula to the cells below in column D.

• Consequently, the CONCAT function should accurately join the texts that we wanted in column D.

### 4. Utilizing TEXTJOIN Function

The TEXTJOIN function allows us to add multiple texts or values inside a single using a delimiter. Let us see how to apply this function in Excel as a join formula.

Steps:

• First, navigate to cell D5 and type in the formula below:
`=TEXTJOIN(", ",TRUE,B5,C5)`
• Next, take this formula to the below cells using Fill Handle.

• Immediately, this should give you the result as in the image below.

### 5. Combining CHAR and CONCAT Functions

The CHAR function in Excel is also very helpful to insert delimiters inside a join formula. Let us see how to use this function.

Steps:

• In the first place, enter the formula below in cell D5:
`=CONCAT(B5,CHAR(44),CHAR(32),C5)`
• Again, copy this formula by dragging Fill Handle.

• As a result, this formula will generate a similar result to what we saw previously.

### 6. Excel Power Query Editor to Use Join Formula

Power Query in Excel is a very powerful tool to use join formula in a data table. Follow the steps below to see this in action.

Steps:

• Initially, go to the Data tab and click on From Table/Range.

• Now, in the Create Table window, select the table location and check the My table has headers
• Then, click OK.

• Next, in the Power Query Editor, select the two columns using Ctrl+Left Click.
• Then, go to the Add Column tab and click on Merge Columns.

• After that in the Merge Columns window, select a separator and give the new column a name.
• Next, click OK.

• Now, go to the Home tab and click on Close & Load.

• As a result, this will generate a new column and join the two columns as we wanted.

## Conclusion

I hope that you were able to apply the methods that I showed in this tutorial on how to use the join formula in Excel. As you can see, there are quite a few ways to achieve this. So wisely choose the method that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more Excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.

## Related Article

#### Nazmul Hossain Shovon

Hello, I am Nazmul Hossain. I am currently working full-time in Exceldemy as an Excel & VBA Content Developer. I have completed my bachelors in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I am interested in working with MS Excel. I also like coding web applications a lot.

We will be happy to hear your thoughts

5 Excel Hacks You Never Knew