Buy Now

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

May 04, 2023
different-ways-on-how-to-separate-first-and-last-name-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

https://www.simplesheets.co/catalog

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.

Split Names Using Functions

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 functionExtract the First Name With TEXTBEFORE Function

  1. Choose the cell that has the full name, for example, B2.Extract the First Name With TEXTBEFORE Function

  1. Type a space character to have a delimiter.Extract the First Name With TEXTBEFORE Function

The first name will appear in cell C2.

Extract the First Name With TEXTBEFORE Function

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.Extract Names with the LEFT function

  1. Select cell B2.Extract Names with the LEFT function

  1. 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.Extract Names with the LEFT function

     

  1. To extract the first name, press " Enter " and close the parenthesis."Extract Names with the LEFT function

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:

Extract Names with the LEFT function

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.Insert the last name from the full name column

    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.

  1. Type the LEN function, then cell B2.Insert the last name from the full name column

  1. 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.Insert the last name from the full name column

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

    Insert the last name from the full name column

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.

Insert names to separate cells that have middle names

  1. Type the TEXTSPLIT function and select the first cell from the full name column.Insert names to separate cells that have middle names

  1. Put a space character for your delimiter. Finally, put a parenthesis and press Enter.Insert names to separate cells that have middle names

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.Insert names with middle names

  1. Type the FIND function with a delimiter and cell reference and +1.Insert names with middle names

  1. Input the number of characters in the middle name. Then, use the following formula to locate the last space.Insert names with middle names

  1. To separate the middle and full names, close the parenthesis and press "Enter."Insert names with middle names

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.Split Names Using Text to Columns

  1. Navigate to the Data tab and click Text to Columns under the Data Tools group.Split Names Using Text to Columns

  1. Click Delimited, then the Next button.Split Names Using Text to Columns

  1. Select the Space as Delimiters and the Next button.Split Names Using Text to Columns

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

  1. To avoid overwriting existing data, click "Finish" after changing the destination cell to C2.Split Names Using Text to Columns

The program automatically fills names into two separate columns here.

Split Names Using Text to Columns

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.Split Names Using Find and Replace Tool

  1. To use the Find and Replace dialog box, press the CTRL+H keys.Split Names Using Find and Replace Tool

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

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

Split Names Using Find and Replace Tool

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.

https://www.simplesheets.co/catalog

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).

https://www.simplesheets.co/catalog

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

Want to Make Excel Work for You?ย Try out 5 Amazing Excel Templates & 5 Unique Lessons

We hate SPAM. We will never sell your information, for any reason.