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
4 Ways to Combine First and Last Name in Excel
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.
Using CONCATENATE Function (or Ampersand)
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 is a new function that was introduced in Excel 2016 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: 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: The 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.
Using the TEXTJOIN function
TEXTJOIN is a function that’s available in Excel 2019 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: The above TEXTJOIN function takes three arguments: 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.
Using Flash Fill
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 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: 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.
Using Power Query
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: 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: The above steps would insert a new worksheet in the workbook with a table that has one column that has the full name. In case you want the original columns as well as a new column that contains the full name, instead of selecting the columns and Merge them, first select the Add Column tab in the Power Query Editor, then select the columns, and then click on Merge Columns. This will add a new column that will have 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:
Separate First and Last Name in Excel (Split Names Using Formulas) How to Sort by the Last Name in Excel How to Extract a Substring in Excel (Using TEXT Formulas) Extract Usernames from Email Ids in Excel How to Extract the First Word from a Text String in Excel Extract Last Name in Excel (5 Easy Ways) Separate Text and Numbers in Excel How to Switch First and Last Name in Excel with Comma?
