How to search a column for a string of text?

Posted on 2011-03-11
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

Question by:kgittinger
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 81

Expert Comment

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

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:


Enter by pressing CTRL+SHIFT+ENTER.

LVL 24

Expert Comment

ID: 35112560
There are many options:
=IF(OR(ISERROR(SEARCH(B3,Sheet2!AA)),B3=""),"Not found","Found")
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

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:


LVL 39

Expert Comment

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


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.

LVL 50

Accepted Solution

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.


regards, barry

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

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;…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

688 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