Check if entire contents of one cell appear somewhere in another

[Deanomac]

Hi all,

My first post, so go easy on me.

I have an issue i have been unable to find an adequate solution to. I'll try to explain as simply as possible.

I have a worksheet with around 2000 rows and only a handful of columns. I would like to either conditionally format one of the columns or can insert another column to relay the results based on searching one cell in a row with another. However, i have found many ways of searching for a particular defined phrase within a cell, but not the entire contents of a cell within another. For example:

Text to search for	Results	Text to be searched within
Hello-How-Are-You	True	My-Friend-Said_Hello-How-Are-You
I'm-OK_Thanks	True	She-Said_I'm-OK_Thanks
Great	False	Fantastic
Good-To-Hear	False	Good_To_Hear

The reason I have added underscores and special characters is that the contents of both the cells in question may contain single words as well as words attached by special characters, and the text to be searched for may not be separated from the rest of the text in the cell being searched in.

Ideally, I would like a formula to read something like

Please Login or Register  to view this content.

so that it can be copied down the sheet and the numbers will change as it goes.

Hopefully I have explained with enough clarity for someone to be able to help me, but if not, please ask and I will provide any further information needed.

Thanks in advance.

[:) Sixthsense :)]

Everything is clear to me except what do you expect to do OR get

[Deanomac]

Everything is clear to me except what do you expect to do OR get

Knew I'd miss something off....

The column in the middle of my example is the result I'd like. It is currently blank, and I would like to have a formula that gives me a true/false or yes/no result in this column based on the cells either side of it. A formula that I can also drag down the rest of the cells and have it increment correctly so that I don't have to go in and change it for each of the lower 1999 or so cells.

Thanks

[Kevin UK]

Hi Deanomac

Assuming your data is in A1:C5. In B2 and copy down, is this what you require!

Please Login or Register  to view this content.

[Deanomac]

Hi Deanomac

Assuming your data is in A1:C5. In B2 and copy down, is this what you require!

Please Login or Register  to view this content.

Hi Kevin,

Thanks for your help. I had that query (or something similar) tried already, which didn't work. I didn't bother investigating why, as I wasn't even sure it was even going to give me the results I wanted. But as you suggested it, I though it would try to figure out why.

Long and short of it is that in the reference cells, there was a space at the end of the contents which wasn't a proper space. Once I had copied this blank space and removed it from all the reference cells, the formula sprung into life and gave me my answers.

Thanks for your help, much appreciated.

[Alf]

To add to Kevin's solution:

Please Login or Register  to view this content.

would work just as well I think.

Alf