Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Greetings Experts!
The Surname field contains the majority of names but needs to be standardized to follow a consistent naming convention. Generally, the first name is properly formatted, although sometimes the first name is included in the surname field, even though it's captured separately.
Furthermore, the middle name is often missing, and the surname typically holds most of the entries. However, there are occasional inconsistencies within this field, such as titles appearing at the end or unusual characters.
Nonetheless, the primary goal is to separate the surname into middle and last names.
Below is a data sample to provide a clearer understanding. Thanks in advance @marcus_sommer @rwunderlich @or @steeefan @MarcoWedel
Load * inline [
First Name,Surname
David, David N.O Plies
Ivan,Ojo-Yaw
Jim,Jim Jame Jones
ESTATE,ESTATE OF Mke Peters-Sui
Theresa,Theresa cols / Henry cols
O.,O.J Johnson (Mrs.).
lt. LT. Col Larson
Duke,Duke L.T.Y
Reagan,Rita Rosilina Addo ];
The overall goal might be challenging because the entries lack a consistent pattern. Hope this helps.
...
Mid(Surname, Index(Surname, ' ', 1) + 1, Index(Surname, ' ', -1) - Index(Surname, ' ', 1)-1) as [Middle Name],
Trim(subfield(Surname, ' ', -1)) as [Last Name]
...