Different Ways On How To Separate First And Last Names in Excel

Are you looking for an easy guide on separating first and last names in Excel? 

If so, then search no further! Keeping your data organized doesn't have to be tedious or time-consuming anymore. This blog post will teach you how to efficiently use Microsoft Excel to take all your contacts' full names and separate them into their respective first and last name columns.

Read on as we cover the following:

• Split Names Using Functions

• Split Names Using Text to Columns

• Split Names Using the Find and Replace Tool

• Split Names Using the Flash Fill Feature

• Final Thoughts on How to Separate First and Last Names in Excel

• Frequently Asked Questions on How to Separate First and Last Names in Excel

Split Names Using Functions

You can use Excel text functions in conjunction with Excel formulas to split data.

In the example below, you can use specific formulas to separate the first and last names under their respective columns.

You can use two different formulas in Excel to extract the first names.

Extract the First Name With TEXTBEFORE Function

To get the first name into the first name column, use the TEXTBEFORE function.

1. Select a cell in Column C and type the TEXTBEFORE function

2. Choose the cell that has the full name, for example, B2.

3. Type a space character to have a delimiter.

The first name will appear in cell C2.

NOTE: Currently, only Microsoft 365 users can use the above formula.

Extract Names with the LEFT Function

You can also use the LEFT function to populate the first name column.

1. Select a cell in Column C and type the LEFT function.

2. Select cell B2.

3. Use the FIND function to determine the number of characters in the first name. Then, you'll use the following formula to subtract one from the position of the space character.

    

4. To extract the first name, press " Enter " and close the parenthesis."

Excel can use two formulas to extract the last name from the entire column.

Tips:

You can use the SEARCH function instead of the FIND function in the formula above. While both functions are similar, the FIND function is case-sensitive, and the SEARCH function is not.

Since we are looking for the position of a space character when extracting names, case sensitivity is not important. The formula used for the SEARCH function is:

Insert the Last Name from the Full Name Column

Excel users can use the following Excel formula to extract the last name column text.

1. Type the RIGHT function formula.

   NOTE:

    We can use the LEN and the FIND functions to determine the number of characters in the last name. The LEN function tells us the number of characters in a text string.

   We need to subtract the location of the space character from the number of characters in the full name text string.

2. Type the LEN function, then cell B2.

3. Use the FIND function to locate the first space in the cell containing the first and last names, and then subtract that position from the formula.

   You have learned how to split first and last names using Excel formulas.

   

Insert Names to Separate Cells with Middle Names

We can use formulas to split names with Excel's first, middle, and last names. The table below shows full names with middle names.

1. Type the TEXTSPLIT function and select the first cell from the full name column.

2. Put a space character for your delimiter. Finally, put a parenthesis and press Enter.

The cells for first name, middle name, and last name will be filled automatically

Note:

• Type the formula only in one column.

• The TEXTSPLIT function simplifies the process of entering multiple formulas in three columns.

• Only Microsoft 365 users have access to TEXTSPLIT, TEXTBEFORE, and TEXTAFTER.

Insert Names with Middle Names

1. Choose a column, then type the MID function with its cell value.

2. Type the FIND function with a delimiter and cell reference and +1.

3. Input the number of characters in the middle name. Then, use the following formula to locate the last space.

4. To separate the middle and full names, close the parenthesis and press "Enter."

You have learned how to use formulas in Excel to separate names.

For other uses of the SUBSTITUTE function in Excel, check out our guide on removing dashes in Excel.

Split Names Using Text to Columns

The text-to-columns wizard is a useful Excel feature that can convert text to columns into separate columns.

We can use the converted text to columns wizard to separate the text into different columns.

1. Choose the cells with full names. Do not include the column headers.

2. Navigate to the Data tab and click Text to Columns under the Data Tools group.

3. Click Delimited, then the Next button.

4. Select the Space as Delimiters and the Next button.

   The data preview window will display how Excel separated the full name into distinct columns.

5. To avoid overwriting existing data, click "Finish" after changing the destination cell to C2.

The program automatically fills names into two separate columns here.

To learn more about Excel's Text-to-Column tool, check out our tutorial on splitting cells. 

Split Names Using the Find and Replace Tool

The Find and Replace tool is a useful feature in Microsoft Excel.

1. Move the list of separate names in Excel from their current location to a new empty column specifically designated for names.

2. To use the Find and Replace dialog box, press the CTRL+H keys.

3. To replace all occurrences of a space character and an asterisk symbol, input them in the "Find what" field and then click "Replace All."

The column will only keep the first name and delete all other names in a single column.

In Excel, input an asterisk followed by a space in the "Find what" box to find the last name, giving you the desired output.

Split Names Using the Flash Fill Feature

The easiest method is to separate the data format by names in Excel.

This feature automatically fills in names and detects a pattern.

You need to enter the first name manually in the second cell. Flash Fill will display a list of first names (in gray). 

Once you press "Enter," the Flash Fill feature will automatically populate the succeeding cells.

Final Thoughts on How to Separate First and Last Names in Excel

You can export your data now that you have separated your first and last names in Excel. Moreover, you can confidently move ahead, knowing your data is clean and organized. Not only will this make your data easier to use and understand, but it will also allow for a more meaningful analysis of the information.

For more easy-to-follow guides, visit Simple Sheets and the Related Articles section of this blog post. Subscribe to Simple Sheets on Youtube for the most straightforward Excel video tutorials!

Frequently Asked Questions on How to Separate First and Last Names in Excel

Why doesn't my Flash Fill feature in Excel work?

If your Flash Fill feature doesn't work, do the following steps:

1. Navigate to File.

2. Click Options.

3. Click Advanced and select the Automatically Flash Fill box under the Editing options.

What should I do if the names of my data have a space and a comma by using Text to Columns?

To handle names that have a comma and space, such as "Jason, Robbie," make sure to select the "Comma" and "Space" boxes under "Delimiters." Also, choose the "Treat consecutive delimiters as one" option.

In Excel, how do I extract the last two characters?

To extract the last n characters, such as the last three characters, use this formula: =RIGHT(cell_reference,3).

Related Articles:

How to Combine Cells in Excel

Excel Remainder: How to Get it With the Excel Mod Function

Basic Microsoft Excel Formulas You NEED to Know