Solved

How to search a column for a string of text?

Posted on 2011-03-11
7
312 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)
Comment Utility
In a formula or in VBA?

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
Comment Utility
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
Comment Utility
There are many options:
=IF(OR(ISERROR(SEARCH(B3,Sheet2!AA)),B3=""),"Not found","Found")
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
Comment Utility
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
Comment Utility
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)
Comment Utility
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
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now