I would like Excel to count all my NY customers; e.g., Search("NY",B2) if
the state code NY is found in a lengthy text field containing a name,
address, comma separators, etc.

The search works fine if NY is in the searched cell, but returns #Value if
not. I can't perform any logical function on the result if it returns #Value.
How do I get around that?

Thank you.

Hi,
Please post your formula

"Harry C." wrote:

I would like Excel to count all my NY customers; e.g., Search("NY",B2) if
the state code NY is found in a lengthy text field containing a name,
address, comma separators, etc.

The search works fine if NY is in the searched cell, but returns #Value if
not. I can't perform any logical function on the result if it returns #Value.
How do I get around that?

Thank you.

Here it is:

=IF(SEARCH("NY",G884),1,0) (I've tried the same with FIND).

Here is a sample cell: Joe Smith, 7951 Dorcas Street, Philadelphia, PA,
19111, United States

You'll see that he is not from NY. When I apply either the FIND or SEARCH to
such a cell, Excel returns #VALUE! and I can't do anything with that.

Thank you.

"Eduardo" wrote:

Hi,
Please post your formula

"Harry C." wrote:

I would like Excel to count all my NY customers; e.g., Search("NY",B2) if
the state code NY is found in a lengthy text field containing a name,
address, comma separators, etc.

The search works fine if NY is in the searched cell, but returns #Value if
not. I can't perform any logical function on the result if it returns #Value.
How do I get around that?

Thank you.

Try something like this:

=COUNT(SEARCH("NY",B2))

Or, another way:

=COUNTIF(B2,"*NY*")

Those will return either 1 or 0. 1 = cell contains NY, 0 = cell does not
conatin NY.

**CAVEAT**

Both of those formulas will return "false positives" if the string NY
appears anywhere within the cell. For example:

B2 = Albany, Georgia

Both formulas will find the "ny" in Albany.

--
Biff
Microsoft Excel MVP


"Harry C." <Harry wrote in message
...
I would like Excel to count all my NY customers; e.g., Search("NY",B2) if
the state code NY is found in a lengthy text field containing a name,
address, comma separators, etc.

The search works fine if NY is in the searched cell, but returns #Value if
not. I can't perform any logical function on the result if it returns
#Value.
How do I get around that?

Thank you.

Hi
try

=IF(ISERR(SEARCH("NY",G884)),0,SEARCH("NY",G884))

if this helps please click yes, thanks

"Harry C." wrote:

Here it is:

=IF(SEARCH("NY",G884),1,0) (I've tried the same with FIND).

Here is a sample cell: Joe Smith, 7951 Dorcas Street, Philadelphia, PA,
19111, United States

You'll see that he is not from NY. When I apply either the FIND or SEARCH to
such a cell, Excel returns #VALUE! and I can't do anything with that.

Thank you.

"Eduardo" wrote:

Hi,
Please post your formula

"Harry C." wrote:

I would like Excel to count all my NY customers; e.g., Search("NY",B2) if
the state code NY is found in a lengthy text field containing a name,
address, comma separators, etc.

The search works fine if NY is in the searched cell, but returns #Value if
not. I can't perform any logical function on the result if it returns #Value.
How do I get around that?

Thank you.

Here is a sample cell: Joe Smith, 7951 Dorcas Street,
Philadelphia, PA, 19111, United States

WRT my other post, try it like this:

=COUNTIF(B2,"* NY, *")


--
Biff
Microsoft Excel MVP


"Harry C." wrote in message
...
Here it is:

=IF(SEARCH("NY",G884),1,0) (I've tried the same with FIND).

Here is a sample cell: Joe Smith, 7951 Dorcas Street, Philadelphia, PA,
19111, United States

You'll see that he is not from NY. When I apply either the FIND or SEARCH
to
such a cell, Excel returns #VALUE! and I can't do anything with that.

Thank you.

"Eduardo" wrote:

Hi,
Please post your formula

"Harry C." wrote:

I would like Excel to count all my NY customers; e.g., Search("NY",B2)
if
the state code NY is found in a lengthy text field containing a name,
address, comma separators, etc.

The search works fine if NY is in the searched cell, but returns #Value
if
not. I can't perform any logical function on the result if it returns
#Value.
How do I get around that?

Thank you.

Try

=COUNTIF(A1,"*NY*")

--
If this post helps click Yes
---------------
Jacob Skaria


"Harry C." wrote:

I would like Excel to count all my NY customers; e.g., Search("NY",B2) if
the state code NY is found in a lengthy text field containing a name,
address, comma separators, etc.

The search works fine if NY is in the searched cell, but returns #Value if
not. I can't perform any logical function on the result if it returns #Value.
How do I get around that?

Thank you.