Suppose you have a list of names in your data records separated by a space, now want to separate first and last names into two columns.
Microsoft Excel allows to spilled a single cell or multiple cells. In our previous article, we try to explain how to split a single cell in half in Excel. In the guides below, we’ll converse about three effective methods for splitting first and last names in excel into two columns.
We’ll be using the most recent version of Microsoft Excel 2019 for this article. However, the methods work on all Excel versions.
Method 1. Splitting names using the Text to Columns function
If your data set contains a list of names where the respective first and last names are in one cell, you can use the text to columns wizard to separate them into separate cells if required.
Follow these steps to splitting names using the text to columns tool:
1. Open the file containing the list of names you want to split.
2. Select all the names in the “Full Name” column.
3. Switch to the “Data” tab and click on the “Text to Columns” option under the “Data Tools” category.
4. Convert Text to Columns Wizard dialog box will appear. Here have three steps, each of which requires you to make changes.
5. In step 1 of 3: Select “Delimited” (this allows you to use a space between the first and last names as a separator). Click “next“.
6. In Step 2 of 3: Uncheck all Delimiters first and then check “Space” only. Click “next”.
7. In step 3 of 3: Select the “Destination” cell, where the first name appears after splitting. For our example, we select column E2.
8. And finally, click “Finish“.
Now, we were able to successfully split the first and last names from a single cell into two separate cells, as shown below.
PRO Note
Text to columns will overwrite the existing column if you don’t specify a destination cell.
Method 2. Use Formulas for splitting first and last names in Excel spreadsheet
A different approach to splitting first and last names in Excel is using formulas.
If you change the full name, the change will automatically be reflex in the split first and last name cells as well.
You can split your full names cell fairly easily using the following formula:
Cell B2: First Name [space] Last Name
Formula to get the First Name in Cell E2
| =LEFT(B2,FIND(” “,B2,1)-1) Another formula to split the first name =LEFT(B2,SEARCH(” “,B2)-1) |
Formula to get the Last Name in Cell F2
| =RIGHT(B2,LEN(B2)-FIND(” “,B2)) Another formula to split the last name =RIGHT(B2,LEN(B2)-SEARCH(” “,B2)) |
Do not forget to enter the space between the quotation marks.
If you want to put them back together, for example, if you want to get the first and last names separately, but want them to be in one cell, use the following formula:
| =E2 &” “& F2 |
This allows you to reassemble the names. The space between the quotation marks is important here. So that there is a space between the first and last name.
Method 3. Separate first and last names Using Find and Replace
You can separate first and last names with Excel’s Find and Replace feature. This one is weird, but it works and may be faster than the other options. Let’s see the below process to splitting names using it.
Get the First Name using Find and Replace
1. Select all the name datasets (B2:B9 for this example) and copy them using the “Ctrl+C” keyboard shortcut.
2. Select the destination cell and paste them. (Ctrl+V keyboard shortcut).
3. Select your pasted all full name and stay on the Home tab. Click the “Find & Select” in the Editing group.
4. Click on “Replace“. A new dialog box will appear.
5. Enter the following into the “Find and Replace” dialog box:
- Find what: * (Type a space ( ), and then an asterisk *)
- Replace with: Leave this field blank
6. And then click “Replace All“.
Result
Get the Last Name using Find and Replace
1. Select all the name datasets (B2:B9 for this example) and then copy and paste them to the destination cell.
2. Click the “Find & Select” button in the Editing group on the Home tab.
3. Click on “Replace“. A new dialog box will appear.
4. Enter the following into the “Find and Replace” dialog box:
- Find what: * (Type an asterisk *, and then a space ( ) character).
- Replace with: Leave this field blank
5. And then click “Replace All“.
