In a text string I need to identify the word number that a particular word is in the text field.

For example.

"The cat sat on the mat". I need to know that the word "sat" is word no.3 etc.

But I cant see which functions to use.

Can anyone help. Please.

John

Hi

this is a way:

PatternCount ( Substitute ( Replace ( text ; Position ( text ; "sat" ; 1 ; 1 ) ; Length ( text ) ; "xxx" ) ; " " ; "xxx" ) ; "xxx" )

EDIT: NOT a good way; I'll search a better one !

Edited September 11, 200619 yr by Guest

This is a bit better ...

Let([

adjString1 = TrimAll ( Substitute ( text ; ¶ ; " " ) ; 1 ; 1 );

adjString2 = Replace ( adjString1 ; Position ( adjString1; " sat " ; 1 ; 1 ) ; Length ( adjString1 ) ; " sat" );

adjString3 = Substitute ( adjString2 ; " " ; ¶ )

];

Case(

PatternCount ( adjString1 ; " sat " ); ValueCount ( adjString3 );

""

)

)

That doesn't seem very good either. What if the text is:

Sat the cat on the mat.

Or:

The cat sat, on the mat.

Or:

The cat sat? on the mat.

Or:

The cat "sat" on the mat.

I believe a recursive custom function is required here - here's a simple one that should do the job:

WordPositionSimple ( text ; searchWord ; startingWord )

Let ( [

thisWord = MiddleWords ( text ; startingWord ; 1 )

] ;

Case (

thisWord = searchWord ; startingWord ;

startingWord < WordCount ( text ) ; WordPositionSimple ( text ; searchWord ; startingWord + 1 ) ;

0

)

)

Note:

1. This always find the first occurrence of searchWord, at or after startingWord;

2. Just like the Position() function, the result is the position from the start of the entire text - regardless of the startingWord parameter.

Yes, comment, you are right...

that was *only* a bit better !

But this must work:

Let([

t1 = " " & TrimAll ( Substitute ( text ; ¶ ; " " ) ; 1 ; 1 ) & " ";

t2 = Filter ( t1 ; " abcdefghjkilmnopqrstuvwxyzABCDEFGHJKILMNOPQRSTUVWXYZ" );

t3 = Replace ( t2 ; Position ( t2 ; " sat " ; 1 ; 1 ) ; Length ( t2 ) ; " sat" )

];

Case(

PatternCount ( t2 ; " sat " ) ; WordCount ( t3 );

""

)

)

To me, that's still too specific. What if my text is not in English? I might be looking for "Søren" or "resumé" or "Надежда". Or, I could be looking for "cat" in "I had a cat-nap". And so on...

Ok, Ok... :