Splitting full name - Last name
Hi
I have Full name fields, data is all represented in one field and l need to split this out to First name, middle name and surname. If l can get a split of surname (being last name in string) and all others into another field l can split this out using split function.
So looking at list below l want to be able to split the one column out to surname and first names.
John Smith
Peter Paul Brown
Betty Jones
Ann Betty Joan Smith
Is there a way to just split the last name in string to surname column and all others expect last name in another so it would like this at the end:
FirstName LastName
John Smith
Peter Paul Brown
Betty Jones
Ann Betty Joan Smith
thanks
Carolyn.
1
Answers
You will want to use the split function to split the name on spaces, then take the last term for the surname and other terms for the first name.
For example, start by splitting the name term on a space and storing the resulting list as a variable
Then extract the last element from the list by using the list count function
This results in
In a separate function do the same, but extend it to remove the last name from the list using the list difference function, then replace the commas with spaces for the first name.
Which results in
@Carolyn try this. Of course, this is based on the assumption the last name consist of only one word and is always the last word in the full name. Are you working with names of different nationalities/ethnicity? For example, "Au Yeung" is a possible Chinese Last Name or Surname that would not be split correctly with this function. It appears that this is possible for western names as well. https://en.wikipedia.org/wiki/Double-barrelled_name
Hi Akshay and Sueann
I am very excited, love learning new functions. This has worked perfectly. Appreciate your help on this
thanks
Carolyn