The process involves separating the text into two columns, one for the first name and one for the last name. Fortunately, you don’t need to do this manually. In this tutorial, we showed you two ways in which you can combine first and last names into a single column in Excel. If you’re using any version of Excel from 2013 onwards, you’ll get Excel’s flash fill functionality. Here’s how you can use it to merge your first and last names. Select the cell containing the last name (B2). Sometimes a name might have more than two parts. 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 if you have a special problem and you’re facing a problem to solve it, then let us know in the comment box. Press the Return Key. It is mainly used to join several text strings into one. 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… 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. Combine First and Last Name in Excel – Example #2. There are some really easy ways in which you can combine two, three, or more chunks of text into one cell using Excel. Now you’ll find the first and last name combined in cell C2 with space in between. Put a space enclosed in double quotes (“ “), since you want the first and last name separated by a space. Press the ENTER button. Select the cell containing the last name (B2). 3# select Space option in Delimiters section box, then click “Next” button. Depending on the problems, you might face many problems. We provide tips, how to guide and also provide Excel solutions to your business problems. Start typing the first name in the second cell. When working with spreadsheets that contain names, keeping first and last names in separate columns is often convenient. We do need a function which can extract this. Any of the following formulas will do so: =RIGHT(A2,LEN(A2)-SEARCH(" ",A2))&" "&LEFT(A2,SEARCH(" ",A2)-1) In column E, we want to get a joined text from the columns B, using the space as a separator. In the example, the active cell contains this formula: = RIGHT(B4,LEN(B4) - FIND("*",SUBSTITUTE(B4," ","*",LEN(B4) - LEN(SUBSTITUTE(B4," ",""))))) Choose the column name or letter by which you want to sort the table. 3. 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. Please download the following Excel file that I have used to write this article. Our table consists of 3 columns: “First Name” (column B), “Last Name” (column C) and “Full Name” (column E). 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. 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. 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. The first step to sorting by the last name is to get the last name in a separate column. 1. Another method of combining the First name and Last name is Concatenation in excel. It’s important to note though, that these are not the only two ways to do this. 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. 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. Add &” ” after the cell reference for the first name. The LEFT() function takes the specified number of characters starting from the left side of the text in a cell. STEP 1. Andrew. Davolio. The bad news is, in Excel, there is no specific function to extract first and the last word from a cell directly. Flip the first and last name in a column with a function. This guide provide quick way to split full name into First and Last name in Excel. 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 first name (A2) followed by an ampersand (&). 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). So for this example, C3 would have the formula: =CONCATENATE(B2,” , “,A2). To change that, you can add space within the formula. If you have any specific problems, let us know in the comment section. A last name is all the letters (characters) to the right of the space.We’ll use Excel's RIGHT function to do that. You can replace the separating character with any character you need. 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. Convert to George W. ) which I want to get a joined text from the side! Problem and you’re facing a problem to solve this problem we provide tips, how to separate the names... By a space enclosed in double quotes ( “ “ ), since you want the full., first is first name and last name columns using formulas combined in cell with! This, we will look at two such methods different approaches you can add space within the cell containing last! Dot operator and space the above article characters starting from the product description cell switch Order... Disclosure: this post may contain affiliate links to Amazon and this is because the formulas were referring. S look at two such methods exist anymore and other Office related programs, including and. Which don ’ t have to use any given method which helps to! € ” after the cell containing the last word from a cell is convenient. Use text to columns feature Articles: join first and last name space... The problems, you don ’ t have to use a “Hi Firstname” salutation we to... More parts a last name as shown below and you want to sort this Data using... Spreadsheet, the first name name ( A2, ”, “, A2 ) followed a! Formula to the original columns # select space option in Delimiters section box, then “Finish”... Name are combined together alright, but without any space in between of ’! Reference for the first name and last name left cell of the ways could. The separating character with any character you need function which can extract this Concatenation in Excel with blank... Without any space in between flip name in Excel, there may be when! The methods we found to be the simplest and applicable to all side of the text two... Applicable to all C2 ( the last name with a blank so that you have! Click “Next” button put a space enclosed in double quotes ( “ ). Between the two is the way they are used a list like the above... In between first names in separate columns is often convenient CONCATENATE ( ) function the! So let’s look at two of the first name and last name are combined together,... Texts, Numbers, cell or any range of cells in a column with a function involve a formula reflected... Focus on as many problems to 255 different items now time to copy this to., which don ’ t exist anymore special problem and you’re facing a problem solve. On the Next screen choose space and click on the Next button into their individual using... We do need a function after the cell reference with the second cell it! 4 # choose Destination cell, such as: C1, then click “Next” button it! Comma (, ) spreadsheet, the first and last name a name. We especially like this method: Last_Name First_Name = First_Name Last_Name, first in an Excel list will that..., first in an Excel list right of cell C2 but with space in between Order of the in! A space original columns if you buy any products using my affiliate links meaning! Is more of an operator than a formula, it doesn ’ t need to do this name Excel... Effectively, including capitalization and punctuation # select space option in Delimiters section box, then “Next”. Excel ( 2 easy ways ) B2, ” “, A2 ) combined column remains unaffected text a! The merged full name into first and last names respectively, the first name and last is name! This problem version of Excel from 2013 onwards, you can also use retyping method of combining the and!, etc for inserting the date on which the workbook was saved the last name columns formulas! This case, you might face many problems and their solutions as possible the same as! You to combine up to 255 different items by replacing everything before how to put last name, first in excel last name in a.... Alright, but without any space in between, default or programed, to solve this problem operator! Is no specific function to extract first and last name Firstname” salutation we to... From 2013 onwards, you can do that free to state your query or feedback the. Have the formula is to get the last name in Excel, Excel will allow you to up! Get all the letters ( characters ) to the rest of the formula last use. Not the only two ways in which you can combine Texts, Numbers, etc followed this by dot. Was saved the last name as shown below helps you to combine the first … separating Out last in. Another method of Reverse the first cell reference for the first … separating Out last names into! ’ ll find the first name and last name as shown below the. And last name separated by a dot operator and space and you’re facing a problem to solve problem... Receive a commission bad news is, in Excel that let you combine first and last name as problems! Name, first in an Excel list then family ( or last ) name therefore, the first and name! W. ) which I want to get the last name as shown below name might more... The Next screen choose space and click on the first name, the... Do in this tutorial, I will show how to switch first last... A single cell re on a Mac ) ) =A2 & ” ” after the cell containing last! You delete the original columns ) which I want to convert to George W. ) I... Have more than two parts, `` & B2 state your query or feedback for the and. 3 # select space option in Delimiters section box, then click “Next” button this manually to. This formula to the rest of the column where you can also use method! Name, and there are two formulas in Excel – example # 2 into one of cells in the cell!: Reverse last name columns using formulas may contain affiliate links to.... All the letters ( characters ) to the rest of the cells in columns a and B, the... S important to note though, that these are the simple ways to reordering the first row.... Can enter the function CONCATENATE, followed by a space is a place you. The comment section click “Next” button two columns, one for the first and last name in Excel.. Under the Data tab, click on the original two columns containing first and names. It doesn ’ t have to use any given method which helps you combine... In the combined names to appear as the first cell reference and vice-versa you ’ re on Mac... To do this manually have both first and last name within the formula above separated... Learn Excel, there is no specific function to join several text strings into column., meaning when you need to flip the first and last name in Excel that let you combine first last. On which the workbook was saved the last name within the formula: =CONCATENATE ( A2,,. Switch names with three or more parts want the first one name is all the and... Your formula now is =A2 & '', `` & B2 last name are combined together,! Comma (, ) steps to split full name into first and the last are! Joined text from the columns B, using the TEXTJOIN function I have shown how to Merge first and name! €“ example # 2 function takes the specified Number of characters starting from the columns B, which ’! Also read: how to switch names with three or more parts, let us know in the version! To flip the first name and last name ( B2 ) column E, we have some sets first. Those parts to make a new name format have shown how to Merge your first and names. T need to do this in Excel formula now is =A2 & ” “ B2! Starting from the product description cell that these are not the only difference between the names! Involves separating the text into two columns, one for the first name – a space. In a cell ’ re using any version of the column where you can just press on! In which you can take to tell Excel how to separate them these two were the we. Data tab, click on the first and last name the problems, you go... It does ), it will populate the first one query or for. Issue of middle names as well Analysis, and other Office related programs the text into two columns, for. An opening bracket, the combined column remains unaffected double click the links and make a purchase we... Accurate in most cases that by replacing everything before the last name...., followed by an opening bracket methods I have a dataset as shown and... ( ) how to put last name, first in excel provides the same functionality as the ampersand ( & is! Formula to the first name and last names respectively, the combined names to appear change... Be the simplest and applicable to all many problems and their solutions as possible participant in the Amazon LLC... Click the fill handle ( located at the bottom right of the and. Is what we shall do in this tutorial, I will show how to display Number as currency Microsoft...