Results 1 to 3 of 3

Thread: Help with a search function

  1. #1

    Help with a search function



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,
    I was hoping someone would be able to help me with a search function I am trying to perform.

    Basically, I have a document full of data and I am looking for specific errors in a specific column.

    What I'm looking for, is instances where a forward slash has been used, and there is no space directly after it. So for example "RED/BLUE" would be an error, but "RED/ BLUE" would not be an error.

    I tried using conditional formatting to show up instances of ?/? however, it see's a space as a character and brings back all correct functions also.

    I have also tried using a search function which works in principal, but once it's found the first instance it stops looking and is therefore missing errors, eg:

    =IF(ISERROR(SEARCH("/",O199)),"",IF(ISERROR(SEARCH("/ ",O199)),"CHECK SLASH",""))

    This would bring back "CHECK SLASH" on "RED/BLUE" but not on "RED/ BLUE/YELLOW"

    How on earth do I perform a formula that would only show where the error I am looking for is included, and exclude anything else?

    Help would be greatly appreciated.

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,490
    Articles
    0
    Excel Version
    Excel 2016
    Try:

    =IF((LEN(O199)-LEN(SUBSTITUTE(O199,"/ ","")))/2=LEN(O199)-LEN(SUBSTITUTE(O199,"/","")),"","CheckSlash")


  3. #3
    Quote Originally Posted by NBVC View Post
    Try:

    =IF((LEN(O199)-LEN(SUBSTITUTE(O199,"/ ","")))/2=LEN(O199)-LEN(SUBSTITUTE(O199,"/","")),"","CheckSlash")
    That's a really interesting approach - thanks for that. Much appreciated!

Posting Permissions

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