Extract last n words from a string

[marapatricia]

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!

[JamieD]

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.

[jdunkerley79 · Accepted solution]

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.

[MarqueeCrew]

Here is a different approach:

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.

[MarqueeCrew]

.

[jdunkerley79]

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

[MarqueeCrew]

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

[marapatricia]

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

[marapatricia]

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

[Cerys]

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))