collect all text between 2 words in a field

[hartmut]

Hi

I am trying to find out how to get all the text between 2 words in a field.

example 1

"The cow jumped over the moon. " -- in field phrase

between [color:blue]cow and [color:blue]moon yields

[color:red]jumped over the

example 2

"The dish ran away with the spoon."

between [color:blue]dish and [color:blue]away

yields

[color:red]ran

Thank you

[Raybaudi]

Let([

start = Position ( text ; start after ; 1 ; 1 ) + Lenght ( start after ) + 1 ;

stop = Position ( text ; stop before ; 1 ; 1 ) - 1

];

Middle ( text ; start ; stop - start )

)

where "start after" contains the first word and "stop before" contains the second word.

Another way can be:

Let([

start = Replace ( text ; 1 ; Position ( text ; start after ; 1 ; 1 ) + Lenght ( start after ) ; "" );

stop = Position ( start ; stop before ; 1 ; 1 ) - 1

];

Replace ( start ; stop ; Lenght ( start ) ; "" )

)

[comment]

I think you have some mistakes there:

Let([

start = Position ( text ; start after ; 1 ; 1 ) + Leng[color:red]th ( start after ) + 1 ;

stop = Position ( text ; stop before ; 1 ; 1 ) - 1

];

Middle ( text ; start ; stop - start )

)

Let([

start = Replace ( text ; 1 ; Position ( text ; start after ; 1 ; 1 ) + Leng[color:red]th ( start after ) [color:red]- 1 ; "" );

stop = Position ( start ; stop before ; 1 ; 1 ) - 1

];

Replace ( start ; stop ; Leng[color:red]th ( start ) ; "" )

)

[hartmut]

Thank you both

Works great.

I think this helped me alot

Dave

[Raybaudi]

I think you have some mistakes there

Hi comment

yes for Lenght, no for others !

He wants words, not spaces :

[Lee Smith]

The basic Middle Words Function can do this, either by hard code, or using a start and end fields.

Hard Code

MiddleWords ( Text ; 3 ; 3 )

MiddleWords ( Text ; 3 ; 1 )

OR, using two additional fields PositionStart & NumberWords

MiddleWords ( Text ; PositionStart ; NumberWords ) using the 3,& 3 in the two fields.

MiddleWords ( Text ; PositionStart ; NumberWords ) using the 3, & 1 in the two fields

HTH

Lee

[comment]

He wants words, not spaces

Good point. But if there are two spaces between "cow" and the next word, you will get a space too. So there is the question of 'what exactly is a word'? Suppose the text contains "cow jumped", cow-milk" and "coward". Your results will be "jumped", "milk" and "rd". IOW, this method doesn't check that prefix is a complete word in the text. I am not going to elaborate on this, I just thought it's worth mentioning.

[Raybaudi]

I just thought it's worth mentioning.

All your thoughts are worth mentioning !

I have given only a track that can be modified to satisfy more needs.

[comment]

I am afraid it would take more than just a modification. It would need to be another approach altogether, using a custom function. That's why I didn't want to get into it here, it's off topic.

[Raybaudi]

It would need to be another approach altogether, using a custom function. That's why I didn't want to get into it here, it's off topic.

Although off topic it's worth mentioning the calc using a CF:

Let([

start = WordPosition ( text ; start after ; 1 ; 1 ) + 1 ;

stop = WordPosition ( text ; stop before ; 1 ; 1 )

];

MiddleWords ( text ; start ; stop - start )

)

where WordPosition ( text ; searchWord ; start ; occurrence ) was a mine CF of May 2007

[comment]

Yes, a custom function like WordPosition() may be useful here.

As long as we are off topic, your implementation does not handle correctly the 'start' parameter:

WordPosition ( "Lorem ipsum dolor lorem" ; "lorem" ; 2 ; 1 )

should return 4, not 2.

[Raybaudi]

WordPosition ( "Lorem ipsum dolor lorem" ; "lorem" ; 2 ; 1 )

should return 4, not 2.

Oh...what a terrible bug :

I'm going to revisit that CF ! Thank you to point that wrong result.

[hartmut]

What a nice discussion. i keep learning here.

I have something to add to this which might go along with what we have been talking about here. ...

I have asked these questions because I have source text to extract answers to fields. That being said. What do I do if the words on either side don't exist in on instance?

example

"The dish ran away with the spoon"

looking for word between ran and with

Returns "away"

Example 2

"The dish ran from the spoon"

Looking for word between ran and with

but the calc gives me something else

I want it not to give me any answer when the conditions don't exist.

[Raybaudi]

...but the calc gives me something else

Hi

the calc:

Let([

start = Replace ( text ; 1 ; Position ( text ; start after ; 1 ; 1 ) + Length ( start after ) ; "" );

stop = Position ( start ; stop before ; 1 ; 1 ) - 1

];

Replace ( start ; stop ; Length ( start ) ; "" )

)

gives no answer in that case.

[fabriceN]

Hi,

I have also a CF for that, hope it helps :

http://www.briandunning.com/cf/674

Edited April 24, 2008 by Guest