Extract given number of words from a field of comma separated words

Phill_Dettman · ‎Sep 06, 2018

Hi all.

I have a field which contains a list of 50 words/phrases that are all separated by commas, in order of importance with the most important being first. Let’s called this field ‘50W’

I have some other fields that I want to auto populate/extract from ‘50W’. One of them is called ‘1W’ which should extract only the first word/phrase, meaning everything in front of the first comma.

Then I have another field called ‘10W’ which i want to extract the first 10 words/phrases, everything in front of the 10th comma.

It would be great if this was possible but I can’t seem to do it whatever i try.

Does anyone have any ideas I can try?

Many thanks.

Phill

Elias_Gomez_Sai · ‎Sep 06, 2018

I think a Formula with FIND() and LEFT() is the way to go, but I can’t find the way to get the 10W value :thinking:

Phill_Dettman · ‎Sep 06, 2018

Thanks Elias.

Both of these appear to require a predefined character count, which is fine when the words/phrase have known or identical lengths, but in my case the words/phrases are different in every case.

For example, here would be the first 5 words/phrases of the field ‘50W’:

one, two words, three words, four words, five words, etc

Some of them may well contain entries with the same number of words but this doesn’t matter.

Thank you for your ideas.

Phill

W_Vann_Hall · ‎Sep 06, 2018

I created a similar routine to extract hashtags; maybe you can modify it accordingly, 'cause I never want to see it again. :winking_face:

Phill_Dettman · ‎Sep 06, 2018

My goodness!! That’s pretty intense. I don’t even want to think how that it took you to work all that out…

Needless to say, for my 50 words, I won’t be attempting that for only 600 records… it’ll be quicker to do it all manually for sure :slightly_smiling_face:

Really well done though. Nice work.

W_Vann_Hall · ‎Sep 06, 2018

Oh, your task is relatively simple — assuming the variation are all ‘the first X’ words — since all it takes is a LEFT() and an ugly SWITCH() statement.

The following assumes (1) your list of fifty words is in the field {50W}; (2) the field that determines whether to use the '1W' or '2W' or '10W' formula is called, simply, {W}, and (3) you need variations from '1W' through '10W'.

Here’s the formula:

LEFT(
    {50W},
    SWITCH(
        W,
        '1W',FIND(',',{50W})-1,
        '2W',FIND(',',{50W},FIND(',',{50W})+1)-1,
        '3W',FIND(',',{50W},FIND(',',{50W},FIND(',',{50W})+1)+1)-1,
        '4W',FIND(',',{50W},FIND(',',{50W},FIND(',',{50W},FIND(',',{50W})+1)+1)+1)-1,
        '5W',FIND(',',{50W},FIND(',',{50W},FIND(',',{50W},FIND(',',{50W},FIND(',',{50W})+1)+1)+1)+1)-1,
        '6W',FIND(',',{50W},FIND(',',{50W},FIND(',',{50W},FIND(',',{50W},FIND(',',{50W},FIND(',',{50W})+1)+1)+1)+1)+1)-1,
        '7W',FIND(',',{50W},FIND(',',{50W},FIND(',',{50W},FIND(',',{50W},FIND(',',{50W},FIND(',',{50W},FIND(',',{50W})+1)+1)+1)+1)+1)+1)-1,
        '8W',FIND(',',{50W},FIND(',',{50W},FIND(',',{50W},FIND(',',{50W},FIND(',',{50W},FIND(',',{50W},FIND(',',{50W},FIND(',',{50W})+1)+1)+1)+1)+1)+1)+1)-1,
        '9W',FIND(',',{50W},FIND(',',{50W},FIND(',',{50W},FIND(',',{50W},FIND(',',{50W},FIND(',',{50W},FIND(',',{50W},FIND(',',{50W},FIND(',',{50W})+1)+1)+1)+1)+1)+1)+1)+1)-1,
       '10W',FIND(',',{50W},FIND(',',{50W},FIND(',',{50W},FIND(',',{50W},FIND(',',{50W},FIND(',',{50W},FIND(',',{50W},FIND(',',{50W},FIND(',',{50W},FIND(',',{50W})+1)+1)+1)+1)+1)+1)+1)+1)+1)-1
        ))

And here’s a demonstration base showing how it all works together.

W_Vann_Hall · ‎Sep 06, 2018

Yeah, @Katherine_Duh, I have to say I’m really enjoying that SWITCH() statement. :winking_face:

Phill_Dettman · ‎Sep 07, 2018

@W_Vann_Hall this is VERY impressive. I really appreciate you taking the time to work on this. I love it. Although i’m still a bit new to all this so it’s stretching some of my brain power understanding what is happening.

I’ve had a little play around, and wonder if it might be possible to expand the functionality of your idea to be able to call out a given number of keywords on a record by record basis.

In this i have created a base to demonstrate what my ‘ideal world’ base would be able to do. ‘50W’ is different for every record and doesn’t always contain exactly 50 words, it could be anything from 0-50 or more. although we can assume i will not exceed 50 words.

The count col just shows how many words are in 50W for reference.

Then in the ‘how many words to show’ col, i put in a value from 1-50.

‘Ideal Result’ shows what i would like it to show.

‘Extract’ is the magic bit that creates the contents of Ideal Result automatically without me having to copy paste as i did with the contents of ‘Ideal Result’ :slightly_smiling_face:

there might be an occasion where i want to see 15 words, but currently i might have less than that entered in ‘50W’ . In this case it can just show as many as are available and this will update as 50W is populated.

Do you think this is doable or am I simply dreaming?

Many thanks.

Phill

W_Vann_Hall · ‎Sep 07, 2018

Actually, the formula I provided took that functionality into consideration. It starts with

LEFT(
    {50W},
    SWITCH(
        W,
        '1W',FIND(',',{50W})-1,
        '2W',FIND(',',{50W},FIND(',',{50W})+1)-1,
           [...]

In your example base, you could replace W with {HowManyWordsToShow?} and '1W' with 1, '2W' with 2, and so on. That would let the number of words extracted to be selected on a per-record basis.

That leaves the chore of creating a SWITCH() statement supporting the extraction of one word up through whatever maximum number of words might be desired. The issue is Airtable’s current lack of any sort of looping mechanism. Without such functionality, the only way to perform an action against each of an indeterminate number of instances is to build in explicit support for every possible number of instances one might encounter and then select the applicable branch.

This limitation also determines how to implement your 'extract this many words up to the maximum words currently in 50W’ algorithm. Actually, since when asked to extract more words than are currently defined, the routine seemingly fails by gobbling all of 50W, you might not have to change anything — but if you’d rather have a solution based on intention rather than circumstance, your extraction routines could include something along these lines:

IF(
    {Count}>={HowManyWordsToShow?},
    {HowManyWordsToShow?},
    {Count}
    )

Phill_Dettman · ‎Sep 08, 2018

@W_Vann_Hall

Absolutely genius. I love it… especially your use of the word ‘chore’ for creating the SWITCH() statement… that took me about half an hour, and it works VERY well with only one issue. …

If i request the exact same amount of keywords, as contained in 50W (and consequently the Count field), the Extract result field is blank and whatever i change i can’t seem to fiddle it to work.

Any ideas?

Thank you.

Phill