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.

--
Biff
Microsoft Excel MVP


"hsg" wrote in message
...
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

On Sat, 30 Jan 2010 08:25:01 -0800, hsg
wrote:

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

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
--
Gary''s Student - gsnu201001


"hsg" wrote:

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