Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: if statement

  1. #1
    Board Regular swaink's Avatar
    Join Date
    Feb 2002
    Location
    51.421818,-0.977139
    Posts
    432
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi All Does anyone know how I may do this.

    I need to be able to search a field that will contain a text value.

    I want to use the IF statement "If A1 contains "4" then do this"

    The field may contain several references seperated ie "2;3;4;5;6;7" or "1;4;7"

    Any help would be appreciated

    Kevin

    [ This Message was edited by: swaink on 2002-04-04 08:15 ]

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =IF(ISNUMBER(SEARCH("*4*",A1)),"do this","do that")

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-04 08:06, swaink wrote:
    Hi All Does anyone know how I may do this.

    I need to be able to search a field that will contain a text value.

    I want to use the IF statement "If A1 contains "4" then do this"

    The field may contain several references seperated ie "2;3;4;5;6;7"

    Any help would be appreciated

    Kevin
    could you explain at little further:

    The field may contain several references seperated ie "2;3;4;5;6;7"

    which field? A1 etc. or the IF() criteria?
    "Have a good time......all the time"
    Ian Mac

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-04 08:06, swaink wrote:
    Hi All Does anyone know how I may do this.

    I need to be able to search a field that will contain a text value.

    I want to use the IF statement "If A1 contains "4" then do this"

    The field may contain several references seperated ie "2;3;4;5;6;7" or "1;4;7"

    Any help would be appreciated

    Kevin

    [ This Message was edited by: swaink on 2002-04-04 08:15 ]
    Hi swaink:
    This should get you started ... then you can enhance it to do some further error checking:

    in cell C5 2;3;4;5

    =IF(FIND("4",C5,1),"Found It","no 4 here")

    with the result

    Found It


    Please post back if it works for you ... otherwise explain a little further and let us take it from there

    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  5. #5
    Board Regular swaink's Avatar
    Join Date
    Feb 2002
    Location
    51.421818,-0.977139
    Posts
    432
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mark, Ian

    Many thanks for your response. Mark that works a treat. And Ian It was the field that may contain the text value, sorry if i confused you.

    Thanks again

    Kevin

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-04 08:16, Mark W. wrote:
    =IF(ISNUMBER(SEARCH("*4*",A1)),"do this","do that")
    Hi Mark:
    That's neat ... looks better than mine with the find and act approach. One question, why the leading a nd trailing * -- your formula does work without the * also. TIA

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ...One question, why the leading and trailing * -- your formula does work without the * also. TIA
    I did it out of habit. Since I didn't care about the position of 4 in the string, "*4*" always returns 1 if 4 is present. This allows a summary count of "conditional flags"...

    =SUM(ISNUMBER(SEARCH("*4*",A1)),0,1,1)

    Also it's easy to change into an AND condition...

    =IF(ISNUMBER(SEARCH("*4*7*",A1)),"do this","do that")

    ...returns "do this" if an ordered list (implemented as a text string) contains both 4 and 7.


    [ This Message was edited by: Mark W. on 2002-04-04 09:22 ]

  8. #8
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Mark!
    I did expect to hear some thing along this line.

    [ This Message was edited by: Yogi Anand on 2002-04-04 09:25 ]

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-04 09:22, Mark W. wrote:
    ...One question, why the leading and trailing * -- your formula does work without the * also. TIA
    I did it out of habit. Since I didn't care about the position of 4 in the string, "*4*" always returns 1 if 4 is present. This allows a summary count of "conditional flags"...

    =SUM(ISNUMBER(SEARCH("*4*",A1)),0,1,1)

    Also it's easy to change into an AND condition...

    =IF(ISNUMBER(SEARCH("*4*7*",A1)),"do this","do that")

    ...returns "do this" if an ordered list (implemented as a text string) contains both 4 and 7.


    [ This Message was edited by: Mark W. on 2002-04-04 09:22 ]
    Excellent!!!
    "Have a good time......all the time"
    Ian Mac

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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