Morning

I would like to see if a cell contains a specific word in a string & if it does write the specific word in a new cell, I can do this easily with a data filter text contains but I want to do it with a formula.

Any help appreciated.

Thanks

Freddy

I don't think you can without writing your own function.

chergh

thanks for info, i'll use access I know where I am there.

Cheers

Freddy

wouldn't this work in the new cell?:=if(instr(old cell number, "string") > 0, "string", "")

No, instr is only available in vba.

I'm getting the idea that you don't like me chergh. I will stay away. :)

Yeah explaining why your idea wouldnt work was very nasty of me.

if you have

Freddy67 wants answers.

in cell A1,

the following should extract the word "wants" -

=MID(A1,SEARCH("wants",A1),SEARCH(" ",A1,SEARCH("wants",A1))-SEARCH("wants",A1))

peace,
l

No, instr is only available in vba.

SEARCH is the Excel Function equivalent of INSTR.

example
A1: This is some text
A2: some
A3: =IF(ISERROR(SEARCH(A2,A1)),"Not Found",A2)

The cell A3 will return "some".
If the value in A2 can't be found, then A3 will return "Not Found".
You can change that to whatever you want.