Fahim Shahriyar Dipto
Sometimes, you may need to join names in Excel. Suppose you have a dataset where the first and last names have existed separately. You need to join the names to get the full names. Excel provides you with enormous features and functions to do this operation. Also, you can use VBA macros to join the names. We have tried to discuss all possible ways of concatenating the names. In this article, you can find simple and easy methods to join names in Excel.
Download Practice Workbook
Download the following practice workbook. It will help you to realize the topic more clearly.
7 Methods to Join Names in Excel
We have taken a dataset of some random Names, where we put the First Name and the Last Name separately. Now, we will join them to get the Full Names.
Not to mention, we have used the Microsoft 365 version. You may use any other version at your convenience.
1. Using Ampersand Operator
The ampersand operator (&) generally combines two or more texts. If we set the cell reference with the ampersand, then it will concatenate the text. For joining the names, we use the operator. Follow the below steps.
📌 Steps:
- First of all, go to cell D5 where you want to put the Full Name. Then, write up the formula.
=B5&" "&C5Here, we take cell B5 for the First Name and cell C5 for the Last Name. The ampersand operator combines the two cells. The space between two cells is represented by double quotes (” “).
- Eventually, press ENTER and drag down the Fill Handle tool for other cells.
Finally, you get the result for all of the cells like the image below.
Read More: How to Use Join Formula in Excel (6 Practical Examples)
2. Employing Flash Fill Feature
You can use the Flash Fill feature to combine the names. Follow the steps below to accomplish this.
📌 Steps:
- You have to write down the first name on your own (see the image).
- Consequently, navigate to the Home tab >> choose to Fill from the Editing group >>pick Flash Fill.
Subsequently, all the names will be joined like the image below.
Note: This feature gives you suggestions. It cannot be the proper way to join the names.
Read More: How to Perform Left Join in Excel (3 Easy Ways)
3. Utilizing CONCAT Function
The CONCAT function also joins the names in Excel. Basically, it joins two or more cells. It is a simple function to use. Follow the procedures we have stated below.
📌 Steps:
- Firstly, go to cell D5 and write down the formula.
=CONCAT(B5," ",C5)The CONCAT function joins the text of B5 and C5 respectively, and the ” ” stands for the space between the two names.
Finally, you will get the following output.
Read More: How to Perform Outer Join in Excel (2 Easy Ways)
4. Applying CONCATENATE Function
The CONCATENATE function also follows the same procedure as the CONCAT function. The CONCAT function does not provide any delimiter, whereas the CONCATENATE function does. Also, the CONCATENATE function is compatible with the earlier versions.
📌 Steps:
- Initially, move to cell D5 and insert the function.
=CONCATENATE(B5," ",C5)The CONCATENATE function joins the text of B5 and C5 respectively, and the ” ” stands for the space between the two names.
5. Incorporating TEXTJOIN Function
The TEXTJOIN function concatenates texts with a delimiter. Also, it takes a command to ignore the empty cells. The function we have used to join the names. Let’s follow the below steps.
📌 Steps:
- Primarily, move to the D5 cells and input the formula.
=TEXTJOIN(" ",TRUE,B5:C5)The syntax uses the “ “ as a delimiter to create space. TRUE stands for ignoring empty. And finally, it joins B5 and C5 cells.
6. Using Power Query Feature
The Power Query is a powerful feature in Excel. You can merge the names with this Power Query tool and get the full names. For doing this, follow the below steps.
📌 Steps:
- Firstly, select the entire range of names and navigate to the Data tab>> select From Table/Range.
Consequently, the Create Table dialog box appears. Check the My table has headers box and hit OK.
- It takes you to the Power Query editor. Select both columns with the CTRL key and right-click on the headers. Pick Merge Columns from the Context Menu.
- Eventually, the Merge Columns window appears. Change the Separator to Space. And write the New column name as Full Name. Press OK then.
Sequentially, it merges the columns of names.
Lastly, click on the Close & Load drop down>> pick up Close & Load To…
- Subsequently, the Import Data dialog window appears. Check the Existing worksheet for putting the data and select the cell where you want to put it. Hit OK.
Finally, the output of merged names appears in the existing worksheet.
Read More: How to Join Tables in Excel (6 Suitable Methods)
7. Applying VBA Macros
VBA macros is a programming language for Excel. You can join the names with a VBA code. It is an automatic process that you can run with the tip of a key. We have demonstrated the step to you for better visualization.
📌 Steps:
- In the beginning, select the entire range. It is a must for using the code we have discussed. Then, hover over the Developer tab >> choose Visual Basic.
- Apparently, choose the Insert tab>> Module>> Module 1.
- In Module 1, a General box appears. Write the following code in that box.
Sub JoinNames()
Dim r As Range
Dim ArN() As Variant
Dim ColC As Long
Dim RC As Long
Set r = Selection
RC = r.Rows.Count
ColC = r.Columns.Count
ReDim arrNames(ColC)
If ColC < 2 Then
MsgBox ("You have to select two columns")
End
End If
For i = 1 To RC
For j = 1 To ColC
arrNames(j) = r.Cells(i, j).Value
Next j
r.Cells(i, ColC).Offset(0, 1).Value = Trim(Join(arrNames, " "))
Next i
End Sub
Code Breakdown
- This code first checks if two columns are selected or not. If you don’t select any column, then it will alarm you with the message ” You have to select two columns“.
- After selecting two columns of names, it starts a loop through the applied range and joins it with a space.
- It takes the First and Last names as the array object. Finally, it returns the Full Name.
We have attached the final output in the below image after running the code with the F5 key.
How to Combine Names with Comma in Excel
You can combine the names with a comma. As we have mentioned earlier, the use of the TEXTJOIN function. All you need is to make the delimiter a (,) comma.
- Insert the below formula in cell D5.
=TEXTJOIN(",",TRUE,B5:C5)The syntax creates a comma by using “,” as a delimiter. TRUE is an acronym that stands for ignoring the empty. And finally, it joins B5 and C5 cells.
Practice Section
We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.
Conclusion
That’s all about today’s session. And these are some easy methods to join names in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding, please download the practice sheet. Visit our website, Exceldemy, a one-stop Excel solution provider, to find out about diverse kinds of Excel methods. Thanks for your patience in reading this article.
