search for a particular string in cells

  • Thread starter Thread starter hsg
  • Start date Start date
H

hsg

Is there any function which will search for a specific combination of letters
in
a range of data. Say data is in column A(A1 to A1000).

How to find how many cell entries are having "atd" pattern in them. Order is
fixed, but the letters may be at stat, end or mid of the entire string in
the cell

thanks
 
One way...

=COUNTIF(A1:A1000,"*atd*")

Note that this is not case sensitive. That formula will evaluate "atd" and
"ATD" as being equal.
 
Is there any function which will search for a specific combination of letters
in
a range of data. Say data is in column A(A1 to A1000).

How to find how many cell entries are having "atd" pattern in them. Order is
fixed, but the letters may be at stat, end or mid of the entire string in
the cell

thanks
Click to expand...


Try this formula:

=SUM(1-ISERROR(FIND("atd",A1:A1000)))

Note: This is an array formula that must be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER.

Use SEARCH instead of FIND if you don't want the matching to be case
sensitive.

Hope this helps / Lars-Åke
 
In B1 enter:

=IF(LEN(A1)=LEN(SUBSTITUTE(A1,"atd","")),0,1) and copy down

Autofilter on col B for 1 to reveal the rows in which column A contains atd
 
Back
Top