How to easily separate names in Excel using the Text to Columns feature or formulas. Follow our step-by-step guide to separate first name, middle name, and last name into separate cells in Excel. Improve your Excel skills with this helpful tutorial.

Extract the first name

How to Separate Names in Excel

Assuming the full name is in cell A2, you can use the following formulas:

  1. To extract the first name: =LEFT(A2,FIND(" ",A2)-1)

This formula uses the FIND function to locate the space between the first and last name, and then the LEFT function to extract the characters to the left of that space.

How to Separate Names in Excel

Extract the last name

To get the last word from a string is not so easy because what I’ve mentioned earlier, the FIND() function works from left to right so we need to find the last space on the string and get the text to the right of it, Here’s the formula :



Extract the middle name (if available)

=IFERROR(MID(A2, SEARCH(” “, A2) + 1, SEARCH(” “, A2, SEARCH(” “, A2)+1) – SEARCH(” “, A2)-1),””)

This formula uses the FIND function to locate the first and second spaces, and then the MID function to extract the characters between those spaces. The IFERROR function is used to handle cases where there is no second space (i.e., no middle name), in which case an empty string is returned.

You can enter these formulas in the adjacent cells to the full name, and then drag them down to apply them to the entire column of names.

You May Also Like

By seoanalyser

SEOAnalyser is a powerful tool for analyzing and improving your website's SEO performance. Our website provides a comprehensive suite of tools to help you optimize your website, including keyword analysis, competitor analysis, website analysis, and more. Try SEOAnalyser today and take your website's SEO to the next level!

Leave a Reply

Your email address will not be published. Required fields are marked *