If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Search / Find Function
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. |
#2
|
|||
|
|||
Search / Find Function
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. |
#3
|
|||
|
|||
Search / Find Function
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. |
#4
|
|||
|
|||
Search / Find Function
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. |
#5
|
|||
|
|||
Search / Find Function
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. |
#6
|
|||
|
|||
Search / Find Function
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. |
#7
|
|||
|
|||
Search / Find Function
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. |
Thread Tools | |
Display Modes | |
|
|