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!

[wdavis · Accepted solution]

Hi @rwelman003 

If you use the Formula tool you can use the 'GetWord' and 'CountWords' formulas to get this for you.

For the first name it would be Getword([Field], 1) then for the last name it would be Getword([Field]. (Countwords([Field])-1)

Let me know if that works for you

Kind regards

Will

[rwelman003]

Brilliant, this has worked perfectly! Thanks so much!

[Karam]

Hi @rwelman003 

Please see attached example of how this can be achieved using RegEx.

Regards,

Karam

[shuman-p]

... however, methinks your " Getword([Field]. (Countwords([Field])-1)" has a small typo with the "." (fullstop), which should be a "," (comma)?

[Data_User88]

...

[cfrat]

Aloha, 

Thank you for this solution! I think your answer is missing a closing parenthesis. I added it in red below.

Getword([Field],(Countwords([Field]))-1)

Mahalo, 

Celene

[lisebiss]

Hello, thanks for this formula which works very well. However, i have 3 days of Alteryx and i do not understand what the "-1" means. Can you explain to me? Thanks a lot in advance!!

[atcodedog05]

Hi @lisebiss 

Exactly means last word 🙂. 1 from the back.

[lisebiss]

Thanks! I think i had not understood well the logic of the Countwords formula that's why. Then if we would put "3" instead of "-1", it would return the 3rd word of the sentence, right?