Find word within cell & put value in adjacent

Hi Mike,

Can you please clarify your question. The formula you have provided works to the job you are asking advice about - so what is the issue ? Making the text to search dynamic perhaps ?

Hi Al,

i tried it but it doesn't seem to copy to the next column. I foukd the example here

http://ccm.net/forum/affich-46323-find-word-within-cell-put-value-in-adjacent

judt wonder if there was another way.

Thanks

ok found my error

=IF(ISERROR(SEARCH("*SlideOpened*",H2,1)),"","SlideOpened ")

Good news ! It is nearly always some small mistake you don't see.

can I ask..

what if I have two or more 'items' that i need to copy across.. how would I do it

=IF(ISERROR(SEARCH("*SlideOpened*",H2,1)),"","SlideOpened ")

for exampel
=IF(ISERROR(SEARCH("*SlideOpened*,*CorrectAnswer*",H2,1)),"","SlideOpened, CorrectAnswer ")

If you wanted to search one cell for either the word 'car' or 'pipe' then you would use:

=IF(AND(ISERROR(SEARCH("car",H9)),ISERROR(SEARCH("pipe",H9)))=TRUE,"","SlideOpened ")

Select allOpen in new window

If you wanted a more extensible formula that you can add multiple words to and have it search for any in the on cell:

=IF(COUNT(SEARCH({"car","source","is"},H9)),"SlideOpened","")

Select allOpen in new window

just to be clarify please
on Cell H, I may have

        H
SlideOpened
Action
Close

how do I move them to cell B

Do you have a list of the values you want to search for?

HI,

Thiis is the result of this formular

=IF(COUNT(SEARCH({"SlideOpened","CorrectAnswer"},H2,1)),"SlideOpened","CorrectAnswer")

H                       I
SlideOpened      SlideOpened
SlideOpened      SlideOpened
1397.25              CorrectAnswer

Mike I think you have misunderstood

=IF(COUNT(SEARCH({"SlideOpened","CorrectAnswer"},H2,1)),"SlideOpened","CorrectAnswer")

Select allOpen in new window

makes no sense because what the 'COUNT(SEARCH({"SlideOpened","CorrectAnswer"},H2,1))' is saying is 'If you find 'SliderOpened' OR 'CorrectAnswer' in cell H2 then return TRUE. So you entire formula is saying is 'If you find 'SlideOpened' OR 'CorrectAnswer' then return 'SlideOpened' but if neither of those values are in the cell H2 then return 'CorrectAnswer'.

Based on the last part of your formula I think what you are trying to find is: if the test cell (H2) contains one of the test words then return only that test word in to the column I cell. Is that correct ? Because that is not what I understood from your initial request.

Can there only ever be one of the test values in the test cell ?

I will have to have a deeper think about how to do this in a cell formula. It would be straightforward in VBA but a formula is another thing.

You see the problem is having multiple test values. As they can each have different lengths there is no obvious way to identify which of the test words were found and therefore determine the length of text to extract.

if the test cell (H2) contains one of the test words then return only that test word in to the column I cell.

Yes this is what I am looking for

Yes that is basically what I am thinking. Have 'hidden helper' columns that enable you a) to test for multiple words b) allow you to change those words dynamically.

I will have to look at this after work but I really very busy at the moment.

The best solution would be to create a User Defined Function in VBA to do this,

No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Accept: AL_XResearch (https:#a42100995)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

broomee9
Experts-Exchange Cleanup Volunteer