Solved

How to search a column for a string of text?

Posted on 2011-03-11
7
313 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

895 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

16 Experts available now in Live!

Get 1:1 Help Now