How to Find Specific Text in a Text String

[Confused_in_Houston]

I have a text string that I want to search for in a column. At times, it can
begin at character 1 in the text string. At others it might begin at
character 20, others at character 40. In other words it's random.

How can I get Excel to affirm that the text is there (or to copy that text
to an adjacent cell); regardless of the position of my sub-string within the
cell?

Thanks

 

[muddan madhu]

try this

=MATCH("*"&B1&"*",A1:A10,0)

 

[Gary''s Student]

Say we are searching for "happiness" in column A which contains stuff like:

happiness is a warm gun
health, wealth, and happiness

In B1 enter:

=COUNTIF(A1:A1,"*happiness*") and copy down

If a cell in column B is more than zero, you will find happiness to your left.