Parsing Name into First Name and Middle Name

Question

I've a use case that I'm trying to solve. I have Name column which is unstructured in the sense that some names have an initial for middle , some have full name and some don;t have middle name at all. I've created 2 new fields First Name and Last Name. And for an example QUENTIN W WORTHY should have last Name of Worthy and First Name of Quentin M. Similarly MATTHEW TAYLOR RODRIGUEZ should have last name of Rodriguez and First Name of Matthew Taylor

ddiesel · Answer

Hi @Idyllic_Data_Geek!

To build upon @FilipR's solution, here's a RegEx approach with some of the other formatting options you requested above.

Please let us know if these solutions work for you!

Thanks,
Deb

FirstName LastName_dmd.yxmd

FilipR · Answer

Please see attached. My solution will work if the Last Name is one word (or two words separated by a sign other than a space). Remember to cleanse your data from trailing spaces, as the formula looks for the last space in a string.

FirstName LastName.yxmd

Qiu · Answer

@Idyllic_Data_Geek 
Parsing names are usually difficult and not in a fixed pattern which causing difficulty.
Can you kindly provide a few more samples so we can take a close look?