Isolating first and last words in string

[rwelman003]

Hi there,

I am trying to isolate the first and last word in a string. I have data of names and some names include middle names or nicknames The names can vary up to 5 words.... I first tried to do text to columns but then I don't know how to get the surname of the shorter names under the right column. I then tried to use the RegEx function to parse the first and last names but have been unsuccessful in writing a formula. Please can someone assist?

Thanks!

[atcodedog05]

Hi @lisebiss 

Case 1: there is 3 words. Specifying "3" will return 3rd word. "-1" will return last word i,e 3rd word.

Case 2: there is 4 words. Specifying "3" will return 3rd word. "-1" will return last word i,e 4th word.

[lisebiss]

Crystal clear, thank you all!!

[jassik]

Many Thanks , I was struggling a lot with the last names to get them. It worked perfect for me🤗

[brandonbocking]

Yes that is right, but sometimes you want the last word only and cells can contain various number of words from row to row.