How to Join Names in Excel (7 Quick Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset

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&" "&C5

Here, 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 (” “).

Using Ampersand Operator to join names in Excel

  • Eventually, press ENTER and drag down the Fill Handle tool for other cells.

Fill Handle to join names in excel

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.

Employing the Flash Fill Feature to join names in Excel

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.

Utilizing CONCAT Function to join names in Excel

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.

Applying the CONCATENATE Function to join names in excel


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.

Incorporating the TEXTJOIN Function to join names in excel


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.

Using the Power Query Feature to join names in Excel

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.

Power query editor

  • Eventually, the Merge Columns window appears. Change the Separator to Space. And write the New column name as Full Name. Press OK then.

Merge Columns Window

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.

Import data window

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.

Applying VBA Macros to join names in Excel

  • 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

VBA code to join names in Excel

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.

How to Combine Names in Excel with Comma


Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.

Practice Section


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.


Related Articles

Fahim Shahriyar Dipto
Fahim Shahriyar Dipto

Hello! Welcome to my Excel blog! I am a big fan of MS Excel. I am learning new and exciting things in Excel and writing the process here. I think this will be helpful for you to get used to Excel. Keep visiting our website for new and updated Excel methods.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo