+ Reply to Thread
Results 1 to 7 of 7

Need help with formula =IF(IFERROR(SEARCH

  1. #1
    Registered User
    Join Date
    03-21-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    4

    Need help with formula =IF(IFERROR(SEARCH

    I need to evaluate the values in Column A and return a "1" if valid, "0" if invalid. I have tried using the =IF(IFERROR(SEARCH formula to evaluate but am not having luck. Examples of valid values are in cell A1, A3, and A4 below. Examples of invalid values are in A2, and A5. How can I write a single formula to perform this evaluation for these different numeric strings? Thanks in advance.

    Column A
    100101
    001317072-001317072
    224-159-1233
    2241591233-250001103659
    10UTCC-10UTCC
    Last edited by ibrown9; 03-21-2013 at 04:07 PM.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Need help with formula =IF(IFERROR(SEARCH

    Why is A2 invalid and A4 valid? Can you give a more clear definition of a valid value
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Need help with formula =IF(IFERROR(SEARCH

    Hi and welcome to the forum

    apart from maybe the 1st item, none of those enties are numeric, they are all text.

    You dont say what causes a cell to be valid or not though? A formula like this will return the value you want....

    =if(A1=whatever-your-rule-is-for-being-value,1,0)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    03-21-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Need help with formula =IF(IFERROR(SEARCH

    Quote Originally Posted by ChemistB View Post
    Why is A2 invalid and A4 valid? Can you give a more clear definition of a valid value
    Hi - My apologies for lack of clarity. These values are internal key values in column A of an excel file I am trying to scrub. The business rules I have state the following are considered valid key values/format:

    6 digit numeric (A1 in forum post)
    10 digit phone number (with hyphens) (A3 in forum post)
    10 digit number "hyphen" 10 digit number (A4 in forum post)

    Invalid values are of the format:

    9 digit number "hyphen" 9 digit number (A2 in forum post)
    6 digit alpha "hyphen" 6 digit alpha (A5 in forum post)

    These 5 string formats are the only string formats in column A1 ( ~430,000 records). I was trying to use a formula to evaluate each cell value in column A, return a "1"or "0" for valid/invalid, so I can isolote all invalid values (records) for elimination.

    I've attached a sample file which is not identical to the sample values I included in the original post. The original formula I tried is: =IF(IFERROR(SEARCH("-",A1,1),0)<11,0,1). This returns a "1" only for the format - 10 digit number "hyphen" 10 digit number (A4 in forum post) however 6 digit numeric and 10 digit phone number (with hyphens) are also valid and should return a "1" but don't since these conditions are not included in the original formula I have. I think I need to add an "AND" clause but not sure how.

    I hope this helps and I hope I didn't make this more complicated that it should be. Thanks.Sample_IF_file.xlsx
    Last edited by ibrown9; 03-22-2013 at 08:35 AM.

  5. #5
    Registered User
    Join Date
    03-21-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Need help with formula =IF(IFERROR(SEARCH

    sorry, IE9 browser error caused this to post twice ...
    Last edited by ibrown9; 03-22-2013 at 08:35 AM.

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Need help with formula =IF(IFERROR(SEARCH

    Sorry I couldn't come up with something simplier but this is what I have.

    =--OR(AND(ISNUMBER(VALUE(A2)), LEN(A2)=6), AND(ISNUMBER(VALUE(REPLACE(REPLACE(A2,8,1,""),4,1,""))), LEN(A2)=12), AND(MID(A2,11,1)="-", ISNUMBER(VALUE(REPLACE(A2,11,1,"")))))
    See attachment.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    03-21-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Need help with formula =IF(IFERROR(SEARCH

    Quote Originally Posted by ChemistB View Post
    Sorry I couldn't come up with something simplier but this is what I have.

    =--OR(AND(ISNUMBER(VALUE(A2)), LEN(A2)=6), AND(ISNUMBER(VALUE(REPLACE(REPLACE(A2,8,1,""),4,1,""))), LEN(A2)=12), AND(MID(A2,11,1)="-", ISNUMBER(VALUE(REPLACE(A2,11,1,"")))))
    See attachment.
    I think this will work. Thank you for your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1