find a string of nth occurance & pick next 3 words

  • Thread starter Thread starter Eddy Stan
  • Start date Start date
E

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
 
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
 
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
 
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
 
Eddy Stan said:
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
Click to expand...
....

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.
 
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.
 
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
 
Back
Top