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!

Preparation

Accepted answers

jdunkerley79

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.

Last Words By Split.yxmd

All comments

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.

RegexParse.yxmd

jdunkerley79

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.

Last Words By Split.yxmd

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.

Sentence.yxmd

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

55dhr

Exactly what I needed. Very simple. Sometimes when you ask people what time it is they show you how to build a watch. Thanks for the time.

binhnguyendn1606

If I understand your problem correctly, you want to split a string to 2 columns by a word in the string.

I come up with this idea which I think is simpler than most of solutions here (at least from the number of tools standpoint). You add a formula tool in which you add 2 columns.

1st column is to extract the last word by the formula: GetWord([field], CountWords([field]) -1)

2nd column is to extract the remaining by the formula: Replace([Page 2], GetWord([field], CountWords([field]) -1), "")

So conceptually, I get the last word to make it 1 column and the rest is the original one with the last word replaced by null.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335