Multiple criteria in FIND formula.
VBA Telemetry pings you when your VBA projects fail
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Multiple criteria in FIND formula.

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I'm scratching my head over this one.

    In a cell, I have this text: XXX-XXX

    I want to use the FIND function to determine the position of the '-'.

    I can do that OK.

    The problem I have is, the character I'm looking for will not always be the same.
    It will be either a space, a dash (-) or a slash (/).

    Can I use the FIND function with multiple criteria to do this?

    (Lotus 123 did this very simply with the #OR# function)

    Regards,
    Andrew.

  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

    =FIND("-",SUBSTITUTE(A1,"/","-"))

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for trying, but I don't want to alter the original data.

    I want a single FIND formula to return 5 if any of the three examples below are tested.

    XXXX XXXX, or,
    XXXX-XXXX, or,
    XXXX/XXXX,


  4. #4
    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

    On 2002-04-09 07:24, Andrew70 wrote:
    Thanks for trying, but I don't want to alter the original data.

    I want a single FIND formula to return 5 if any of the three examples below are tested.

    XXXX XXXX, or,
    XXXX-XXXX, or,
    XXXX/XXXX,
    =FIND("-",SUBSTITUTE(SUBSTITUTE(A1,"/","-")," ","-"))

    This formula doesn't "alter the original data"!!!

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-09 07:47, Mark W. wrote:
    On 2002-04-09 07:24, Andrew70 wrote:
    Thanks for trying, but I don't want to alter the original data.

    I want a single FIND formula to return 5 if any of the three examples below are tested.

    XXXX XXXX, or,
    XXXX-XXXX, or,
    XXXX/XXXX,
    =FIND("-",SUBSTITUTE(SUBSTITUTE(A1,"/","-")," ","-"))

    This formula doesn't "alter the original data"!!!
    When tried your original solution, it didn't work so I looked up the SUBSTITUTE function in Excel help. When I saw that it is a text replacement command, I assumed you'd misunderstood me.

    I've since tried your revised solution and that does work.

    Thank you.

  6. #6
    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


    When tried your original solution, it didn't work so I looked up the SUBSTITUTE function in Excel help. When I saw that it is a text replacement command, I assumed you'd misunderstood me.

    I've since tried your revised solution and that does work.

    Thank you.
    I missed the "It will be either a space..." part of your original specification. It's always good to provide a complete set of test data as you did on your subsequent posting.

  7. #7
    New Member
    Join Date
    Nov 2015
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple criteria in FIND formula.

    Hi Everyone,

    Was wondering if someone could help me out with a formula related to the contents of this 15 year old thread.

    I am looking to modify the above formula so that it removes the offending part of the text string as well as finding it.

    For example, the inputs and outputs might look like this:

    "q1 Why?" -> "Why?"
    "q1 - Why?" -> "Why?"
    "q1 : Why?" -> "Why?"

    My attempt at this looks like:
    =IFERROR(RIGHT(A1,(LEN(A1)-(FIND(" - ",SUBSTITUTE(SUBSTITUTE(A1," : "," - ")," "," - "))))),"")

    The problem I'm having is that the =FIND function only returns the beginning position of the offending string portion and doesn't return the length of the statement. In the cases shown above the =FIND portion of the function will return a 3 in each case. I need it to return a 3 is case 1 and a 5 in cases 2 and 3 so that the =RIGHT function completely removes the offending parts of those strings.

    Thanks in advance for the help!

  8. #8
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    21,460
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple criteria in FIND formula.

    If you want to return everything after the final [space], - or : in a string

    =TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-"," "),":"," ")," ",REPT(" ",255)), 255))

    If space is not one of your key characters (i.e. if you want to preserve the spaces after the last key character), it becomes more complicated.

  9. #9
    New Member
    Join Date
    Nov 2015
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple criteria in FIND formula.

    Hi Mikerickson,

    Unfortunately, I do need to preserve spaces after the last key character. The cases I provided did not represent the problem correctly. See new cases below:

    "q1 Why do you think - this?" ->"Why do you think - this?"
    "q1 - Why do you think - this?" ->"Why do you think - this?"
    "q1 : Why do you think - this?" ->"Why do you think - this?"

    Thanks.

  10. #10
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    21,460
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple criteria in FIND formula.

      
    I'd build this formula with helper columns.
    First column, replace the spaces in the original string with a ~ or some other never used character.
    Next column, apply a formula like the one in post 8 (using TRIM and spaces) to the first column, getting everything after the last key character.
    Last column, replace the ~ in the second column with a space.

    Then you could combine all those columns into one monster formula.

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
  •  

 

 
DMCA.com