find a string of nth occurance & pick next 3 words

[Eddy Stan]

Hi
I need to find a string "pv" occurring 3rd time in a cell and pick 3
subsequent words from the cell
example : the following sentense is there in c5 cell
111111 pv 222222 pv 3333333 44444 55555 pv 66666 pv 7777 pv 8888
the result : I need a function in cell D5 to return
it must be like : find(pv, c5, 3rd occurancce, next 3 words)
and give result 66666 pv 7777 pv 8888
i know we cannot use find() but i need a function something like that and
NOT a macro please.

Eddy Stan

 

[Mike H]

This works for your given example

=TRIM(MID(C5,FIND("pv",C5,FIND("pv",C5,FIND("pv",C5,1)+1)+1)+2,LEN(C5)))

Mike

 

[Rick Rothstein \(MVP - VB\)]

A little clarification please... What do you mean by "next 3 words"? The
suggested answer you posted looks like 5 words to me... 5 pieces of text
separated by 4 blanks. Why is the pv included in the found result, but not
counted?

Rick

 

[Jarek Kujawa]

presuming yr text is in A1 try to put the following in A2:

=RIGHT(A1,LEN(A1)-FIND("pv";A1;1)-2)

then copy down to A2, A3 and A4

A$ should show the required result

 

[Harlan Grove]

I need to find a string "pv" occurring 3rd time in a cell and pick 3
subsequent words from the cell
example : the following sentense is there in c5 cell
111111 pv 222222 pv 3333333 44444 55555 pv 66666 pv 7777 pv 8888
the result : I need a function in cell D5 to return
it must be like : find(pv, c5, 3rd occurancce, next 3 words)
and give result 66666 pv 7777 pv 8888

....

So pv is a word separator and the space characters are just padding?
Otherwise 66666 pv 7777 pv 8888 is 5 words with pv being two of those
words.

Easy enough to remove the substring before the 3rd pv.

=TRIM(MID(A1,FIND(CHAR(127),SUBSTITUTE(A1,"pv",CHAR(127),3))+2,32767))

Much more difficult to restrict this to the next 3 words ignoring pv.

 

[Eddy Stan]

Hi Mike,
Thank you and it helped me and
saved lot of my time.

 

[Eddy Stan]

Hi Rick,
Thanks for your reply. You are correct it is 5 pieces.
But Mike's formula helped me, saving 3 hrs at least and i edited 15 recs
from 700 which showed more than 3 pieces.

 

[Mike H]

Hi,

I'm pleased that worked but it's a bit of a cheat because it doesn't really
return the 'next 3 words' it simply returns what's left of the string

Mike