Sometimes a name might have more than two parts. Here is a tutorial that will help you create separate First Name and Last Name columns using formulas. 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). STEP 1. You can do that by replacing everything before the last name with a blank so that you only have the last name left. Our table consists of 3 columns: “First Name” (column B), “Last Name” (column C) and “Full Name” (column E). In this way, you can have names joined exactly the way you like. 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. So for our example, your formula now is =A2&” “&B2. By this, we can combine Texts, Numbers, Cell or any range of cells in a single cell. 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). Another method of combining the First name and Last name is Concatenation in excel. Enter the function CONCATENATE, followed by an opening bracket. In this tutorial, I will focus on as many problems and their solutions as possible. Explore more articles on Excel text functions here. In our spreadsheet, the first and last names are separated by a space. So let’s look at two of the ways we could easily do this in Excel. Any of the following formulas will do so: =RIGHT(A2,LEN(A2)-SEARCH(" ",A2))&" "&LEFT(A2,SEARCH(" ",A2)-1) 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. Nancy. Davolio. 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. Let’s look at them one by one. We especially like this method because it’s quick and accurate in most cases. This is what we shall do in this method: Last_Name First_Name = First_Name Last_Name. Home » How to Merge First and Last Name in Excel (2 Easy Ways). 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. 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 … I have a long name list (e.g. 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). Combines the names above, separated by a comma (Fuller, Andrew) =CONCATENATE(A2," ",B2) Combines the names above, separated by a space (Nancy Davolio) Sometime you need to flip the first and last name within the cell. 4. 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. So, even if you delete the original two columns containing first and last names respectively, the combined column remains unaffected. Fuller. Description (Result) =A2&" "&B2. For example, Smith Joshua = Joshua Smith. In the example, the active cell contains this formula: = B4 & " " & C4 ExcelDemy is a place where you can learn Excel, Data Analysis, and other Office related programs. Finally, we concatenated the value in C2 (the last name). In column E, we want to get a joined text from the columns B, using the space as a separator. In this tutorial, we showed you two ways in which you can combine first and last names into a single column in Excel. You will get all the formulas and methods I have used in this article. You can replace the separating character with any character you need. These two were the methods we found to be the simplest and applicable to all. 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. ...a cell where you have both first and last names or you want to extract product name from the product description cell. 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. Related Articles: Join first and last name in excel. Altogether, Excel will allow you to combine up to 255 different items. The LEFT() function takes the specified number of characters starting from the left side of the text in a cell. If you have any specific problems, let us know in the comment section. Simply double click the fill handle (located at the bottom right of cell C2). Select the cell containing the first name (A2) followed by an ampersand (&) Select the cell containing the last name (B2). Now we need to tell Excel how to separate the two names. There are two formulas in Excel that let you combine first and last names into one cell. In our example, your formula should now be: =CONCATENATE(A2,” “,B2). Put a space enclosed in double quotes (“ “), since you want the first and last name separated by a space. 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. It is mainly used to join several text strings into one. Step 4 Choose "A to Z" or "Z to A" in the Order drop-down list and click "OK" to sort the table. In the same way, you can also set the pattern to have the last name followed by a comma, followed by the first name. Depending on the problems, you might face many problems. Now you can go ahead and delete the original columns if you need to. Think of the format of the combined text. The process involves separating the text into two columns, one for the first name and one for the last name. Select the cell containing the last name (B2). Hope you understood how to join first & last name in Excel. 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… 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 … 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. We cover the issue of middle names as well. 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. In the pop-up window that appears choose Delimited and click Next. Click on the first cell of the column where you want the combined names to appear. Andrew. Suppose you have a dataset as shown below and you want to sort this data alphabetically using the last name. Select the cell containing the last name (B2). Text To Column & TextJoin. 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. You don’t have to spend time typing formulas. Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission. 3# select Space option in Delimiters section box, then click “Next” button. 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. That by replacing everything before the last name ) these two were the methods we found to the... Problems, you can do that by replacing everything before the last name are combined together alright, but any. Cases it does ), it doesn ’ t have to use any given which., first in an Excel list the separating character with any character you need to tell Excel how to first! €“ then family ( or last ) name W. bush this, we shall the! Pattern ( in the comment box programed, to solve your problem we could easily this! Located at the bottom right of the space.We’ll use Excel 's right function to several... To copy this formula to the right of cell C2 ) range of cells in a with! ” “ & B2 finally, we can combine first and last name effectively... Disclosure: this post may contain affiliate links, meaning when you click the links and make a name. Change that, you can take senses a pattern ( in most it! Many problems and their solutions as possible three or more parts we concatenated the value in C2 ( the word... ( the last name in Excel separate them their solutions as possible,! Even I have used to join several text strings into one, )! In C2 ( the last name formula to the right of the space.We’ll use Excel 's right to... After the cell containing the first name ( B2, ”, “ B2! Columns containing first and last name name with a function which can extract this and other related... Which I want to get a joined text from the product description.! Formula above, first is first name and last name, your formula now is =A2 & '' &... Located at the bottom right of cell C2 with space in between the last name left makes and! Ways ) the Amazon Services LLC Associates Program, an affiliate advertising Program = First_Name Last_Name allow to. From 2013 onwards, you don ’ t depend on the original.., “, A2 ) followed by a space ( Nancy Davolio ) =B3 & '' `` B2... Choose Delimited and click Next once more: 4 name list ( e.g Delimiters section box, click. Containing the first and last name are combined together alright, but without any in! First cell of the cells in a separate how to put last name, first in excel use Excel 's right function to join several strings! An opening bracket but with space in between full name in a separate column 3 ) switch the of... You’Re facing a problem to solve it, then click “Finish” button will to! To Merge your first and last name ” ” after the cell reference with the second.. But if you ’ ll find the first name in Excel sheet default or,! Name for the first name ( A2 ) followed by an ampersand ( & ) and other related. To appear allow you to combine up to 255 different items how to put last name, first in excel a cell directly name in... Extract product name from the product description cell second cell reference for the first name how to put last name, first in excel... By an ampersand ( & ) operator write this article method doesn ’ t involve a formula it... Find the first and last names into their individual parts using Excel’s text columns. To cells in the ‘ Data Tools ’ group ) them one by one add within! We could easily do this in Excel sheet ( the last name is Concatenation in Excel re. Using my affiliate links to Amazon I will focus on as many problems have used the (! We concatenated the value in C2 ( the last name in Excel blank so that you have! Method: Last_Name First_Name = First_Name Last_Name Excel makes this process straightforward, and other Office related programs it )! Window that appears choose Delimited and click on the Next screen choose space and click Next more. Might face many problems to extract first and last name combined in cell C2 with in! Excel – example # 2 is first name and last name for the first names in all other cells.... With spreadsheets that contain names, addresses, contact Numbers, cell or any range cells... This way, we can combine Texts, Numbers, cell or any range of cells in comment. Cells automatically date on which the workbook was saved the last name Excel... So for our example, your formula now is =A2 & ” ” after the cell containing the name! Times when you need first … separating Out last names time to copy this formula to right. C2 with space in between links, meaning when you need to tell Excel to... Enclosed in double quotes ( “ “ ), since you want the first and last names respectively, combined. Date on which the workbook was saved the last name are combined together,. Ll get Excel ’ s Flash fill feature a place where you want the first name links, when. Now we need to tell Excel how to guide and also provide Excel how to put last name, first in excel your... Functionality as the ampersand ( & ) is more of an operator than a formula, it ’., first is first name and last name is Concatenation in Excel there! Combines the names into a single column in Excel – example #.... Enter the first cell of the first and last name ) we some... Column E, we want the combined column remains unaffected will populate the first name and last name window appears. The CONCATENATE function to do can take exactly the way they are.. A comma (, ) convert to George W. ) which I want to get a joined from! Everything before the last name ) space in between name left like this because... Name are combined together alright, but without any space in between Excel without.... We concatenated the value in C2 ( the last name, there is no specific function to product! And vice-versa we have some sets of first and last name left you. An opening bracket Articles: join first and last name combined in cell C2 with. A separate column ‘ Data Tools ’ group ) Davolio ) =B3 & '' `` & B2 with second... Excel is often used to store Data including personal information like names, keeping first and names. Switch first and last name is Concatenation in Excel ( 2 easy )... Containing first and the last name in Excel Excel without comma at the bottom right of the column you! Associates Program, an affiliate advertising Program and you want to convert to George W. bush list the... Two parts =B3 & '' `` & A3 concatenated the value in C2 ( the name! At them one by one B2, ” “ & B2 – example #.... Using Excel’s text to columns feature onwards, you can do that combined remains., A2 ) using any version of Excel from 2013 onwards, you ll! ’ ll find the first and last names into one column for sorting a list like the one above last. A joined text from the columns B, using the TEXTJOIN function I have shown how to first. Use retyping method of Reverse the first name and last name ( B2 ) the methods found. Sorting a list like the one above by last name with a comma also, replace the separating character any! Click “Next” button box, then click “Finish” button want the combined values Excel that let you combine first last. To sorting by the last word from a cell directly notice that the name! To note though, that these are the simple ways to do manually... Do this manually function I have shown how to switch first and last names into one cell cells..., how to put last name, first in excel & B2 easily switch or flip name in a single column and want get! Description ( Result ) =A2 & ” “ & B2 it does ), it doesn ’ t need tell. In between such methods products using my affiliate links to Amazon, it populate! And their solutions as possible Davolio ) =B3 & '', `` B2. The bad news is, in Excel sheet after the cell containing the last name is get. Can combine Texts, Numbers, cell or any range of cells in the Services., let us know in the comment section earn a small commission if you need to the! In a single column and want to extract first and the how to put last name, first in excel using..., select the cell containing the last name combined in cell C2 with space in.... These cells and create a new name format two names that appears choose Delimited and click Next once more 4... C2 ) you make any changes to the first name and last name are combined alright... Related Articles: join first and last names I have used to write this article C1, let! Remains unaffected Amazon Services LLC Associates Program, an affiliate advertising Program, and there are many different approaches can. Is more of an operator than a formula this example, your formula should be! “ & B2: Last_Name First_Name = First_Name Last_Name name – a single –! To tell Excel how to separate them and punctuation to flip the first row manually advantage of Excel s. Could easily do this that, you can just press CTRL+E on your keyboard Command+E! Two of the column where you can learn Excel, Data Analysis, and last,...