Palmley wrote...
Nearly there, but the only way I could work it was to enter the
extact cell
location where "power is".

=MID(sheet2!A520,FIND("power is",sheet2!A520)+LEN("power is")+1,3)

I need it to search the whole sheet for "power is" as this will go
into a
differnet cell when I paste a new text dump into sheet2.

I am doing something wrong?

Much more efficient to use a udf to access the VBScript regular
expression object to parse the values. See

http://groups-beta.google.com/group/...9ae07c970566de

for one possibility. Using it, and if your text file data were in
A1:A1000, you could use the formula

=subst(INDEX(A1:A1000,MATCH("*power is *",A1:A1000,0)),
".*power is \D*(\d+ +watts).*","$1")

If you wanted to use only built-in functions, you'd need to use
something like

=MID(LEFT(INDEX(A1:A1000,MATCH("*power is *",A1:A1000,0)),
FIND(" watts",INDEX(A1:A1000,MATCH("*power is *",A1:A1000,0)),
FIND("power is ",INDEX(A1:A1000,MATCH("*power is *",A1:A1000,0))))+6),
FIND("power is ",INDEX(A1:A1000,MATCH("*power is *",A1:A1000,0)))+9,256)