Nazmul Hossain Shovon
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.
Download Practice Workbook
You can download the practice workbook from here.
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.
Read More: How to Perform Left Join in Excel (3 Easy Ways)
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.
Read More: How to Join Names in Excel (7 Quick Methods)
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.
Read More: How to Perform Outer Join in Excel (2 Easy Ways)
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.
Read More: How to Inner Join in Excel (2 Easy Methods)
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.
Read More: How to Join Tables in Excel (6 Suitable Methods)
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.
