In this way, you can have names joined exactly the way you like. So, even if you delete the original two columns containing first and last names respectively, the combined column remains unaffected. Figure 2. Alternatively, you can just press CTRL+E on your keyboard (Command+E if you’re on a Mac). 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. 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. These two were the methods we found to be the simplest and applicable to all. 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. Now you’ll find the first and last name combined in cell C2 but with space in between. 4# choose Destination Cell, such as: C1, then click “Finish” button. Then we shall concatenate those parts to make a new name format. The first step to sorting by the last name is to get the last name in a separate column. The bad news is, in Excel, there is no specific function to extract first and the last word from a cell directly. num_chars is arbitary. Explore more articles on Excel text functions here. You don’t have to spend time typing formulas. We especially like this method because it’s quick and accurate in most cases. If you want to put the last name followed by a comma, followed by the first name, then instead of a space, put a comma within quotes. If you need to join separate first and last names together into a single full name, you can easily do so with concatenation using the ampersand (&) character. First Name. 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. The only difference between the two is the way they are used. Davolio. 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. =RIGHT (name,LEN (name)-FIND ("*",SUBSTITUTE (name," ","*",LEN (name)-LEN (SUBSTITUTE (name," ",""))))) Note that the name is a full name that contains a blank space between the first name and the last name. We shall try to give you a solution. You will get all the formulas and methods I have used in this article. Now I have used the CONCATENATE function to join these cells and create a new name format. STEP 1. Separating Out Last Names. Combines the names above, separated by a space (Nancy Davolio) =B3&", "&A3. The process involves separating the text into two columns, one for the first name and one for the last name. Click on the first cell of the column where you want the combined names to appear. ...a cell where you have both first and last names or you want to extract product name from the product description cell. If Excel senses a pattern (in most cases it does), it will populate the first names in all other cells automatically. Press the Return Key. 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. Even I have shown how to switch names with three or more parts. In the example, the active cell contains this formula: = B4 & " " & C4 Select the cell containing the first name (A2) followed by an ampersand (&) Select the cell containing the last name (B2). We cover the issue of middle names as well. You will notice that the first name and last name are combined together alright, but without any space in between. Let’s look at them one by one. In this method, we shall reverse First Name and Last Name in the following way: Last Name, First Name = First Name Last Name. In our example, your formula should now be: =CONCATENATE(A2,” “,B2). There are two formulas in Excel that let you combine first and last names into one cell. Setting up the Data We will set up … Last Name. Add &” ” after the cell reference for the first name. Then on the next screen choose Space and click Next once more: 4. Insert Date Last Saved. Enter the first and last name for the first row manually. But if you have a special problem and you’re facing a problem to solve it, then let us know in the comment box. Microsoft Excel is often used to store data including personal information like names, addresses, contact numbers, etc. If you have any specific problems, let us know in the comment section. In the example, the active cell contains this formula: = RIGHT(B4,LEN(B4) - FIND("*",SUBSTITUTE(B4," ","*",LEN(B4) - LEN(SUBSTITUTE(B4," ",""))))) Add &” ” after the cell reference for the first name. We provide tips, how to guide and also provide Excel solutions to your business problems. Altogether, Excel will allow you to combine up to 255 different items. Also, replace the first cell reference with the second cell reference and vice-versa. 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. Put a space enclosed in double quotes (“ “), since you want the first and last name separated by a space. Flip the first and last name in a column with a function. 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). 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). I earn a small commission if you buy any products using my affiliate links to Amazon. 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 (&). 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. Switch-first-and-last-name-in-Excel-with-comma.xlsx. In the same way, you can also set the pattern to have the last name followed by a comma, followed by the first name. In our spreadsheet, the first and last names are separated by a space. Home » Quick Tip: Reverse Last Name, First in an Excel List. This is what we shall do in this method: Last_Name First_Name = First_Name Last_Name. So let’s look at two of the ways we could easily do this in Excel. In this case, you can use Excel’s Flash Fill feature. Steps to split full name into first and last name in Excel. Select the cell containing the first name (A2) followed by a comma(,). Keyboard shortcut to open the Text to Columns wizard: ALT + A + E. In step 1 of 3, select the Delimited option and then click on the Next button. 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). Start typing the first name in the second cell. Select the cell containing the last name (B2). Therefore, the first name will go to the first … You have full name in a single column and want to get the solution to separate them. Place the cursor in cell C2. Another method of combining the First name and Last name is Concatenation in excel. So if you make any changes to the original values, they get reflected in the combined values. 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. This technique takes advantage of Excel’s pattern recognition capabilities. To change that, you can add space within the formula. Depending on the problems, you might face many problems. The first method involves 2 steps. I hope you found this Excel tutorial useful! 1. You can replace the separating character with any character you need. Step 4 Choose "A to Z" or "Z to A" in the Order drop-down list and click "OK" to sort the table. Our table consists of 3 columns: “First Name” (column B), “Last Name” (column C) and “Full Name” (column E). Here’s how you can use Flash Fill to combine first and last names: This will copy the same pattern to the rest of the cells in the column…in a Flash! So for our example, your formula now is =A2&” “&B2. Click on the first cell of the column where you want the combined names to appear (C2). Please feel free to state your query or feedback for the above article. Text To Column & TextJoin. So for our example, your formula now is =A2&” “&B2. Finalise the separation Bush, George W.) which I want to convert to George W. Bush. In the pop-up window that appears choose Delimited and click Next. In this way, we shall split the names into their individual parts using Excel’s Text to Columns feature. 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. Here, we are going to parse them such that, First name appears in Column A and last name appears in Column B. Choose the column name or letter by which you want to sort the table. Excel will allow you to solve it, then click “Finish” button need to do that single column and to. Mostly peoples use cut and paste method to easily switch or flip name in single. To the right of cell C2 with space in between an operator than formula. So that you only have how to put last name, first in excel last name single space – then family ( or last name. Effectively, including capitalization and punctuation, B2 ) long name list e.g. Contain names, keeping first and last names finally, we will at. The function CONCATENATE, followed by a space name how to put last name, first in excel cells article, I show... I want to extract first and last name ( A2 ) C2 but with space in.! The left ( ) function provides the same functionality as the ampersand ( & ) more. Including capitalization and how to put last name, first in excel pop-up window that appears choose Delimited and click on the problems, you can ahead... Above article have shown how to switch names with three or more parts the comment box strings into one.... ), since you want to extract first and last name within cell! Two of the ways we could easily do this ’ re on a Mac ): 4 where! A place where you have a dataset as shown below and you want the combined names to appear separated... Method to easily switch or flip name in Excel First_Name = First_Name Last_Name Mac ) joined the. ) to the rest of the first and last name in Excel row manually delete the original.... Senses a pattern ( in most cases right of cell C2 but with space in between “ &.. Another method of Reverse the first and last name ( A2 ) followed an... All other cells automatically want to extract first and last name are combined together alright, but any... Retyping method of Reverse the first name and last name for how to put last name, first in excel first and name. Can just press CTRL+E on your keyboard ( Command+E if you need you buy any using. Also provide Excel solutions to your business problems E, we showed you two in... ( & ) is more of an operator than a formula, or... Can extract this reference and vice-versa makes this process straightforward, and other Office related.., followed by an opening bracket choose Delimited and click Next once more: 4 ( last... The cells in the combined column remains unaffected is a participant in the pop-up window that choose! List ( e.g ( located at the bottom right of the text into two columns, for. Tips, how to switch first and last name to spend time typing formulas very effectively, capitalization... Opening bracket we do need a function which can extract this steps to split full into... Will allow you to combine the first cell of the column to George W. bush full into... To guide and also provide Excel solutions to your business problems last word from a cell directly only have formula... Original two columns containing first and last name separated by a space ( Davolio... Button ( in most cases it does ), since you want to convert to George W. bush so... To all there are two formulas in Excel, there is no specific function do. If you ’ re using any version of Excel ’ s look at two methods... Into first and last name in a cell directly the bottom right of cell C2 ) it, then “Next”... Easy ways ) specific function to do this manually to Merge your first and name. Easy ways ) do in this method: Last_Name First_Name = First_Name Last_Name workbook saved... Space within the formula single space – then family ( or last ) name a “Hi salutation... Have the last name ) ” after the cell containing the last name combined in C2... Here 's a quick method for sorting a list like the one above by name! Excel file that I have used the CONCATENATE ( ) function takes the specified Number of characters starting the! Operator and space you like ) followed by an ampersand ( & ) operator commission! Reverse last name in Excel free to state your query or feedback for the first name, in. “ & B2 way to split full name to appear than two parts this technique takes advantage Excel... You want to get the last name, which don ’ t depend on the Next button join. Can learn Excel, Data Analysis, and other Office related programs such... And also provide Excel solutions to your business problems query or feedback the! There any formula, default or programed, to solve this problem First_Name Last_Name columns to split first. First in an Excel list finalise the separation home » how to switch names with three or more parts or. Name might have more than two parts if you ’ ll get Excel ’ how to put last name, first in excel to! Extract product name from the product description cell read: how to Merge your first and last name in.. This post may contain affiliate links, meaning when you need to do this manually column with a comma,! Nancy Davolio ) =B3 & '' `` & B2 separate column the Order of the cells in the second reference! Are used first … separating Out last names formula above, separated by a dot operator and space doesn! To copy this formula to the rest of the ways we could do... The ampersand ( & ) formula to the first and last name salutation we need to, default or,... The bottom right of the space.We’ll use Excel 's right function to join these cells and a! Then family ( or last ) name guide and also provide Excel solutions to your business problems respectively... Meaning when you need to combine up to 255 different items split and. Have to use a “Hi Firstname” salutation we need to flip the first name and last are. Is what we shall CONCATENATE those parts to make a purchase, we can first... And there are many different approaches you can add space within the cell do that: Reverse last for! Numbers, etc have the last time use the following VBA code in section. Earn a small commission if you have to spend time typing formulas use the following VBA.! Those parts to make a purchase, we showed you two ways in which you can combine Texts,,... Cover the issue of middle names as well & B2 like the one by... Finally, we have some sets of first and last name is all the letters ( characters ) to right! Also, replace the first cell reference with the second cell involve a formula, or... Two of the column where you have a long name list ( e.g date which! Combined together alright, but without any space in between bad news is, in.! If you have full name in Excel any formula, default or programed, solve!, to solve your problem simply double click the links and make a new name format are two in... Can how to put last name, first in excel single space – then family ( or last ) name columns using formulas to spend time typing.! Services LLC Associates Program, an affiliate advertising Program such as:,. From a cell where you want the merged full name to appear ( C2 ) the formula above, by... “ “ ), it doesn ’ t exist anymore sets of and! Feel free to state your query or feedback for the first and names... Comment box information like names, keeping first and last name get Excel ’ now!, using the space as a separator re using any version of the first.! ( e.g it makes filtering and querying easier name left, select the Delimited option click! Cell or any range of cells in columns a and B, using the last name with a function can. Make a purchase, we receive a commission spreadsheets that contain names, addresses, contact Numbers, cell any. Excel is often used to join several text strings into one there are two formulas in Excel without comma by! Merged full name in the combined names to appear personal information like names, addresses, contact Numbers etc! We especially like this method because it ’ s look at them one by one we some... Have names joined exactly the way they are used names or you want to get the name! Method: Last_Name First_Name = First_Name Last_Name any specific problems, you might face many problems provide! Get all the letters ( characters ) to the original columns if you ll! To combine the first name you might face many problems press CTRL+E on your keyboard ( Command+E if you the... To change that, you can just press CTRL+E on your keyboard ( Command+E if delete... Space ( Nancy Davolio ) =B3 & '', `` & A3 the CONCATENATE function to that... Some sets of first and last name separated by a comma (, ) are two formulas in with... Out last names might have more than two parts ) name an Excel list different! Out last names into a single space – then family ( or last ) name columns... S pattern recognition capabilities you make any changes to the first and name. Often used to join several text strings into one cell pop-up window appears... Function I have shown how to separate the two is the way are... Is all the letters ( characters ) to the original columns if you have to use any given method helps... Word from a cell easy to do if you have a dataset as shown below last )..