"Peo Sjoblom" wrote...
One way

=LEFT(MID(A1,SEARCH("^^^",SUBSTITUTE(A1,"abc","^^ ^",
((LEN(A1)-LEN(SUBSTITUTE(A1,"abc","")))/LEN("abc"))-1))
+LEN("abc"),255),SEARCH("abc",MID(A1,SEARCH("^^^" ,
SUBSTITUTE(A1,"abc","^^^",((LEN(A1)
-LEN(SUBSTITUTE(A1,"abc","")))/LEN("abc"))-1))
+LEN("abc"),255))-1)

Text parsing in Excel is definitely not pretty!

Another way, which involves using the defined name seq referring to

=ROW(INDIRECT("1:1024"))

Also an array formula.

=MID(A1,LARGE(IF(MID(A1,seq,LEN("abc"))="abc",seq) ,3)+LEN("abc"),
SUMPRODUCT(LARGE(IF(MID(A1,seq,LEN("abc"))="abc",s eq),{2,3}),
{1,-1})-LEN("abc"))

However, udfs encapsulating calls to Windows Scripting Host regular
expression objects would be much, much better for this. Using the Subst udf
from

http://groups-beta.google.com/group/...e?dmode=source

(or http://makeashorterlink.com/?S512525CA )

the same result is given by

=subst(A1,".*abc(.*)(abc.*){2}$","$1")