community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Extract last n words from a string

Say I have the following table:

 

Sentence
A Data Model is a new approach for integrating data from multiple tables.
This parameter is a boolean flag.
I will be going to Germany with my boyfriend this May.
Nobu is a Japanese restaurant known all over the world for its unique food

 

How can I extract the last x words from each sentence. Is there a function similar to SUBSTITUTE in Alteryx wherein I can replace the xth occurrence of the space or is there another option? The REPLACE function replaces all instances of the character.

 

Thank you!

 

Alteryx
Alteryx

Hi @marapatricia,

 

Wasn't sure if you wanted to extract the last word or replace it within the string so I have done both in the attached example. One uses Regex replace in a formula tool, the other uses the Regex tool to parse out the final word.

One option is to break the sentenances into words and then you can act on them.

 

I have attached a demo doing this to extract the last four words of each example.

Last4Words.jpg

Alteryx Certified Partner
Alteryx Certified Partner

Here is a different approach:

 

Capture1.PNG

 

I explicitly assign word IDs and reverse word IDs to each record (removed the periods too).  You can filter on the number of words that you want.

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Alteryx Certified Partner
Alteryx Certified Partner

.

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.

Mine and @MarqueeCrew approaches are very similar. Allows you a lot of flexibilty on how to reprocess the sentences

Alteryx Certified Partner
Alteryx Certified Partner

@jdunkerley79 had an approach with fewer tools.  Bonus Kudos to him :)

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.

Thanks @jdunkerley79 and @MarqueeCrew! this is what I needed. Really appreciate :smileyhappy:

 

Thanks also Jamie! This helps to understand how to use RegEx in this scenario. :)

Meteoroid

I realise this is an old question but it came up when I was searching for a solution to this problem today. I figured out a single-formula solution which I'm sharing in case it helps anyone else looking for this.

 

ReverseString(GetWord(ReverseString([Name]),0))

 

This works if you want the last word, and the string is separated by spaces. Basically it's 3 steps, nested:

ReverseString([Name]) - reverses the whole string

GetWord(<string>, 0) - this gets the first word of the string

ReverseString - reverse the word back to normal

 

You could probably modify it to get multiple words but it's a bit more fiddly... e.g. this gets the last 2 words, although you have to manually add the space and so you get an extra space if there is only one word in the string:

ReverseString(GetWord(ReverseString([Name]),0)+
" "+GetWord(ReverseString([Name]),1))

Labels