I have a worksheet with about 4000+ users (rows).
I need to filter out the data containing one of the four codes starting with
A,D,L, or R.
How can I create a formula that will search for the codes in the column
starting with the above letters and return a TRUE if it is there and a FALSE
if it is not.
There are 8 columns per row. I just need to search in one column for the
above text.

Thanks!

=or(a2={"a","d","l","r"})

is one way.



NeedExcelHelp07 wrote:

I have a worksheet with about 4000+ users (rows).
I need to filter out the data containing one of the four codes starting with
A,D,L, or R.
How can I create a formula that will search for the codes in the column
starting with the above letters and return a TRUE if it is there and a FALSE
if it is not.
There are 8 columns per row. I just need to search in one column for the
above text.

Thanks!

--

Dave Peterson

ok but what if the codes are written like a7?
How can I write the formula so it searches for the "a" even though the 7 is
in the cell as well?

"Dave Peterson" wrote:

=or(a2={"a","d","l","r"})

is one way.



NeedExcelHelp07 wrote:

I have a worksheet with about 4000+ users (rows).
I need to filter out the data containing one of the four codes starting with
A,D,L, or R.
How can I create a formula that will search for the codes in the column
starting with the above letters and return a TRUE if it is there and a FALSE
if it is not.
There are 8 columns per row. I just need to search in one column for the
above text.

Thanks!

--

Dave Peterson

One way, in B2, copied down:
=SUMPRODUCT(--ISNUMBER(SEARCH({"a","d","l","r"},A2)))0
Replace SEARCH with FIND if you need it case sensitive. SEARCH is not case
sensitive.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"NeedExcelHelp07" wrote:
ok but what if the codes are written like a7?
How can I write the formula so it searches for the "a" even though the 7 is
in the cell as well?

=SUM(COUNTIF(A2,{"*a*","*D*","*L*","*R*"}))0
or
=SUM(COUNTIF(A3,"*"&{"a","D","L","R"}&"*"))0

This actually will test for 9A7 where A is anywhere in the cell.




NeedExcelHelp07 wrote:

ok but what if the codes are written like a7?
How can I write the formula so it searches for the "a" even though the 7 is
in the cell as well?

"Dave Peterson" wrote:

=or(a2={"a","d","l","r"})

is one way.



NeedExcelHelp07 wrote:

I have a worksheet with about 4000+ users (rows).
I need to filter out the data containing one of the four codes starting with
A,D,L, or R.
How can I create a formula that will search for the codes in the column
starting with the above letters and return a TRUE if it is there and a FALSE
if it is not.
There are 8 columns per row. I just need to search in one column for the
above text.

Thanks!

--

Dave Peterson


--

Dave Peterson

Thanks Max. It worked!

"Max" wrote:

One way, in B2, copied down:
=SUMPRODUCT(--ISNUMBER(SEARCH({"a","d","l","r"},A2)))0
Replace SEARCH with FIND if you need it case sensitive. SEARCH is not case
sensitive.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"NeedExcelHelp07" wrote:
ok but what if the codes are written like a7?
How can I write the formula so it searches for the "a" even though the 7 is
in the cell as well?

welcome. do check out Dave's response for alternatives
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"NeedExcelHelp07" wrote in
message ...
Thanks Max. It worked!