You are reading the article How To Combine First And Last Name In Excel (4 Easy Ways) updated in September 2023 on the website Khongconthamnam.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 How To Combine First And Last Name In Excel (4 Easy Ways)
Excel is an amazing tool to store and analyze data. And many of the times, you will have to deal with text data types such as names, regions, departments, or product names.
In such cases, it’s good to know how to manipulate text data and get the desired result.
One of the most common tasks most Excel users have to do is work with a dataset of names. Often you’ll find that the first name and the last name are in separate columns, and you may have a need to combine these first and last names and get these as a combined name in a cell.
In this Excel tutorial, I’ll show you multiple different ways to combine the first and the last name in Excel.
You can easily do that using simple formulas (such as Concatenate or TextJoin) and features such as Flash Fill and Power Query
Suppose you have a dataset as shown below and you want to combine the first name in column A and the Last Name in column B.
Let’s have a look at some of the ways to do this.
Combining different text strings from different cells is quite easy in Excel. There is an in-built Concatenate formula in Excel that’s made for this purpose only.
Below is the formula that will combine the first and the last name (separated by a space character):
=CONCAT(A2," ",B2)CONCAT is a new function that was introduced in Excel 2023 and is made to replace the CONCATENATE function. But as of now, both the functions continue to be available and you can either function.
Below is the CONCATENATE formula if you wish to use that:
=CONCATENATE(A2," ",B2)The above formula simply takes the first and the last name and combines it. And since I want these to be separated by a space character, I have used ” ” (space in double-quotes) as the second argument.
You can also use the ampersand operator to do the concatenation.
Assuming you have the same dataset, you can use the below formula to combine the first and the last name:
=A2&" "&B2The ampersand operator combines the text before and after it. In the above example, I have combined three parts – first name, a space character, and last name.
Now that you understand the concept, you can combine the names in different formats if you want. For example, you may want to have the last name and then the first name, or a comma instead of the space between the names.
In case you only want the combined name and want to get rid of the first and the last name, you should first convert the formula values to static values. Once done, you can then remove/delete the first and the last name.
TEXTJOIN is a function that’s available in Excel 2023 and Office 365.
In case you have access to this function, it’s best to use it for combining cells and columns (as it’s a lot better than the above CONCATENATE and ampersand methods).
Suppose you have the dataset as shown below and you want to combine the first and the last name.
Below is the formula to do this:
=TEXTJOIN(" ",TRUE,A2:B2)The above TEXTJOIN function takes three arguments:
The delimiter, which is a space character in double-quotes in this example (since we want the first and the last name to be separated by a space character)
A Boolean value where TRUE means that in case there are any blank cells, the formula will ignore it
The range that has the cells that you want to combine
It’s faster than the regular concatenate formula and is also easier to create and manage. So if you have access to the TEXTJOIN function in Excel, it’s better to use it over any other formula.
Flash Fill is a smart functionality that tries to understand the pattern and give you the result.
Let me explain how it works.
Suppose you have a dataset as shown below and you want to combine the first and the last name.
Below are the steps you can use to do this using Flash Fill
In cell C2, enter the result you want. In our example, it would be ‘Bobby Baker’
In cell C3, start typing the expected result. You will notice that Excel shows you the expected result in all the cells (in the light gray text). This is Flash Fill in action
Hit the Enter key
The above steps would instantly fill all the cells with the combined name.
In some cases, it’s possible that you won’t see flash fill while you are typing in the second cell.
Don’t worry, it happens sometimes.
In such a scenario, below other steps you can use to make Flash Fill work:
In cell C2, enter the result you want. In our example, it would be ‘Bobby Baker’
Select select C3
The above steps would instantly pick the pattern from the cell above and fill the entire column with the combined name.
In case Flash fill isn’t able to pick up the right pattern and gives incorrect result, fill two cells manually and then do the above steps.
You can also use the keyboard shortcut Control + E to fill using Flash fill.
Combining the first and the last name (or even the first, middle, and last name) is a simple operation that Flash Fill can easily handle.
Keep in mind that Flash Fill is not perfect. It works by identifying patterns and using the same pattern to fill all the cells in the column. While it’s most likely to work as expected, it’s a good idea to double-check the result of Flash Fill.
Power Query is an amazing tool that used to extract and transform data.
You can also use this to quickly merge columns and combine the first and the last name.
For Power Query to work, your data needs to be in an Excel Table (or at least a Named Range).
For the purpose of this tutorial, I will convert the data set that has the 1st and the last name into an Excel Table.
Suppose you have a data set as shown below and you want to merge the two columns to get the full name.
Below are the steps to convert the data into an Excel Table:
Select any cell in the dataset
In the Create Table dialog box, make sure the range is correct
The above steps would convert the data range into an Excel Table.
Now let’s see how to combine the first and last name using Power Query:
Select any cell in the table
In the Power Query Editor, make sure the right table is selected in the left pane. If you just have one table, you will only see one option in the left pane
In the Merge Columns dialog box that opens, select Space as the delimiter (from the drop-down)
Enter a name for the new merged column. I will go with ‘Full Name’
The above steps would insert a new worksheet in the workbook with a table that has one column that has the full name.
Compared to the formula methods and Flash Fill, Power Query is definitely a bit longer.
But the benefit of using this method is that in case your original data changes, you can quickly refresh the query and your resulting data would automatically update.
Also, Power Query is widely used to combine different tables and data from multiple worksheets and workbooks. So, in case you have the name data you want to combine, it’s one single step in your already existing power query workflow.
In this tutorial, I have covered how to combine the first name and the last name in Excel. But in case you have the first, middle, and last name, you can use the same methods to do it.
I hope you found this tutorial useful.
Other Excel tutorials you may also like:
You're reading How To Combine First And Last Name In Excel (4 Easy Ways)
Update the detailed information about How To Combine First And Last Name In Excel (4 Easy Ways) on the Khongconthamnam.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!