Search / Find Function

[Harry C.]

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.

 

[Harry C.]

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.

 

[T. Valko]

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.

 

[Eduardo]

Hi
try

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

if this helps please click yes, thanks

 

[T. Valko]

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, *")

 

[Jacob Skaria]

Try

=COUNTIF(A1,"*NY*")