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