search for a particular string in cells

[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

 

[T. Valko]

One way...

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

Note that this is not case sensitive. That formula will evaluate "atd" and
"ATD" as being equal.

 

[Lars-Åke Aspelin]

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

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

 

[Gary''s Student]

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