Find a text string in a column of text

[sheffslayer]

Hi All,

I'm new to excel so forgive me if I'm asking people to suck eggs. An easy one for you all im sure

Problem
Got a column of excel cells containing lots of text. Ie. "Farmer Giles has a bail of straw" in cell B1. "Farmer Thompson has a tractor" in B2. "Farmer Wright has a bull" in B3 etc.... . I have a cell with the value of 'Giles' (or another value) in it. I want to report a simple 'Yes' or 'No' if 'Giles' (or cell value) is found. Not bothered about knowing where 'Giles' is in the cell or column or if its repeated.

Would like a simple formula if possible.

Thanks in advance to all, please help if you can to save my bacon!

..Phil

 

[VoG]

Try

=IF(ISNUMBER(SEARCH("Giles",B1)),"Yes","No")

 

[T. Valko]

Hi All,

I'm new to excel so forgive me if I'm asking people to suck eggs. An easy one for you all im sure

Problem
Got a column of excel cells containing lots of text. Ie. "Farmer Giles has a bail of straw" in cell B1. "Farmer Thompson has a tractor" in B2. "Farmer Wright has a bull" in B3 etc.... . I have a cell with the value of 'Giles' (or another value) in it. I want to report a simple 'Yes' or 'No' if 'Giles' (or cell value) is found. Not bothered about knowing where 'Giles' is in the cell or column or if its repeated.

Would like a simple formula if possible.

Thanks in advance to all, please help if you can to save my bacon!

..Phil

Try this...

Data to search in column B.

D1 = Giles

=IF(COUNTIF(B:B,"*"&D1&"*"),"Yes","No")

 

[Marcelo Branco]

Assuming A1 houses the word of interest (Giles in your example) maybe

=IF(COUNTIF(B:B,"*"&A1&"*"),"Yes","No")

M.

 

[GGK]

In the above example... what if you had all of those sentences in a paragraph... in the same cell, and only wanted to extract and isolate one of those sentences in a new cell. Could you create a formula for that?

Example of what is pasted in CELL B1: "Farmer Giles has a bail of straw";in cell B1. "Farmer Thompson has a tractor" in B2;"Farmer Wright has a bull; in B3 etc.... . I have a cell with the value of 'Giles' (or another value) in it.

Lets just say i wanted to find all the data from B2;" up until the;

How could I extract the phrase Farmer Wright has a bull

So... can i create a formula that will find data that is placed BETWEEN 2 sets of constant data? ie. I will always want any data that falls between B2;" AND ;

I hope this isn't too confusing.. lol

 

[sheffslayer]

Try this...

Data to search in column B.

D1 = Giles

=IF(COUNTIF(B:B,"*"&D1&"*"),"Yes","No")

This is exactly what I was after and works perfectly! Many thanks to everyone who answered.

..Phil

 

[T. Valko]

This is exactly what I was after and works perfectly! Many thanks to everyone who answered.

..Phil

You're welcome. Thanks for the feedback!