How to Concatenate Names in Excel (10 Practical Examples)

Get FREE Advanced Excel Exercises with Solutions!

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


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.
=C5&" "&D5
  • Now, drag the AutoFill handle down to copy this formula.

2-Use the Ampersand symbol to concatenate names

Notes:

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

=C5&" , "&D5

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.
=CONCATENATE(B5,C5)
  • After that, press Enter and drag the AutoFill Handle to copy this formula to the cells below.

3-Use the CONCATENATE function to concatenate First and Last Names without space


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.
=CONCATENATE(B5:B16,", ",C5:C16)
  • Then press Enter. (or just press Ctrl + Shift + Enter if you are not an Excel 365)

4-Use the CONCATENATE array formula to concatenate names

Formula Breakdown:

If we break the Array Formula, we will get 12 single formulas like this:

  • CONCATENATE(B5,”, “,C5)
  • CONCATENATE(B6,”, “,C6)
  • CONCATENATE(B7,”, “,C7)



  • CONCATENATE(B16,”, “,C16)

CONCATENATE(B5,”, “,C5) joins the First Names in cell B5, a comma with a space, and then the Last Names in cell C5. Same for the rest of the cells.


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.
=CONCATENATE(B5,",",C5)
  • Joining multiple cells with space: insert the following formula in cell C20.
=CONCATENATE(B6," ",C6)
  • Joining multiple cells with hyphen: enter the following formula in cell C21.
=CONCATENATE(B7,"-",C7)

5-Concatenate first and last name with delimiter

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

6-Use Flash Fill feature

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

7-using Flash Fill feature to Concatenate names

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.
=TEXTJOIN(", ",TRUE,B5,C5)
  • After that, use the AutoFill handle to copy the formula to the rest of the cells in column E.

8-Applying the TEXTJOIN function to join first and last names with hyphen

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:
=CONCAT(C5," , ",D5)
  • Now, press the Enter key and use Fill Handle to apply this formula to the rest of the cells in column F.

9-Using the CONCAT function to concatenate names with comma


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.
=CONCAT(C5,",",CHAR(32),D5)
  • Again, copy this formula up to cell E20 by dragging AutoFill Handle.

10-Concatenating names with comma by using the CONCAT and CHAR functions


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.
=B5&CHAR(10)&C5
  • Now, hit Enter and then use the AutoFill handle to copy the formula to the rest of the cells in column F.

11.1-Concatenate names with line break using the CHAR function

  • 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
=B5& " "&C5&CHAR(10)& D5 & "- " & E5

11.2-Concatenate names, designations, and salary

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.

11.3-Use the Wrap Text command


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.
=CONCATENATE(B5& " "&C5&CHAR(10)& D5 & "- " & E5)
  • Now, hit Enter and then use the AutoFill handle to copy the formula to the rest of the cells in column F.

11.4-Combine names with line break using the CHAR and CONCATENATE functions

Formula Breakdown:

=CONCATENATE(B5& ” “&C5&CHAR(10)& D5 & “- ” & E5)

  • The CONCATENATE function combines the value of cells B5, C5, D5, and E5.
  • The CHAR(10) function breaks the line between the value of cells C5 and D5.

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.
="Salary of"&" "&B5&" "&C5&" "&"is"&" "&TEXT(E5,"$#,#0.00")
  • After that, drag the AutoFill handle to copy the formula up to cell F16.

11.5-Apply Excel TEXT formula to concatenate names and numbers


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.

11-Open a Module window from Insert tab

  • 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

12-Copy and paste the VBA code to run

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

13-Concatenate Names using VBA code


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.

14-Selecting the From TableRange feature

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

15-Selecting the table’s data range

  • 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.

16-Apply the Merge Columns feature from the Power Query Editor

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

17-Select Space as a Separator

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

18-Merged the first name and the last name columns

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

19-Select the Close & Load feature

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

20-Concatenate names using the Power Query Editor

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.

21-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.

22-Check the Delimiter option

  • 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.

23-Check the Space option as Delimiters

  • 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.

24-Inserting destination value

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

25-Microsoft Excel shows a warning message

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

26-Separating the Name into First Name and Last Name


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.

27-Selection of the Consolidate feature

  • 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.

28-Select Sum under the Function dropdown list to sum up the duplicate values

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

29-Merging 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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Araf Bin Jayed
Md. Araf Bin Jayed

I am Araf. I have completed my B.Sc in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Currently I am working as an Excel & VBA Content Developer in Softeko. With proper guideline and aid of Softeko I want to be a flexible data analyst. With my acquired knowledge and hard work, I want to contribute to the overall growth of this organization.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo