How to search a column for a string of text?

How to search a column for a string of text?  I want to search for the value in b3, in column AA on sheet2.  The value of b3 could be anywhere withing the string of data housed in column AA

kgittingerAsked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
If you want to find the position of the text in the column then MATCH with a wildcard would be a better option than using SEARCH, i.e.

=MATCH("*"&B3&"*",sheet2!AA:AA,0)

regards, barry
0
 
zorvek (Kevin Jones)ConsultantCommented:
In a formula or in VBA?

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
This array formula will return the row of the first occurrence of the sub-string:

   =MIN(IF(ISERROR(SEARCH("*text*",A1:A100)),"",ROW(A1:A100)))

Enter by pressing CTRL+SHIFT+ENTER.

Kevin
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
jimyXCommented:
There are many options:
=IF(OR(ISERROR(SEARCH(B3,Sheet2!AA)),B3=""),"Not found","Found")
0
 
zorvek (Kevin Jones)ConsultantCommented:
Correction - you don't need wildcard characters when using the SEARCH function to find a simple substring:

   =MIN(IF(ISERROR(SEARCH("text",A1:A100)),"",ROW(A1:A100)))

Kevin
0
 
nutschCommented:
and =COUNTIF(AA:AA,"*" & B3 &"*") will give you the number of matches you got in the column

T

0
 
zorvek (Kevin Jones)ConsultantCommented:
Can you provide more information as to what you are trying to do?

Your question is somewhat vague and thus far has prompted three very different solutions. Experts will continue to post varying solutions that will ultimately generate more confusion than clarity.

Kevin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.