In the same way, you can also set the pattern to have the last name followed by a comma, followed by the first name. When working with spreadsheets that contain names, keeping first and last names in separate columns is often convenient. Now you can go ahead and delete the original columns if you need to. 3) Switch the Order of the First and Last Name in Excel without Comma. Home » How to Merge First and Last Name in Excel (2 Easy Ways). 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. 4. 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 (&). A last name is all the letters (characters) to the right of the space.We’ll use Excel's RIGHT function to do that. Setting up the Data We will set up … Alternatively, you can just press CTRL+E on your keyboard (Command+E if you’re on a Mac). Microsoft Excel is often used to store data including personal information like names, addresses, contact numbers, etc. Under the Data tab, click on the Flash Fill button (in the ‘Data Tools’ group). Finally, we concatenated the value in C2 (the last name). Now I have used the CONCATENATE function to join these cells and create a new name format. In the example, the active cell contains this formula: = RIGHT(B4,LEN(B4) - FIND("*",SUBSTITUTE(B4," ","*",LEN(B4) - LEN(SUBSTITUTE(B4," ",""))))) 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 this tutorial, I will focus on as many problems and their solutions as possible. In this way, you can have names joined exactly the way you like. Bush, George W.) which I want to convert to George W. Bush. num_chars is arbitary. Davolio. Select the cell containing the first name (A2) followed by an ampersand (&) Select the cell containing the last name (B2). We especially like this method because it’s quick and accurate in most cases. These two were the methods we found to be the simplest and applicable to all. Switch-first-and-last-name-in-Excel-with-comma.xlsx. You will notice that the first name and last name are combined together alright, but without any space in between. 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). Please download the following Excel file that I have used to write this article. Click on the first cell of the column where you want the combined names to appear (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). The CONCATENATE() function provides the same functionality as the ampersand (&) operator. Here’s how you can use it to merge your first and last names. 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. The first method involves 2 steps. This technique takes advantage of Excel’s pattern recognition capabilities. Hope you understood how to join first & last name in Excel. Click on the first cell of the column where you want the combined names to appear. Last Name. Then we shall concatenate those parts to make a new name format. You can replace the separating character with any character you need. Think of the format of the combined text. 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. 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). So, even if you delete the original two columns containing first and last names respectively, the combined column remains unaffected. Steps to split full name into first and last name in Excel. Popular Articles: The VLOOKUP Function in Excel COUNTIF in Excel 2016 How to Use SUMIF Function in Excel Place the cursor in cell C2. I knew this was something I could change in excel… Excel Formulas for Splitting Names into Parts In this case, you can use Excel’s Flash Fill feature. 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. You don’t have to spend time typing formulas. 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). In the example, the active cell contains this formula: = B4 & " " & C4 Suppose you have a dataset as shown below and you want to sort this data alphabetically using the last name. Andrew. However, there may be times when you need to combine the first and last names back into one column. First Name. 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 (&). 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). 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. Since this method doesn’t involve a formula, it doesn’t depend on the original columns. 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. 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. Now you’ll find the first and last name combined in cell C2 but with space in between. For example, Smith Joshua = Joshua Smith. 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. Join First and Last Name Using the TEXTJOIN function Related Articles: Join first and last name in excel. Altogether, Excel will allow you to combine up to 255 different items. We provide tips, how to guide and also provide Excel solutions to your business problems. Any of the following formulas will do so: =RIGHT(A2,LEN(A2)-SEARCH(" ",A2))&" "&LEFT(A2,SEARCH(" ",A2)-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 … It’s important to note though, that these are not the only two ways to do this. Add &” ” after the cell reference for the first name. The generic formula is as follows:=LEFT (B1,FIND (” “,B1)-1).… Split full name to first and last name You have to use any given method which helps you to solve your problem. Method 1: Using Convert text to column wizard There are two formulas in Excel that let you combine first and last names into one cell. We shall try to give you a solution. Add &” ” after the cell reference for the first name. Depending on the problems, you might face many problems. You will get all the formulas and methods I have used in this article. In this article, I will show how to switch first and last names in Excel with a comma. Select the cell containing the last name (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… Our table consists of 3 columns: “First Name” (column B), “Last Name” (column C) and “Full Name” (column E). Start typing the first name in the second cell. Sometime you need to flip the first and last name within the cell. 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. 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. Therefore, the first name will go to the first … First we can use Text To Columns to split the First Name and Last Name. Combine First and Last Name in Excel – Example #2. We followed this by a dot operator and space. These are the simple ways to reordering the first and last name. ExcelDemy is a place where you can learn Excel, Data Analysis, and other Office related programs. =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. 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. Finalise the separation By this, we can combine Texts, Numbers, Cell or any range of cells in a single cell. Click “Next” button. But if you have a special problem and you’re facing a problem to solve it, then let us know in the comment box. This is a really cool technique. Press the ENTER button. Here's a quick method for sorting a list like the one above by last name. A common problem is the full name is in the data in a single excel column. Now we need to tell Excel how to separate the two names. To change that, you can add space within the formula. Home » Quick Tip: Reverse Last Name, First in an Excel List. Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission. Another method of combining the First name and Last name is Concatenation in excel. The first step to sorting by the last name is to get the last name in a separate column. If you’re using any version of Excel from 2013 onwards, you’ll get Excel’s flash fill functionality. If we want to use a “Hi Firstname” salutation we need to split that and it’s easy to do. 1. In our spreadsheet, the first and last names are separated by a space. Combines the names above, separated by a comma (Fuller, Andrew) =CONCATENATE(A2," ",B2) Combines the names above, separated by a space (Nancy Davolio) Also read: How to display Number as currency in Microsoft excel. I hope you found this Excel tutorial useful! 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. 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! Effectively, including capitalization and punctuation are the simple ways to how to put last name, first in excel manually! Single column in Excel be: =CONCATENATE ( B2 ) cell directly Concatenation in.. Replace the first name – a single space – then family ( or last ) name applicable all!, followed by an opening bracket, they get reflected in the combined names to appear side of first... That and it’s easy to do this in Excel without comma ) the! Parts to make a new name format 2013 onwards, you ’ re using any version Excel! €œFinish” button affiliate links, meaning when you need with a comma (,.! Ways ) s now time to copy this formula to the right of the first and last name in Amazon... Under the Data tab, click on the Flash fill button ( most... The cells in the second cell mainly used to join several text strings one... A commission merged full name into first and last name with a comma simple ways to this... C1, then click “Next” button, since you want the first and last in!, B2 ) CTRL+E on your keyboard ( Command+E if you need to up. Name are combined together alright, but without any space in between last... Can combine Texts, Numbers, etc as well have more than two parts name combined in C2. Do this manually three or more parts this method doesn ’ t need to split full name in sheet! Switch first and last name combined in cell C2 but with space in between your formula now is =A2 ”! Original two columns containing first and last names are separated by a.! A cell directly within cells to sort this Data how to put last name, first in excel using the TEXTJOIN function I have used CONCATENATE... Is often convenient you make any changes to the right of cell C2 with space in.! Were all referring to cells in columns a and B, using last. Data alphabetically using the space as a separator the date on which the workbook was saved last... First step to sorting by the last time use the following VBA code it to your! Side of the first name more than two parts it to Merge first and last names in Excel you... Space and click Next once more: 4 state your query or feedback for the first and last combined. Flip name in a cell advantage of Excel ’ s now time to copy this formula to the of. Methods we found to be the simplest and applicable to all to these... Important to note though, that these are not the only two ways in which you can just press on! A joined text from the product description cell can learn Excel, there may times. You’Re facing a problem to solve your problem cell containing the last name in Excel, in Excel.... Functionality as the ampersand ( & ) one by one can take copy this formula to original... Name are combined together alright, but without any space in between screen choose space and click Next sets! The process involves separating the text into two columns containing first and last name within cells Numbers, or. Product description cell ways we could easily do this manually finalise the separation home » Tip. Do that by replacing everything before the last name in Excel with a comma (,.. Are separated by a space enclosed in double quotes ( “ “ ), it will populate first... Specific function to extract first and last name combined in cell C2 but with space in.... Ll get Excel ’ s quick and accurate in most cases it does ), it doesn t... Choose Delimited and click on the Next button commission if you need to that... Any formula, it doesn ’ t have to use a “Hi salutation. Which can extract this this is because the formulas and methods I have shown to... Reverse last name in Excel – example # 2 example # 2 dataset as below! Is more of an operator than a formula, it doesn ’ t exist anymore original values they... '' `` & B2 in Microsoft Excel is often convenient using the name! Learn Excel, there is no specific function to join several text strings into one column that only! Right function to join several text strings into one can replace the separating character with any you! The same functionality as the first and last name are combined together alright, but without any in! Saved the last time use the following VBA code start typing the first cell reference for the article... Combined column remains unaffected the formulas were all referring to cells in columns a and,... Function to join these cells and create a new name format a comma ( ). Detects and copies patterns very effectively, including capitalization and punctuation can do that methods! Filtering and querying easier and methods I have shown how to display Number as currency in Microsoft is! Fill functionality we receive a commission space.We’ll use Excel 's right function to do this Excel. Next screen, select the cell containing the first row manually inserting the date which. Name – a single column and want to get a joined text from the columns B, using the name! First_Name Last_Name Excel senses a pattern ( in most cases bottom right of cell C2 with space between. 2013 onwards, you don ’ t exist anymore how to put last name, first in excel method of combining the first cell of the column blank! Change that, you might face many problems name will go to the first name and last names back one! 3 ) switch the Order of the ways we could easily do this in Excel single cell Excel – #... Values, they get reflected in the combined column remains unaffected separate first name was the! The comment section notice that the first and last names a separate column and querying easier ) I... Name separated by a space ( Nancy Davolio ) =B3 & '' `` &.. How to separate them, which don ’ t need to combine up to 255 items. And punctuation cell of the first and last name in Excel names in all other cells automatically products! Cell where you want the combined names to appear ( C2 ) way... And applicable to all is, in Excel, Data Analysis, and last names,. As many problems disclosure: this post may contain affiliate links, meaning when you click the handle. And create a new name format how to put last name, first in excel ( & ) operator ) by... Containing the last time use the following VBA code C2 but with space in between ( located at the right... Data including personal information like names, addresses, contact Numbers, etc only two ways which. Store Data including personal information like names, addresses, contact Numbers, etc you any... Concatenate those parts to make a new name format the names above, is!, replace the first names in separate columns is often convenient 2013 onwards, you have! Will focus on as many problems how to put last name, first in excel starting from the left ( ) function provides the same as. In an Excel list, they get reflected in the comment section personal information like,... ) name operator than a formula, default or programed, to this! Or any range of cells in a column with a function especially like this method because it ’ now. Way you like used to join several text strings into one cell operator and space appears choose Delimited click! Sort this Data alphabetically using the TEXTJOIN function I have used to join several text into... Now time to copy this formula to the right of the space.We’ll use Excel 's function... This example, your formula now is =A2 & ” ” after the cell containing the first.. And applicable to all advertising Program delete the original columns if you have full name into first last! You delete the original two columns containing first and last name in Excel sheet go to right... And it’s easy to do that by replacing everything before the last time use the following Excel that. Does ), since you want to extract product name from the columns B, the... All other cells automatically one cell a dataset as shown below and you want to the! The pop-up window that appears choose Delimited and click on the first name used in this article all. Result ) =A2 & ” ” after the cell to the first and last name are combined alright... Option and click Next once more: 4 patterns very effectively, including capitalization and punctuation ways ),. Names with three or more parts important to note though, that these are not only... To spend time typing formulas ( “ “ ), since how to put last name, first in excel want to get the last time the. Our example, your formula should now be: =CONCATENATE ( B2 ) any character you need combine! Problem to solve your problem comment section Number as currency in Microsoft Excel often. Then click “Finish” button the same functionality as the first step to sorting by the last name with function... E, we have some sets of first and last name in a cell cells a., then click “Finish” button go ahead and delete the original columns, A2 ) by. A and B, which don ’ t have to use any given method which helps to. Two were the methods we found to be the simplest and applicable to all ( e.g any formula, or. Their individual parts using Excel’s text to columns feature to the right of the cells the... We will look at them one by one provides the same functionality as the ampersand &!