We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Isolating first and last words in string

rwelman003
5 - Atom

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!

12 REPLIES 12
wdavis
Alteryx
Alteryx

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
5 - Atom

Brilliant, this has worked perfectly! Thanks so much!

Karam
8 - Asteroid

Hi @rwelman003 

 

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

 

first_last.PNG

 

 

 

Regards,

Karam

shuman-p
7 - Meteor

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

Data_User88
8 - Asteroid

...

cfrat
7 - Meteor

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
6 - Meteoroid

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
22 - Nova
22 - Nova

Hi @lisebiss 

 

Exactly means last word 🙂. 1 from the back.

lisebiss
6 - Meteoroid

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?

Labels