How to Concatenate Names in Excel (10 Practical Examples)

In this article, I am going to show you 10 practical examples of how to concatenate names in Excel. You can quickly use these methods even in large datasets to concatenate names or 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.

We have taken a concise dataset to explain the steps clearly. Here is the overview of today’s task.

How to Concatenate Names in Excel: 10 Practical Examples

We have a dataset that contains the salary statements of several employees of XYZ company. Our dataset has 5 unique columns which are Employee ID, First Name, Last Name, Designation, and Salary. We can use the Ampersand symbol, Flash Fill feature, CONCATENATE, CONCAT, TEXTJOIN, TEXT, CHAR functions, VBA code, and Power Query Editor to concatenate names in Excel.

1. Apply Ampersand Symbol to Concatenate Names

I will use the Ampersand symbol (&) to concatenate names in Excel. By using this sign, you can join or concatenate two or more cell values containing names or any other text. Go through the following steps for the full procedure.

• Write down the following formula in cell F5 and press Enter.

• Now, drag the AutoFill handle down to copy this formula.

Notes:

You can combine names with commas. To do that, use the below formula:

Read More: How to Concatenate Date and Time in Excel

2. Using Excel CONCATENATE Function to Concatenate Names

Here, I will apply the CONCATENATE function to concatenate names in Excel. This function will help to join the values of two cells that contain names. Moreover, I will show you how to join two strings in this method using the CONCATENATE function. We can concatenate names with or without space, delimiters, cell value with text, and use the array formula.

2.1 Concatenate First and Last Names Without Space

Here we will concatenate First and Last Names without space using the CONCATENATE function.

• Select cell F5, and write down the following formula.

• After that, press Enter and drag the AutoFill Handle to copy this formula to the cells below.

2.2 Combine First and Last Names with Array Formula

This time we will fill all the cells together, using an Array Formula. Let’s try to concatenate the First Names, and Last Names of all the employees into one single cell using an Array Formula.

• Insert the following Array Formula in cell F5.

• Then press Enter. (or just press Ctrl + Shift + Enter if you are not an Excel 365)

2.3 Join Names with Delimiter

Now we will join Names with delimiters like commas, spaces, different punctuation marks or other characters such as a slash or hyphen. Let’s follow the instructions below to learn!

• Joining multiple cells with commas: write down the following formula in cell C19.

• Joining multiple cells with space: insert the following formula in cell C20.

• Joining multiple cells with hyphen: enter the following formula in cell C21.

Read More: How to Combine Names in Excel with Space

3. Applying Flash Fill Feature to Join Names

In this example, you will see the use of the Flash Fill feature of Excel. This feature can observe the specimen in the users’ data set and then fill the lower cells in the same pattern. To concatenate names by using this feature, see the following steps.

• Write down the First Name and Last Name manually in cell F5. Hence, from your Home tab, go to,

Home → Editing → Fill → Flash Fill

• As a result, you will be able to Fill of the names of the rest of the cells in column F.

Notes:

You can also concatenate names using the keyboard shortcuts. To do that, write down the first and last names and press Ctrl + E.

Read More: How to Combine Name and Date in Excel

4. Apply TEXTJOIN Function to Concatenate Names

The TEXTJOIN function allows us to add multiple texts or values inside a single cell using a delimiter. From our dataset, we will join the First Name and the Last Name of the employees by applying the TEXTJOIN function. Here we will use a hyphen as a delimiter. Let us see how to apply this function in Excel.

• Enter the following formula in cell E5.

• After that, use the AutoFill handle to copy the formula to the rest of the cells in column E.

Read More: How to Concatenate Date/Day, Month, and Year in Excel

5. Using CONCAT Function to Combine Names

Another option is to use the CONCAT function in Excel which works similarly to the CONCATENATE function for concatenating names. Let’s follow the instructions below to learn!

• In cell F5, insert the below formula:

• Now, press the Enter key and use Fill Handle to apply this formula to the rest of the cells in column F.

6. Combining CHAR and CONCAT Functions to Concatenate Names

The CHAR function in Excel is also very helpful to insert delimiters inside a join formula. In this example, we will combine CHAR and CONCAT functions to concatenate names. Let us see how to use this function.

• Select cell E5 and write down the following formula. Hence, hit Enter.

• Again, copy this formula up to cell E20 by dragging AutoFill Handle.

7. Concatenating Names with Line Break

In this example, we will concatenate the First Names, Last Names, Designation, and Salary of the employees with a Line break using the Ampersand (&) symbol or CONCATENATE function along with the CHAR function. The CHAR(10) function in Excel returns the character represented by the ASCII value 10, which is a line break or newline character. This function is commonly used to insert line breaks or start a new line within a cell.

7.1 Use Ampersand Symbol and CHAR Function to Concatenate Names

Here we will combine the Ampersand symbol and the CHAR function to concatenate names with line breaks.

• Select cell F5, and type the following formula in that cell.

• Now, hit Enter and then use the AutoFill handle to copy the formula to the rest of the cells in column F.

• Now we will concatenate not only names but also other parameters like designations, salary To do that, write the below formula in cell G5, and again press Enter. Hence, AutoFill the formula to the rest of the cells in column G

Notes:

Excel occasionally fails to insert a line break when we use the CHAR(10) function. To solve this issue, you can use the Wrap Text command. In Excel, you will find the Wrap Text command in the Alignment group under the Home tab.

7.2 Apply CONCATENATE and CHAR Functions to Concatenate Names

You can use the combination of the CONCATENATE and CHAR functions to combine names with line breaks.

• In cell F5, write down the formula.

• Now, hit Enter and then use the AutoFill handle to copy the formula to the rest of the cells in column F.

8. Using TEXT Function to Concatenate Names and Number Values with Formatting

In the previous example, we learned how to concatenate names in Excel. Here, we will apply the TEXT function to combine the text and number values with formatting. From our dataset, we will join the Name and Salary of the employees of XYZ company. Let’s follow the instructions below to learn!

• Insert the following formula in cell F5, and press Enter.

• After that, drag the AutoFill handle to copy the formula up to cell F16.

9. Run an Excel VBA Code to Concatenate Names

Now, I will discuss how you can concatenate names in Excel using a VBA Macro. Using the VBA code, we will combine the columns named First Name, and Last Name. Let’s see how to do it.

• Press ALT + F11 to bring up the Visual Basic Application You can do this by selecting the Visual Basic feature from the Developer tab.
• After that, Select Insert >> Module.

• Now, write down the following code in that Module, and press the Run button or press the F5 key to run the VBA code.

Sub Concatenate_Names()
	Dim lastRow As Long
	Dim i As Long
	'Find the last row of data in column B
	lastRow = Cells(Rows.Count, "B").End(xlUp).Row
	'Loop through each row and concatenate the values from columns B, and C
	For i = 1 To lastRow
    	Cells(i, "F").Value = Cells(i, "B").Value & " " & Cells(i, "C").Value
	Next i
End Sub

• As a result, you will be able to concatenate names with the header of the columns.

10. Excel Power Query Editor to Combine Names

Power Query in Excel is a powerful tool for combining names in a data table. Let’s follow the instructions below to learn!

• Go to the Data tab >> Select From Table/Range from Get & Transform Data group.

• Now, in the Create Table window, select the table location and check the My table has headers option >> Hit OK.

• Next, in the Power Query Editor, select the three columns using Ctrl+Left Click >> Go to the Add Column tab >> Click on Merge Columns feature from From Text group.

• After that in the Merge Columns window, select Comma from the separator drop-down list and give the new column a name >> Click OK.

• As a result, you will be able to join two columns using the Power Query Editor.

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

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

Read More: Excel VBA: Combine Date and Time

How to Separate Names in Excel

In this section, we will separate names in Excel. From our dataset, we will separate the First Name and Last Name from the Names. To do that, we will use the Text to Columns feature. Let’s follow the instructions below to learn!

• Select the data range of names, we choose B5:B16 from our dataset >> Go to the Data tab >> Select the Text to Columns feature from the Data Tools group.

• As a result, the Convert Text to Columns Wizard – Step 1 of 3 dialog box pops up. Check the Delimiter option >> Hit the Next button from that dialogue box.

• Now, check the Space option from the Delimiters >> Press the Next button from the Convert Text to Columns Wizard – Step 2 of 3 dialog box.

• After that, type =$E$5 in the Destination box >> click the Finish button from the Convert Text to Columns Wizard – Step 3 of 3 dialog box.

• Hence, Microsoft Excel shows a warning message. Now press OK from that window.

• Finally, you can separate the Name into First Name and Last Name which has been given in the below screenshot.

How to Merge Duplicate Names in Excel

If you have duplicate names in a single column and want to merge them to find their total then the following method will be helpful for you. In the following procedure, you will find how to merge duplicate names in Excel. Go through the following steps for a better understanding.

• Make new fields for showing results after merging and go to the Data tab >> Choose Consolidate from the Data Tools group.

• As a result, the Consolidate dialog box will appear in front of you. From that dialog box, select Sum under the Function dropdown list >> give the proper cell range for consolidating >> Check the Left column option from the Use labels in header >> press OK.

• Finally, you will be able to merge the cells that contain duplicate names.

Read More: How to Combine Two Formulas in Excel

Things to Remember

• While writing syntax for the function and the ampersand, remember to insert proper space and quotation marks.
• Use the Power Query on your data range after converting it into a table. Thus it will make the combination process easier.

Frequently Asked Questions

1. Can I concatenate names with a title or salutation included?

Answer: Yes, you can concatenate names with a title or salutation. Include the desired title or salutation as a text string within the formula. For example, =CONCATENATE(“Mr. “, A1, ” “, B1) or =”Mr. ” & A1 & ” ” & B1.

2. Can I concatenate names with a specific capitalization format?

Answer: Yes, you can modify the capitalization of the concatenated names using functions like UPPER, LOWER, or PROPER. For example, to capitalize only the first letter of each name component, you can use =PROPER(A1) & ” ” & PROPER(B1).

3. How can I concatenate multiple names with a comma separator?

Answer: To concatenate names with a comma separator, you can use the CONCATENATE function or the ampersand (&) operator. For example, =CONCATENATE(A1, “, “, B1) or =A1 & “, ” & B1.

Download Practice Workbook

Download this practice workbook for practice while you are reading this article.

Conclusion

I hope that you were able to apply the examples that I showed in this tutorial on how to concatenate names 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 Articles

• How to Copy CONCATENATE Formula in Excel
• Carriage Return in Excel Formula to Concatenate

<< Go Back to Excel Concatenate Text | Concatenate Excel | Learn Excel