Extract first name, multiple middle names, & last name into columns in Google Sheets/Excel

Learn how to extract the first name, multiple middle names, and last name from a cell. Those names will show up in their own columns. These data cleaning formulas for names accounts for names where there are multiple middle names (with multiple spaces). There's no need for the text-to-columns feature which you need to run every time you want to get the proper first name, middle name(s), and last name from a list of full names.

* Intro - 0:00
* Insert blank intermediate calculation columns - 0:38
* Formula for number of spaces - 1:28
* Formula for 1st space position - 2:36
* Formula to replace last space with special character - 3:30
* Formula last space position - 5:19
* Formula for first name - 6:04
* Formula for middle name(s) - 6:55
* Formula for last name - 9:10

[Quinnedy]

I just followed your formulae and completed 9201 names in my spreadsheet - I then auto-filled the remaining cells by selecting the auto-fill button in each column, double clicked it to complete each vertical column and, Wallah, the whole operation took less than a a few minutes. Perfect!! Thank you.

[arunkumarkrishnan]

Excellent! This is what I was looking for in my roster to separate the employees' names. Thanks a lot for sharing.

[CharlotteHudgins]

Bravo!!!! Thank you so much. Thought I'd never find the solution lolol

[c.alexandria]

OUTSTANDING tutorial... you have saved me hours of heartburn. Thank you <3

[ryanf]

This is amazing! I like it. What if there is an extension like Jr., III, Sr., etc.?

[Quinnedy]

AFTER YOU COMPLETE THIS OPERATION YOU THEN NEED TO DO THE FOLLOWING TO CONVERT THE FORMULA VALUES TO TEXT
* Select the cells that contain the data or other attributes that you want to copy.
* On the Standard toolbar, click Copy .
* Click the first cell in the area where you want to paste what you copied.
* On the Home tab, under Edit, click Paste, and then click Paste Special.

[ushaniru]

It helped me a lot, Thanks for the procedure.

[carriebwellness]

This is amazing! Thank you SO much for this—really saved hours on a new export we need to upload for a new list. Just one question for you, though—If someone has ONLY provided their first name (so no spaces appear in Column A), which currently causes all other formulas you create here to give a #VALUE error, is there a formula to ADD a space after their first name, which fixes the error?

[KKayne]

LOVE THIS!! Thank you so much!! Question: How do you TRIM spaces from before and after the name and extra spaces from in between the names?

[JCEurovisionFan]

What if there are two last names such as del Carmen, Anderson, Jr., or Thompson IV?

[chicsharp]

How do you do a count of names in a column of cells, IF there's other names in those cells?

[clickposts]

And if there is only first name? May you help me, please?

[robertthompson]

For names like Dick Van Dyke where Van Dyke is the last name, this spreadsheet would show Van as the middle name and Dyke as the last name.

[neinu_]

Sorry i got lost the middle name is still showing error