Splitting full name - Last name

Carolyn

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.

Akshay Davis

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

Sueann See

@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

Carolyn

Hi Akshay and Sueann

I am very excited, love learning new functions. This has worked perfectly. Appreciate your help on this

thanks

Carolyn