Link to home
Create AccountLog in
Avatar of Mike Page
Mike Page

asked on

Find word within cell & put value in adjacent

Hi Experts,

I have a large excel dataset where I want to find a specfic text and move it to the next column.
For example the text is in cell H and needs to be moved to cell I.

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

Please advice

Thanks

Mike
Avatar of AL_XResearch
AL_XResearch
Flag of United Kingdom of Great Britain and Northern Ireland image

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 ?
Avatar of Mike Page
Mike Page

ASKER

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 ")

Open 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","")

Open 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
Avatar of Norie
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")

Open 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,
ASKER CERTIFIED SOLUTION
Avatar of AL_XResearch
AL_XResearch
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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