Extracting and using Text from external sources

[Palmley]

Please can somebody help me out on this problem...
I download a text file from external stations which I cut and paste into
worksheet 2 (all into column A).
In worksheet 1, I have to enter data (taken from the downloaded file) into
various cells.
i.e.
From the downloaded text file on worksheet 2, part of the text file will
read.......Forward power is 75 watts.
I need to put the '75 watts' in to F38 on worksheet 1.
I started off by simply linking F38 to the corresponding cell on worksheet2,
but this doesn't work as each downloaded file is of different lengths.

I have tried to use FIND, VLOOKUP, SEARCH, MATCH, MID, but can’t figure it
out.

Any help and advice would be very much appreciated.Thanks.

 

[Dave R.]

Question - are you wanting to search through many cells for "watts" then
return the number before "watts", OR do you have a bunch of cells which say
"power is 75 watts", "power is 60 watts", etc. and you want to pull the "##
watts" part out?

 

[Palmley]

The second bit.. ."power is xx watts" then extract xx into a cell on
worksheet 1.

Thanks

 

[Dave R.]

This isn't the most flexible, but should return the watts from 0 to 199, as
long as the sentences had spaces in the right places.

=--MID(A1,FIND("power is",A1)+LEN("power is")+1,3)

if you want it to point at another worksheet, insert this formula into the
worksheet you'd eventually want it pointed at, then select the cell, copy
it, and go to the sheet you'd want it to end up, and paste it there - it
will change the sheet name for you.

 

[Palmley]

Dave,
Thanks for your help, I shall give it a go.

 

[Palmley]

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?

Thanks

 

[Harlan Grove]

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/microsoft.public.excel.programming/msg/1d9ae07c970566de

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)