Loop to extract data

[satyen]

Hello

I need some help on changing my VBA code to output what can be seen in column B. The word Rooster will always exist. The line below this line all text after word Nom should be extracted.

The real file will contain lots of rows. Is the Loop most efficient way of obtaining the output?

I would appreciate if someone can get back to me as soon as possible

Thank you

[Bob Phillips]

Put this array formula in B2

=IF(ISNUMBER(A2),SUBSTITUTE(INDEX($A$1:A1,MAX(IF(ISNUMBER(SEARCH("Rooster",$A$1:A1)),ROW($A$1:A1)))+1),"Nom ",""),"")

Copy it down, then just cop, PasteSpecial>Values in column B

[satyen]

Thank-u XLD. I tried the formula didnt seem to work. Can you explain this bit?

SUBSTITUTE(INDEX($A$1:A1,MAX(IF(ISNUMBER(

I have come up with another formula, which I will post soon. Thanks

[Bob Phillips]

Did you array-enter it?