In column B5:B10, we have full names. Let’s split these names into the First Names and Last Names columns.
Method 1 – Using the Convert Text to Columns Wizard to Split Names in Excel into Two Columns
Steps:
- Select the cells (B5:B10) that include the texts you need to split.
- Go to the Data tab and choose Data Tools. ‘
- Select Text to Columns. The Convert Text to Columns Wizard window will appear.
- Choose Delimited and click on Next.
- Choose the delimiters for your strings. In this example, the delimiter is space.
- Click on Next.
- Choose the Destination (C5) in the current worksheet where you want to split texts to display.
- Click on Finish.
- Here is the split data:
Read More: Separate First and Last Name with Space Using Excel Formula
Method 2 – Splitting Names into Two Columns Using Flash Fill
Steps:
- In the neighboring cell C5, type the first name from the left cell manually.
- In the cell C6, start typing the corresponding first name.
- You should see Flash Fill show you a suggestion list of the first names in grey.
- Press Enter. Flash Fill will fill the rest of the column.
- Repeat the process for the last names.
Read More: How to Split Names Using Formula in Excel
Method 3 – Using Excel Formulas to Split Names into Two Columns
Case 3.1 – Getting the First Name in the First Column
Steps:
- Copy the following formula in an empty cell C5.
=LEFT(B5, FIND(" ",B5)-1)Here, the FIND function gives the location of the first space from the string B5 and the LEFT function returns the characters from the string that is before the first space. You need to put minus 1 for the string length to exclude the space.
- Press Enter.
- Drag the Fill Handle to get the 1st names from the rest of the full names.
- Here’s the result.
Case 3.2 – Getting the Last Name in the Second Column
Steps:
- Copy the following formula in the empty cell D5.
=RIGHT(B5,LEN(B5)-FIND(" ",B5))Here, LEN(B5) determines the length of the string in cell B5.
The FIND(“ ”, B5) gives the location of the space from the full name and finally, the RIGHT function returns the characters from the full name after the space.
- Press Enter.
- Drag the Fill Handle to get the last names from the rest of the full names.
- Here’s how the sample dataset looks:
Read More: How to Separate First Name Middle Name and Last Name in Excel Using Formula
Method 4 – Splitting Names into Two Excel Columns Using Find & Replace
Case 4.1 – Getting the First Name in One Column
Steps:
- Copy all the full names and paste them into the neighboring column (C5:C10).
- Select C5:C10, go to the Home tab and choose Find & Select. It may be under Editing.
- Select Replace. A Find and Replace dialog box will pop up. You can also press Ctrl + H.
- Enter “ *” (1 space before asterisk symbol) in the Find what box and leave the Replace with box blank.
- Click on Replace All.
- Excel might show you a notification box. Click OK.
- Close the window.
- Here is the result:
Method 4.2 – Getting the Last Name in Another Column
Steps:
- Copy all the full names and paste them into the column for Last Names.
- Select the resulting cell range D5:D10, then go to the Home tab and choose Find & Select.
- Select Replace. A Find and Replace dialog box will pop up. You can use the Ctrl + H keyboard shortcut, as well.
- Enter “* ” (1 space after asterisk symbol) in the Find what box and leave the Replace with box blank.
- Click on Replace All.
- You may need to close a notification from Excel.
- Close the window.
- Here is the result:
Read More: Excel VBA: Split First Name and Last Name
Download Practice Workbook
