In many scenarios, users have to handle or exploit data in Excel in different ways. One such instance is when concatenating names in Excel. By using some formulas or built-in features of Excel, this task becomes quite easy. In this article, I will show you how to concatenate names in Excel.
Download Practice Workbook
You can download the free Excel workbook here and practice on your own.
5 Easy Ways to Concatenate Names in Excel
The process of concatenating names in Excel is quite simple and easy. You can use some Excel formulas or built-in features of Excel to achieve your goal. In our article, you will see five different methods to concatenate names in Excel. By using these methods, you will be able to concatenate or combine names in Excel.
For illustrating the article further I will use the following sample data set with the first names and last names of some random people.
1. Use Ampersand Sign to Concatenate Names in Excel
In the first method, I will use the ampersand sign (&) 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.
Steps:
- First of all, to concatenate names that are in cells B5 and C5, use the following formula with an ampersand sign in cell D5.
=B5&" "&C5
- Secondly, press Enter to see the desired name and then, with the help of AutoFill drag the formula to the lower cells of the column.
- Moreover, if you want to combine names with commas in them then, use the following formula with commas and ampersands in cell D5.
=B5&" , "&C5
- Fourthly, again press Enter and AutoFill to see the result after applying the above syntax.
Read More: How to Concatenate Multiple Cells with Comma in Excel (4 Ways)
2. Apply CONCATENATE Function to Concatenate Names in Excel
I will apply the CONCATENATE function of Excel as my second method 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.
Steps:
- Firstly, to concatenate names, type the following formula in cell D5.
=CONCATENATE(B5," ",C5)
- Secondly, hit the Enter button to see the result, and to drag the formula to the lower cells of the column use the Fill Handle.
- Additionally, to concatenate strings, take any two cells containing strings and concatenate them by using the following formula.
=CONCATENATE(B5," ",C5)
- Lastly, to see the concatenation of these strings press Enter and use AutoFill.
Read More: How to Concatenate Two Columns In Excel (5 Methods)
Similar Readings
- Concatenate Numbers in Excel (4 Quick Formulas)
- How to Combine Rows into One Cell in Excel
- Combine Multiple Cells Into One Cell Separated By Comma In Excel (Functions & VBA)
- How to Combine Date and Text in Excel (5 Ways)
- Merge Rows in Excel (2 Easy Methods)
3. UtIlize TEXTJOIN Function to Concatenate Name in Excel
In this method, I will utilize another function of Excel, which is the TEXTJOIN function. This function will join the values of cells of a given cell range. To learn more about this procedure, see the following steps.
Steps:
- First of all, insert the following formula of the TEXTJOIN function in cell D5 to concatenate the names of cells B5 and C5.
=TEXTJOIN(" ",TRUE,B5:C5)
- Secondly, after pressing Enter and dragging the AutoFill, you will be able to see the desired value in column D.
Read More: How to Concatenate Multiple Cells in Excel (7 Easy Ways)
4. Apply Power Query Tool to Concatenate Names in Excel
In the fourth method, I will use an amazing feature of Excel, which is the Power Query Tool. You can use this feature for withdrawing and altering data. Also, you can use this feature to concatenate names in Excel. You will see the detailed steps for this procedure in the following steps.
Steps:
- Firstly, select the data range B4:C10 in your worksheet and go to the Insert tab of the ribbon.
- Then, from the Tables group, select Table.
- Secondly, in the Create Table dialog box, see if the data range for creating the table is correct or not.
- Then, press OK.
- Thirdly, after converting the data range into a table, go to the Data tab of the ribbon.
- Then, from the Get & Transform Data group, select From Table/Range.
- Fourthly, the previous action will launch the Power Query Editor.
- Then, select both columns by clicking Shift and the left button of the mouse.
- Fifthly, right-click on the mouse after selecting both the columns, and from the context menu, select Merge Columns.
- Afterward, you will see the Merge Columns dialog box.
- Firstly, from the Separator dropdown, choose Space.
- Then, give your preferred name for the name column you want to create.
- Lastly, press OK.
- Consequently, the previous action will combine the names in a single column.
- Then, to transfer this data in your Excel worksheet, press Close & Load in the editor.
- Finally, you will be able to concatenate names using Power Query in Excel.
Read More: How to Concatenate Columns in Excel (8 Simple Methods)
Similar Readings
- Concatenate Date and Time in Excel (4 Formulas)
- How to Concatenate Range in Excel (For Both Old and New Versions)
- Combine Multiple Columns into One Column in Excel
- Opposite of Concatenate in Excel (4 Options)
- How to Add a 1 in Front of Numbers in Excel [7 Special Tricks]
5. Utilize Flash Fill to Concatenate Name in Excel
This is the last method of this discussion. In this method, 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.
Steps :
- Firstly, complete the first name manually in cell D5 like the following image.
- Secondly, when you are going to type the second name, you will see the Flash Fill is auto-suggesting the name for the whole column like the following image.
- Finally, after clicking on that suggestion will fill up the result column with concatenated names.
Read More: How to Concatenate with Space in Excel (3 Suitable Ways)
How to Merge Duplicate Names in Excel
If you have duplicate names in a single column and want to merge them for finding 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.
Steps:
- First of all, take the following data set, where you will find duplicate names in column B.
- Secondly, make new fields for showing results after merging and go to the Data tab of the ribbon.
- Then from the Data Tools group, choose Consolidate.
- Thirdly, you will see the Consolidate dialog box after selecting the previous command.
- Then, under the Function dropdown, select Sum.
- After that, give the proper cell range for consolidating.
- Afterward, under the Use labels in header, select Left column.
- Lastly, press OK.
- Finally, you will see the cells containing duplicate names will be merged.
Read More: How to Concatenate in Excel (3 Suitable Ways)
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.
Conclusion
That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to concatenate names in Excel. Please share any further queries or recommendations with us in the comments section below.
The ExcelDemy team is always concerned about your preferences. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions.
Related Articles
- How to Concatenate Cells but Keep Text Formatting in Excel
- Concatenate Email Addresses in Excel (4 Effective Ways)
- How to Concatenate Rows in Excel with Comma (4 Easy Methods)
- Concatenate Multiple Cells but Ignore Blanks in Excel (5 Ways)
- Carriage Return in Excel Formula to Concatenate (6 Examples)
- How to Add Leading Zeros in Excel by CONCATENATE Operation
- Combine Text in Excel (8 Suitable Ways)