Solved

How to search a column for a string of text?

Posted on 2011-03-11
7
314 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:kgittinger
7 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35112518
In a formula or in VBA?

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35112558
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
 
LVL 24

Expert Comment

by:jimyX
ID: 35112560
There are many options:
=IF(OR(ISERROR(SEARCH(B3,Sheet2!AA)),B3=""),"Not found","Found")
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35112691
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
 
LVL 39

Expert Comment

by:nutsch
ID: 35112705
and =COUNTIF(AA:AA,"*" & B3 &"*") will give you the number of matches you got in the column

T

0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35112737
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
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 35112771
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

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question