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

This works for your given example

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

Mike

"Eddy Stan" wrote:

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

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


"Eddy Stan" wrote in message
...
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

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


On 3 Cze, 20:45, Eddy Stan wrote:
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

Eddy Stan wrote...
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",CHA R(127),3))+2,32767))

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

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


"Mike H" wrote:

This works for your given example

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

Mike

"Eddy Stan" wrote:

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

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.

"Rick Rothstein (MVP - VB)" wrote:

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


"Eddy Stan" wrote in message
...
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

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

"Eddy Stan" wrote:

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


"Mike H" wrote:

This works for your given example

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

Mike

"Eddy Stan" wrote:

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