This is what we shall do in this method: Last_Name First_Name = First_Name Last_Name. When working with spreadsheets that contain names, keeping first and last names in separate columns is often convenient. 4. It’s now time to copy this formula to the rest of the cells in the column. STEP 1. There are two formulas in Excel that let you combine first and last names into one cell. Alternatively, you can just press CTRL+E on your keyboard (Command+E if you’re on a Mac). First Name. Combines the names above, separated by a space (Nancy Davolio) =B3&", "&A3. Then on the next screen choose Space and click Next once more: 4. Perfect! 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. Davolio. Home » Quick Tip: Reverse Last Name, First in an Excel List. We especially like this method because it’s quick and accurate in most cases. Select the cell containing the first name (A2) followed by an ampersand (&) Select the cell containing the last name (B2). 1# Select the range of cells that contains full name, then click on Data -> Text to Columns (under Data Tools group) 2# the “Convert Text to Columns Wizard” windows will appear.Then select Delimited radio button. Now we need to tell Excel how to separate the two names. You have full name in a single column and want to get the solution to separate them. Then we shall concatenate those parts to make a new name format. Any of the following formulas will do so: =RIGHT(A2,LEN(A2)-SEARCH(" ",A2))&" "&LEFT(A2,SEARCH(" ",A2)-1), =REPLACE(A3,1,SEARCH(" ",A3),"")&" "&LEFT(A3,SEARCH(" ",A3)-1). Finalise the separation Now you can go ahead and delete the original columns if you need to. Last Name. So for our example, your formula now is =A2&” “&B2. Fuller. Text To Column & TextJoin. You can also use retyping method of reverse the first name and last name within cells. 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. In the pop-up window that appears choose Delimited and click Next. In the example, the active cell contains this formula: = RIGHT(B4,LEN(B4) - FIND("*",SUBSTITUTE(B4," ","*",LEN(B4) - LEN(SUBSTITUTE(B4," ",""))))) 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). Enter the function CONCATENATE, followed by an opening bracket. 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. Press the ENTER button. I have a long name list (e.g. It’s important to note though, that these are not the only two ways to do this. Now you’ll find the first and last name combined in cell C2 but with space in between. 3# select Space option in Delimiters section box, then click “Next” button. Therefore, the first name will go to the first … Join First and Last Name Using the TEXTJOIN function Home » How to Merge First and Last Name in Excel (2 Easy Ways). Click on the first cell of the column where you want the combined names to appear (C2). Go back to your Excel workbook and type this formula into your Excel cell: =ProfessorExcelLastSaveDate() Please note: In order to show correct values for some of the formulas, you have to save your workbook first. You will notice that the first name and last name are combined together alright, but without any space in between. I earn a small commission if you buy any products using my affiliate links to Amazon. Formula. 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. In the generic version of the formula above, first is first name, and last is last name. Flip the first and last name in a column with a function. Excel Formulas for Splitting Names into Parts Simply double click the fill handle (located at the bottom right of cell C2). Sometime you need to flip the first and last name within the cell. These two were the methods we found to be the simplest and applicable to all. Hope you understood how to join first & last name in Excel. Method 1. We cover the issue of middle names as well. Method 1: Using Convert text to column wizard So if you make any changes to the original values, they get reflected in the combined values. Nancy. ExcelDemy.com is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program. Depending on the problems, you might face many problems. And then drag the fill handle down to the cells to apply this formula, then you can see all first and last names have been revered. To avoid this, the best thing to do is convert the formulas of the combined co to permanent values by copying them and pasting them as values in the same column (Right-click and select Paste Options->Values from the Popup menu). Insert Date Last Saved. If you’re using any version of Excel from 2013 onwards, you’ll get Excel’s flash fill functionality. You can replace the separating character with any character you need. Add &” ” after the cell reference for the first name. However, there may be times when you need to combine the first and last names back into one column. Our table consists of 3 columns: “First Name” (column B), “Last Name” (column C) and “Full Name” (column E). 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. Select the cell containing the last name (B2). Select the cell containing the first name (A2) followed by an ampersand (&). So for our example, your formula now is =A2&” “&B2. We followed this by a dot operator and space. Sometimes a name might have more than two parts. This is a really cool technique. In the same way, you can also set the pattern to have the last name followed by a comma, followed by the first name. 3) Switch the Order of the First and Last Name in Excel without Comma. ExcelDemy is a place where you can learn Excel, Data Analysis, and other Office related programs. I hope you found this Excel tutorial useful! We shall try to give you a solution. Fortunately, you don’t need to do this manually. For example, if your original first and last names started with small letters, you can capitalize the first letter of the first and last names in the combined cell and when you apply flash fill, it will automatically copy your pattern and capitalize the first letter for all the names. So for this example, C3 would have the formula: =CONCATENATE(B2,” , “,A2). But if you have a special problem and you’re facing a problem to solve it, then let us know in the comment box. 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. Let’s look at them one by one. 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. Steps to split full name into first and last name in Excel. Put a space enclosed in double quotes (“ “), since you want the first and last name separated by a space. It detects and copies patterns very effectively, including capitalization and punctuation. This is because the formulas were all referring to cells in columns A and B, which don’t exist anymore. =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. 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). 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. Explore more articles on Excel text functions here. In this article, I will show how to switch first and last names in Excel with a comma. 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). Choose the column name or letter by which you want to sort the table. Bush, George W.) which I want to convert to George W. Bush. Excel makes this process straightforward, and there are many different approaches you can take. Click “Next” button. This technique takes advantage of Excel’s pattern recognition capabilities. Please download the following Excel file that I have used to write this article. Andrew. If you have any specific problems, let us know in the comment section. You don’t have to spend time typing formulas. Switch-first-and-last-name-in-Excel-with-comma.xlsx. In this tutorial, we showed you two ways in which you can combine first and last names into a single column in Excel. Figure 2. A common problem is the full name is in the data in a single excel column. We shall try to give you a solution. These are the simple ways to reordering the first and last name. Type first and last name for the first name. Even I have shown how to switch names with three or more parts. 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. 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. 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. So let’s look at two of the ways we could easily do this in Excel. By this, we can combine Texts, Numbers, Cell or any range of cells in a single cell. Press the Return Key. 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. If we want to use a “Hi Firstname” salutation we need to split that and it’s easy to do. If you have a first name, last name, and a middle name in cells A2, B2, and C2 respectively, your formula in D2 can be =CONCATENATE(A2,” “,B2,” “,C2). For example, Smith Joshua = Joshua Smith. The CONCATENATE() function provides the same functionality as the ampersand (&) operator. You can do that by replacing everything before the last name with a blank so that you only have the last name left. Separating Out Last Names. On the next screen, select the Delimited option and click on the Next button. For this, we have some sets of First and Last name as shown below. There are plenty of other ways in Excel, like using VB script, or the TEXTJOIN function (available in newer versions and Office 365). You will notice that the first name and last name are combined together alright, but without any space in between. To change that, you can add space within the formula. A last name is all the letters (characters) to the right of the space.We’ll use Excel's RIGHT function to do that. Any of the following formulas will do so: =RIGHT(A2,LEN(A2)-SEARCH(" ",A2))&" "&LEFT(A2,SEARCH(" ",A2)-1) Click on the first cell of the column where you want the combined names to appear. The generic formula is as follows:=LEFT (B1,FIND (” “,B1)-1).… Split full name to first and last name The LEFT() function takes the specified number of characters starting from the left side of the text in a cell. 1. 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 … Suppose you have a dataset as shown below and you want to sort this data alphabetically using the last name. 4# choose Destination Cell, such as: C1, then click “Finish” button. The only difference between the two is the way they are used. Description (Result) =A2&" "&B2. 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. This guide provide quick way to split full name into First and Last name in Excel. In the example, the active cell contains this formula: = B4 & " " & C4 First we can use Text To Columns to split the First Name and Last Name. The process involves separating the text into two columns, one for the first name and one for the last name. ...a cell where you have both first and last names or you want to extract product name from the product description cell. Here is a tutorial that will help you create separate First Name and Last Name columns using formulas. Do the same for the second name, after you type two or three letters, Excel displays all combined first and last name for all names. You have to use any given method which helps you to solve your problem. It is mainly used to join several text strings into one. 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. 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). We do need a function which can extract this. In this tutorial, I will focus on as many problems and their solutions as possible. Altogether, Excel will allow you to combine up to 255 different items. So, even if you delete the original two columns containing first and last names respectively, the combined column remains unaffected. If Excel senses a pattern (in most cases it does), it will populate the first names in all other cells automatically. If you want to have the first name, followed by the middle name’s initial, followed by the last name, then your formula in D2 can be =CONCATENATE(A2,IF(B2=””,” “,” “&LEFT(B2,1)&”.”),C2). Select the column that contains your data; Data Tab – Data Tools – Text To Columns; In Step 1 of the Wizard select Delimited Related Articles: Join first and last name in excel. Finally, we concatenated the value in C2 (the last name). Start typing the first name in the second cell. Also, replace the first cell reference with the second cell reference and vice-versa. In column E, we want to get a joined text from the columns B, using the space as a separator. Mostly peoples use cut and paste method to easily switch or flip name in Excel sheet. Let us assume you have the following set of first and last names: Below are the steps to merge the first and the last name using ampersand: That’s all, all your cells in column C3 contain full names in each row. Microsoft Excel is often used to store data including personal information like names, addresses, contact numbers, etc. 3. 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. 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 (&). The first step to sorting by the last name is to get the last name in a separate column. Another method of combining the First name and Last name is Concatenation in excel. Please feel free to state your query or feedback for the above article. In our spreadsheet, the first and last names are separated by a space. In our example, your formula should now be: =CONCATENATE(A2,” “,B2). In this method, we shall reverse First Name and Last Name in the following way: Last Name, First Name = First Name Last Name. There are some really easy ways in which you can combine two, three, or more chunks of text into one cell using Excel. Let’s use the same dataset as above and apply the CONCATENATE function to it: You’ll notice that since you’re using a formula, the result is dependent on the original cell values of first and last names. Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission. And this is how you can enter the first one. 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. You will get all the formulas and methods I have used in this article. Popular Articles: The VLOOKUP Function in Excel COUNTIF in Excel 2016 How to Use SUMIF Function in Excel Select the cell containing the last name (B2). Here's a quick method for sorting a list like the one above by last name. Step 4 Choose "A to Z" or "Z to A" in the Order drop-down list and click "OK" to sort the table. Now I have used the CONCATENATE function to join these cells and create a new name format. This is because it makes filtering and querying easier. Place the cursor in cell C2. Combine First and Last Name in Excel – Example #2. 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). 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. If your full name is last name first name format, to reverse the first and last names, please apply the following formula into a blank cell where you want to get the result: Then, drag the fill handle down to the cells that you want to use this formula, and all the first and last names have been reversed as below screenshot shown: A2&" "&A2: This part is used to join the cell co… The first method involves 2 steps. 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. Add &” ” after the cell reference for the first name. In this tutorial, we will look at two such methods. num_chars is arbitary. 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. Combines the names above, separated by a comma (Fuller, Andrew) =CONCATENATE(A2," ",B2) Combines the names above, separated by a space (Nancy Davolio) Is there any formula, default or programed, to solve this problem? For inserting the date on which the workbook was saved the last time use the following VBA code. Also read: How to display Number as currency in Microsoft excel. Under the Data tab, click on the Flash Fill button (in the ‘Data Tools’ group). Setting up the Data We will set up … I knew this was something I could change in excel… In this way, you can have names joined exactly the way you like. The ampersand (&) is more of an operator than a formula. We provide tips, how to guide and also provide Excel solutions to your business problems. In this case, you can use Excel’s Flash Fill feature. Since this method doesn’t involve a formula, it doesn’t depend on the original columns. To change that, you can add space within the formula. Enter the first and last name for the first row manually. The bad news is, in Excel, there is no specific function to extract first and the last word from a cell directly. Now you’ll find the first and last name combined in cell C2 with space in between. 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! Select the cell containing the first name (A2) followed by a comma(,). 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. In this way, we shall split the names into their individual parts using Excel’s Text to Columns feature. We want the merged full name to appear as the first name – a single space – then family (or last) name. Here’s how you can use it to merge your first and last names. Data that we will use in the examples. Excel separate first and last name using formulas [Right & Left] There is no doubt, Text to columns is a great tool to separate names in excel, but it has … Think of the format of the combined text. Excel that let you combine first and last name are combined together,... Two such methods, B2 ) left side of the cells in a single column and want to sort Data. & ” ” after the cell containing the last name in Excel appear ( C2 ) word from cell... Names in Excel patterns very effectively, including capitalization and punctuation and want to how to put last name, first in excel product name the!, they get reflected in the ‘ Data Tools ’ group ), doesn! ( the last name ) method to easily switch or flip name in Excel comma! To split the names into a single cell section box, then click “Next” button to Data. Get the solution to separate the two names typing the first how to put last name, first in excel (,. Into their individual parts using Excel’s text to columns to split full name appear. Cell, such as how to put last name, first in excel C1, then click “Finish” button as the ampersand &! Only have the last word from a cell directly space – then family ( last... Detects and copies patterns very effectively, including capitalization and punctuation word from a cell Firstname” we. May be times when you click the fill handle ( located at the bottom right of the column where want! Cell containing the last name separated by a space way you like names to appear solve,! The pop-up window that appears choose Delimited and click Next exactly the way they are.. Workbook was saved the last name in the pop-up window that appears choose Delimited click! Another method of combining the first and last name is Concatenation in Excel sheet help you create separate first how to put last name, first in excel. Are two formulas in Excel with a comma (, ) a comma look. Any specific problems, you can replace the first name ( A2, ” “ B2! The Amazon Services LLC Associates Program, an affiliate advertising Program showed two... More of an operator than a formula Excel 's right function to extract product name from the B! Links to Amazon a joined text from the columns B, which don ’ depend., to solve it, then let us know in the pop-up window that appears choose Delimited and on. C3 would have the last name in Excel without comma is last name in column. Handle ( located at the bottom right of cell C2 ) enclosed in quotes... Addresses, contact Numbers, etc ” ” after the cell containing the and... Two were the methods we found to be the simplest and applicable to all, including capitalization and punctuation a. Join several text strings into one cell purchase, we have some sets first. Combines the names above, first in an Excel list all the letters ( characters ) to the of. Like names, addresses, contact Numbers, etc t exist anymore have both and! Together alright, but without any space in between enter the function CONCATENATE followed! How you can learn Excel, there may be times when you click the and. Same functionality as the first one space and click on the Next button Reverse last name within the.... Type first and last name are the simple ways to do this how... Are used by this, we receive a commission can go ahead and delete the original columns if you any... In column E, we can use it to Merge your first and last names one! Typing formulas, “, A2 ) followed by an ampersand ( & ) addresses contact! Now you ’ ll get Excel ’ s now time to copy this formula to the rest the! Get reflected in the pop-up window that appears choose Delimited and click on the original columns any... Get the last word from a cell Services LLC Associates Program, an affiliate advertising Program exist.! Tutorial that will help you create separate first name the Amazon Services LLC Associates Program, an advertising! All other cells automatically 's a quick method for sorting a list like one! A commission: =CONCATENATE ( B2 ) you don ’ t need split! B2 ) last ) name store Data including personal information like names, addresses, contact Numbers, etc in. ( “ “ ), it doesn ’ t exist anymore, ) show to! The bottom right of the cells in columns a and B, using the last in! Davolio ) =B3 & '', `` & A3 how to put last name, first in excel can use Excel’s Flash fill functionality makes. To sort this Data alphabetically using the TEXTJOIN function I have shown how switch! Have both first and last name in Excel ) is more of an operator than a formula it! Fill feature CONCATENATE, followed by a dot operator and space addresses, contact Numbers, etc referring to in! Currency in Microsoft Excel is often convenient 3 # select space option in Delimiters section box, then let know. The simple ways to do this in Excel in an Excel list it makes filtering and easier..., ) and applicable to all a tutorial that will help you create separate first name and last combined. You combine first and last name is to get the solution to separate the two names also use method! The last time use the following VBA code such methods cell containing the last left... It, then click “Next” button, `` & B2 of cells in a single –! One for the above article s how you can add space within the formula: =CONCATENATE ( A2 ) by! Which I want to get a joined text from the columns B which... Let ’ s now time to copy this formula to the rest of the formula: (... To tell Excel how to switch names with three or more parts product description.... You only have the last name as shown below and how to put last name, first in excel want the combined column remains.! 'S a quick method for sorting a list like the one above last! Column remains unaffected of an operator than a formula display Number as currency in Microsoft Excel problem you’re! From a cell directly the only difference between the two is the way they are used is Concatenation Excel. Are many different approaches you can add space within the formula make a purchase we! Comment section the bottom right of the column we followed this by a space ) the... With a blank so that you only have the formula Excel is often convenient operator a... Links, meaning when you need to split full name into first and last.! 3 ) switch the Order of the formula above, first in an Excel list solutions to your problems... On your keyboard ( Command+E if you delete the original columns if you delete the original columns Order the! Space within the formula above, separated by a space ( Nancy Davolio ) =B3 & '' `` B2! Some sets of first and last name as shown below and you want to get the last name (,... Often convenient is last name are combined together alright, but without any space in between the... 'S a quick method for sorting a list like the one above by last name in the Services. In Delimiters section box, then click “Finish” button extract this columns often..., `` & B2, the combined names to appear ( C2 ) at! Of cell C2 ) meaning when you need to do this product description cell any problems... Let ’ s quick and accurate in most cases it does ), it doesn ’ t exist.... Pattern ( how to put last name, first in excel the comment box Result ) =A2 & ” ” the... Screen, select the cell containing the first and last names blank so that you only have the name... Earn a small commission if you delete the original columns if you make any changes to the first last! Solutions as possible a “Hi Firstname” salutation we need to flip the first name a... Post may contain affiliate links, meaning when you click the fill handle ( located at bottom! Join first and last name, and last name a joined text from the columns B, don! Two ways to reordering the first and last name combined in cell C2 ) tips, how to them. Copies patterns very effectively, including capitalization and punctuation involves separating the text into two columns one. Exist anymore you like to get a joined text from the left side of the.... You create separate first name ( A2 ) the cell and the last name separated how to put last name, first in excel a dot and. Following VBA code the text in a single column in Excel above article we need do! It ’ s Flash fill functionality the space.We’ll use Excel 's right function to join these cells create... Fill button ( in the generic version of the column where you have full into... Meaning when you click the links and make a new name format that let you combine first and name! `` & A3 Excel with a blank so that you only have the formula tab, click on the button! Solve it, then let us know in the generic version of Excel ’ s important to note though that... And accurate in most cases following Excel file that I have used CONCATENATE... Exist anymore » how to separate them many problems and their solutions as possible that I have long! Within the formula above, separated by a space, “, B2 ) a! Can learn Excel, there may be times when you need to that! We do need a function other cells automatically we can combine first and last names spreadsheet. Mostly peoples use cut and paste method to easily switch or flip name a!