This tutorial will show how to separate first and last names in Excel with formulas, Users will learn various methods to separate names, including the use of Excel functions such as LEFT, RIGHT, and FIND or with Text to Columns.
In this article, we’ll go through clear instructions and practical examples, this resource simplifies the process, making separate first and last names in Excel accessible for both beginners and experienced Excel users.
How to Splitting a full name into first and last names in Excel:
In step one, we will learn to split the Full name into separate first and last name columns with Text to Columns.
1 - Select the Column with Full Names: (e.g., "A") to select the entire column containing the full names.
Ensure that the full names are listed in a single column with the same pattern. for example: only first and last name, or first, middle, and last name,
2 - Go to the Data Tab: On the Ribbon at the top of Excel, click on the Data tab > Data Tools group and click Text to Columns.
3 - This will open the box Convert Text to Columns Wizard. In the wizard, choose the Delimited option And press Next
4 - Select the Space Delimiter: In the Delimiters section, check the Space box. This tells Excel to split the text based on spaces.
In the Data Preview section, You can preview how your data will be split. Then Click Next.
5 - If you want to replace the original data, you can keep the default destination (which is the same column).
Click Finish. Excel will now separate the names into separate columns based on the space delimiter.
This method assumes that there is only a single space between the first and last names. If you have names with middle names or multiple spaces, the Text to Columns tool will split the names into additional columns accordingly.
Method 2: How to Split Names in Excel with Formulas
If you want to keep your original names intact and just display the split names in new columns for display, you'd better divide names with formulas
Assume your full names are in column "A" starting from cell A2, enter the following formula in cell B2 to extract the first name:
=LEFT(A2, FIND(" ", A2) - 1)
This formula finds the position of the first space and extracts the characters before it.
To Extract the Last Name: In cell C2, use the formula:
=RIGHT(A2, LEN(A2) - FIND(" ", A2))
Method 3: Split names In Excel 2013, 2016, and 2019 with Flash Fill
-
Enter the First Name Manually: If your name is in column A, type the first name manually in column B next to the first full name.
-
Start Typing the Last Name: In column C, start typing the last name next to the first name you just entered.
-
Use Flash Fill: Excel should recognize the pattern and offer suggestions to fill the rest of the column. Press Enter to accept the Flash Fill suggestions.
By following these methods, you should be able to split names efficiently in Excel!
=================================
