What is the fastest way to separate first and last name in Excel?
|
We’re doing a lot of work with Customer Relationship Management (CRM) and marketing software. Customers often give us data that’s not easily used and we need to use formulas to fix any issues. A common problem is the full name is in the data in a single excel column. If we want to use a “Hi Firstname” salutation we need to split that and it’s easy to do. 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 1. Enter the formula of =LEFT(A2,FIND(” “,A2,1)-1) in a blank cell, says Cell B2 in this case.  2. Enter the formula of =RIGHT(A2,LEN(A2)-FIND(” “,A2,1)) in another blank cell, Cell C2 in this case. 3. Copy those two formulas down and you will see the full name column is split to the first name and last name columns as shown below. This quick tip has been a big time saver for us and I hope you find it helpful too. If this article did help you, please share it to your friends 🙂 Have you got a list of full names that need to be divided into first and last names in separate columns? It’s easy to do that, thanks to Microsoft Excel’s built-in options. We’ll show you how to perform that separation. How to Split First and Last Names Into Different ColumnsIf your spreadsheet only has the first and last name in a cell but no middle name, use Excel’s Text to Columns method to separate the names. This feature uses your full name’s separator to separate the first and last names. To demonstrate the use of this feature, we’ll use the following spreadsheet. First, we’ll select all the full names that we want to separate. We will not select any column headers or Excel will separate them as well. In Excel’s ribbon at the top, we’ll click the “Data” tab. In the “Data” tab, we’ll click the “Text to Columns” option. A “Convert Text to Columns Wizard” window will open. Here, we’ll select “Delimited” and then click “Next.” On the next screen, in the “Delimiters” section, we’ll select “Space.” This is because, in our spreadsheet, the first and last names in the full name rows are separated by a space. We’ll disable any other options in the “Delimiters” section. At the bottom of this window, we’ll click “Next.” Tip: If you have middle name initials, like “Mahesh H. Makvana,” and you want to include these initials in the “First Name” column, then choose the “Other” option and enter “.” (period without quotes). On the following screen, we’ll specify where to display the separated first and last names. To do so, we’ll click the “Destination” field and clear its contents. Then, in the same field, we’ll click the up-arrow icon to select the cells in which we want to display the first and last names. Since we want to display the first name in the C column and the last name in the D column, we’ll click the C2 cell in the spreadsheet. Then we’ll click the down-arrow icon. At the bottom of the “Convert Text to Columns Wizard” window, we’ll click “Finish.” And that’s all. The first and last names are now separated from your full name cells. RELATED: How to Use Text to Columns Like an Excel Pro If your spreadsheet has middle names in addition to first and last names, use Excel’s Flash Fill feature to quickly separate the first and last names. To use this feature, you must be using Excel 2013 or later, as earlier versions don’t support this feature. To demonstrate the use of Flash Fill, we’ll use the following spreadsheet. To start, we’ll click the C2 cell where we want to display the first name. Here, we’ll manually type the first name of the B2 record. In this case, the first name will be “Mahesh.” Tip: You can use Flash Fill with middle names, too. In this case, type the first and the middle name in the “First Name” column and then use the Flash Fill option. We’ll now click the D2 cell and manually type the last name of the record in the B2 cell. It will be “Makvana” in this case. To activate Flash Fill, we’ll click the C2 cell where we manually entered the first name. Then, in Excel’s ribbon at the top, we’ll click the “Data” tab. In the “Data” tab, from under the “Data Tools” section, we’ll select “Flash Fill.” And instantly, Excel will automatically separate the first name for the rest of the records in your spreadsheet. To do the same for the last name, we’ll click the D2 cell. Then, we’ll click the “Data” tab and select the “Flash Fill” option. Excel will then automatically populate the D column with the last names separated from the records in the B column. And that’s how you go about rearranging the names in your Excel spreadsheets. Very useful! Like this, you can quickly turn a long column into multiple columns with a useful Excel feature. RELATED: How to Make One Long Column into Multiple Columns in Excel READ NEXT
|
