Altogether, Excel will allow you to combine up to 255 different items. Now you can go ahead and delete the original columns if you need to. We want the merged full name to appear as the first name – a single space – then family (or last) name. Normally, in the Excel, the MID function which combines the SEARCH and LEN functions can help you to flip the first and last names as you need. Once you have sorted all the columns by the last name column, you can remove the first name and last name columns so only the column with the combined names remains. We can do this using any of the following Excel formulas: =RIGHT(A2,LEN(A2)-SEARCH(" ",A2))&", "&LEFT(A2,SEARCH(" ",A2)-1), =REPLACE(A3,1,SEARCH(" ",A3),"")&", "&LEFT(A3,SEARCH(" ",A3)-1), =MID(A4&", "&A4,SEARCH(" ",A4)+1,LEN(A4)+1), Last_Name First_Name = First_Name Last_Name. Select the cell containing the last name (B2). First Name. In the example, the active cell contains this formula: = RIGHT(B4,LEN(B4) - FIND("*",SUBSTITUTE(B4," ","*",LEN(B4) - LEN(SUBSTITUTE(B4," ",""))))) Another method of combining the First name and Last name is Concatenation in excel. Flash Fill works when Excel recognizes a pattern in your data and automatically fills in the other cells of the column with the same pattern for you. Excel makes this process straightforward, and there are many different approaches you can take. Now I have used the CONCATENATE function to join these cells and create a new name format. There are plenty of other ways in Excel, like using VB script, or the TEXTJOIN function (available in newer versions and Office 365). But you can also use the & operator in this way: So, these are my methods to switch first and last names in Excel with a comma or without a comma. Bush, George W.) which I want to convert to George W. Bush. There are two formulas in Excel that let you combine first and last names into one cell. Therefore, the first name will go to the first … I hope you found this Excel tutorial useful! Step 4 Choose "A to Z" or "Z to A" in the Order drop-down list and click "OK" to sort the table. Combines the names above, separated by a space (Nancy Davolio) =B3&", "&A3. I earn a small commission if you buy any products using my affiliate links to Amazon. 1. The ampersand (&) is more of an operator than a formula. It is mainly used to join several text strings into one. Now you’ll find the first and last name combined in cell C2 but with space in between. Simply double click the fill handle (located at the bottom right of cell C2). RIGHT has this structure: RIGHT(text,num_chars))text is the text to which you’re applying your formula.. num_chars tells RIGHT how many characters to the right it must pick from text. Home » How to Merge First and Last Name in Excel (2 Easy Ways). In column E, we want to get a joined text from the columns B, using the space as a separator. Switch-first-and-last-name-in-Excel-with-comma.xlsx. For inserting the date on which the workbook was saved the last time use the following VBA code. If you now decide that you don’t want the original two columns anymore and delete columns A and B (in our example), you’ll get an error in all cells of the combined column. 4# choose Destination Cell, such as: C1, then click “Finish” button. Formula. Open up the excel sheet which has the full names stored in Column A.Now, do a right click on the column header just next to it. 3# select Space option in Delimiters section box, then click “Next” button. Since Excel works on what’s in the parenthesis first (like in math), it will combine the first name, a space, and then a last name and put the correct capitalization on … The first method involves 2 steps. Other Excel tutorials you may find useful: Set a Row to Print on Every Page in Excel, Extract URL from Hyperlinks Using Formula, 2021 © Spreadsheet PlanetPrivacy Policy | Sitemap, How to Merge First and Last Name in Excel (2 Easy Ways), Merge First and Last Name using Flash Fill, How to Split One Column into Multiple Columns in Excel, How to Make all Cells the Same Size in Excel (AutoFit Rows/Columns), How to Reverse a Text String in Excel (Using Formula & VBA). Any of the following formulas will do so: =RIGHT(A2,LEN(A2)-SEARCH(" ",A2))&" "&LEFT(A2,SEARCH(" ",A2)-1) Please download the following Excel file that I have used to write this article. ExcelDemy.com is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program. In our spreadsheet, the first and last names are separated by a space. Also, replace the first cell reference with the second cell reference and vice-versa. I have a long name list (e.g. In this way, we shall split the names into their individual parts using Excel’s Text to Columns feature. Select the cell containing the last name (B2). Here, we specified that if cell B2 is not blank (that means if a middle name exists), we want just the first character from the cell B2. Under the Data tab, click on the Flash Fill button (in the ‘Data Tools’ group). I recently ran into a situation where I had an extensive list of last name, first name entries that our client wanted to see in first name then last name order. Place the cursor in cell C2. On the next screen, select the Delimited option and click on the Next button. In the generic form of the formula (above), name is a full name, with a space separating the first name from other parts of the name. If Excel senses a pattern (in most cases it does), it will populate the first names in all other cells automatically. Then on the next screen choose Space and click Next once more: 4. Combine First and Last Name in Excel – Example #2. Put a space enclosed in double quotes (“ “), since you want the first and last name separated by a space. Explore more articles on Excel text functions here. You have to use any given method which helps you to solve your problem. Andrew. In this method, we shall switch the order of the first and last name in Excel with a comma like the following: First Name Last Name = Last Name, First Name. This is because the formulas were all referring to cells in columns A and B, which don’t exist anymore. It’s now time to copy this formula to the rest of the cells in the column. Microsoft Excel is often used to store data including personal information like names, addresses, contact numbers, etc. Add a new column next to the column with the original names and type the name part that you want to extract in the first cell (the first name in this example). This is what we shall do in this method: Last_Name First_Name = First_Name Last_Name. Since this method doesn’t involve a formula, it doesn’t depend on the original columns. I knew this was something I could change in excel… If you want to get the first name from a full name list in Column B, you can use the FIND function within LEFT function in excel. In the generic version of the formula above, first is first name, and last is last name. Data that we will use in the examples. The CONCATENATE() function provides the same functionality as the ampersand (&) operator. ExcelDemy is a place where you can learn Excel, Data Analysis, and other Office related programs. It’s important to note though, that these are not the only two ways to do this. Sometimes a name might have more than two parts. A common problem is the full name is in the data in a single excel column. This is because it makes filtering and querying easier. Finally, we concatenated the value in C2 (the last name). We shall try to give you a solution. The process involves separating the text into two columns, one for the first name and one for the last name. We provide tips, how to guide and also provide Excel solutions to your business problems. If you’re using any version of Excel from 2013 onwards, you’ll get Excel’s flash fill functionality. Also read: How to display Number as currency in Microsoft excel. We can do this using any of the following 3 Excel formulas: =RIGHT(A2,LEN(A2)-SEARCH(",",A2)-1)&" "&LEFT(A2,SEARCH(",",A2)-1), =REPLACE(A3,1,SEARCH(",",A3)+1,"")&" "&LEFT(A3,SEARCH(",",A3)-1), =MID(A4&" "&A4,SEARCH(", ",A4)+2,LEN(A4)-1). You can use the Left function, Right function and Find function to spit a full name to the first name and last name with following steps. Select the column that contains your data; Data Tab – Data Tools – Text To Columns; In Step 1 of the Wizard select Delimited Copy this formula to the rest of the cells in the column by double-clicking the fill handle (located at the bottom right of cell C2). Here’s how you can use it to merge your first and last names. So if you make any changes to the original values, they get reflected in the combined values. In our example, your formula should now be: =CONCATENATE(A2,” “,B2). The first step to sorting by the last name is to get the last name in a separate column. Enter the first and last name for the first row manually. Steps to split full name into first and last name in Excel. These two were the methods we found to be the simplest and applicable to all. Add &” ” after the cell reference for the first name. Method 1. Last Name. Click on the first cell of the column where you want the combined names to appear (C2). Join First and Last Name Using the TEXTJOIN function In the example, the active cell contains this formula: = B4 & " " & C4 You can do that by replacing everything before the last name with a blank so that you only have the last name left. Think of the format of the combined text. So for our example, your formula now is =A2&” “&B2. Related Articles: Join first and last name in excel. You can replace the separating character with any character you need. Excel Formulas for Splitting Names into Parts Press the ENTER button. num_chars is arbitary. In this way, you can have names joined exactly the way you like. Separating Out Last Names. Click on the first cell of the column where you want the combined names to appear. This is a really cool technique. Perfect! Select the entire Column containing Names > click on the Data tab in the top menu bar and click on Text to Columns option in “Data Tools” section. Flip the first and last name in a column with a function. So for our example, your formula now is =A2&” “&B2. Even I have shown how to switch names with three or more parts. 3. Nancy. The generic formula is as follows:=LEFT (B1,FIND (” “,B1)-1).… Split full name to first and last name Type first and last name for the first name. Mostly peoples use cut and paste method to easily switch or flip name in Excel sheet. In this tutorial, we will look at two such methods. 2) Switch the Order of the First and Last Name in Excel with Comma, 3) Switch the Order of the First and Last Name in Excel without Comma, 4) Using Excel Flash Fill Feature (for Excel version 2013 and later), 5) Using Text to Columns Feature of Excel, How to Separate Words in Excel Using Formula (Ultimate Guide), How to Merge Two Cells in Excel without Losing Any Data, How to Split Cells in Excel (The Ultimate Guide), 15 Best Online Excel Training Courses | Learn Advanced Excel Online, How to use point and click method in Excel [Make Formula Error-less], Find difference between two numbers (positive or negative) in Excel, How to apply same formula to multiple cells in Excel (8 easy ways), Excel formula to calculate percentage of grand total (4 Easy Ways), Adding and subtracting in Excel in one formula, Able2Extract Professional 15 Review 2020 (with 15% Discount), At first, make your desired output in the cell, Select the cells where the names are and then open the. Select the cell containing the first name (A2) followed by an ampersand (&). These are the simple ways to reordering the first and last name. The bad news is, in Excel, there is no specific function to extract first and the last word from a cell directly. To change that, you can add space within the formula. We can use one of Excel's formulas to flip the first and last name quickly in a column with following steps: Enter the formula =MID(A2&" "&A2,FIND(" ",A2)+1,LEN(A2)) in a blank cell, says Cell C2. Please feel free to state your query or feedback for the above article. In the generic form, here are the formulas to combine first and last name in Excel: = first_name_cell &" "& last_name_cell CONCATENATE (first_name_cell," ", last_name_cell) In the first formula, concatenation is done with an ampersand character (&). Description (Result) =A2&" "&B2. Let’s look at them one by one. You will notice that the first name and last name are combined together alright, but without any space in between. You have full name in a single column and want to get the solution to separate them. We shall try to give you a solution. In the pop-up window that appears choose Delimited and click Next. STEP 1. We followed this by a dot operator and space. If you have any specific problems, let us know in the comment section. There are times when the names in the data are full names and you would like to split them into first names and last names (sometimes with middle names). A last name is all the letters (characters) to the right of the space.We’ll use Excel's RIGHT function to do that. So, even if you delete the original two columns containing first and last names respectively, the combined column remains unaffected. This technique takes advantage of Excel’s pattern recognition capabilities. Select the cell containing the first name (A2) followed by an ampersand (&) Select the cell containing the last name (B2). Here is a tutorial that will help you create separate First Name and Last Name columns using formulas. Press the Return Key. Depending on the problems, you might face many problems. Provide tips, how to display Number as currency in Microsoft Excel, click on the original values, get. Name using the last name, and other Office related programs click “Next” button Excel’s fill., replace the first and last is last name in a column with a comma keeping. Date on which the workbook was saved the last name combined in cell C2 with space in between original columns! Face many problems use retyping method of combining the first and last in. `` & A3 tutorial that will help you create separate first name and for... Switch the Order of the column where you have any specific problems, you can combine Texts,,. Range of cells in the pop-up window that appears choose Delimited and click Next once more 4... 2013 onwards, you don ’ t depend on the original values, they get reflected the. Methods I have a how to put last name, first in excel as shown below and you want to use “Hi..., you ’ ll find the first and last name columns using formulas case, you can take this! Concatenate function to do this # choose Destination cell, such as C1... Which don ’ t need to combine up to 255 different items write this article, I will show to! ( A2 ) followed by an opening bracket to your business problems ”. To easily switch or flip name in Excel name will go to first. Into their individual how to put last name, first in excel using Excel’s text to columns feature a cell you. Have more than two parts one above by last name as shown below split full name into first and last! A Mac ) takes the specified Number of characters starting from the columns B, using the last separated! So, even if you have both first and last name, and last names respectively, the first of. Small commission if you buy any products using my affiliate links, meaning when you need to this... W. bush add & ” ” after the cell containing the last name are combined together,. € after the cell reference with the second cell you combine first last. A column with a blank so that you only have the formula that contain,! We need to split full name to appear as the ampersand ( & ) operator t need combine. List ( e.g replacing everything before the last name comma (,.. Full name into first and last name separated by a comma (, ) inserting the date on which workbook! Used the CONCATENATE function to join several text strings into one cell ’ t a! Name for the first and last name are combined together alright, but without any space in between Excel!, which don ’ t exist anymore now is =A2 & ” ” the... Therefore, the first step to sorting by the last name with a function which can extract this news... Cell C2 but with space in between exceldemy.com is a place where you want the merged full name Excel! An Excel list to do disclosure: this post may contain affiliate links to Amazon column... Way, we can combine first and last name in Excel sheet the. Important to note though, that these are not the only difference between the two is way! Function CONCATENATE, followed by an opening bracket last name as shown below a long name list (.! There may be times when you click the links and make a new name format the way are!, click on the original columns if you ’ ll get Excel ’ important! Column E, we want to extract first and last name for the first name A2... Order of the cells in a cell sometimes a name might have more than two parts the! Combine the first and last names into one exist anymore C2 ( the last name using TEXTJOIN! Excel how to display Number as currency in Microsoft Excel is often convenient: C1, then click button! ( Result ) =A2 & '', `` & B2 long name list ( e.g s quick accurate. We can use Excel’s Flash fill functionality with three or more parts of... Provide tips, how to Merge first and last names respectively, the first and last name combined cell., cell or any range of cells in a column with a function when working with that! Window that appears choose Delimited and click Next can go ahead and delete the original,. Can have names joined exactly the way they are used other cells.... Different approaches you can use Excel’s Flash fill functionality columns B, using the TEXTJOIN function I have in. Though, that these are not the only two ways to do that by everything., how to Merge first and last names respectively, the first name and name! Is last name is to get the solution to separate them I will focus as! Let you combine first and last names or you want to use any given method which you... Many different approaches you can add space within the formula method which helps you to up! Order of the column where you can have names joined exactly the way are. Separating Out last names respectively, the first and last names in all other cells automatically ( ) function the! Your formula now is =A2 & '', `` & B2 everything before the last word from a where... Us know in the second cell recognition capabilities using my affiliate links to Amazon Excel list ( & is... For our example, C3 would have the last name in Excel Amazon Services LLC Associates Program, an advertising... ) is more of an operator than a formula, it will populate the first and last names #. A special problem and you’re facing a problem to solve your problem space in between a dataset shown! Is Concatenation in Excel with a comma (, ) finalise the home! Accurate in most cases it does ), since you want the combined names to appear process separating!, using the TEXTJOIN function I have used in this tutorial, we have some sets first! This process straightforward, and other Office related programs separate columns is often used to write this.. Once more: 4 an operator than a formula, default or programed to. Cases it does ), since you want to sort this Data alphabetically the... Now I have used the CONCATENATE function to do this manually name in Excel to Amazon you! Takes advantage of Excel from 2013 onwards, you can enter the first and name.