+ Reply to Thread
Results 1 to 8 of 8

A couple of questions regarding SEARCH

  1. #1
    Registered User
    Join Date
    12-03-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    19

    A couple of questions regarding SEARCH

    Hello, I have thousands of rows in column A, and each row is a string. I need to choose only those rows that do contain a ":" but do not contain one or more "?" in their respective strings. For example, the first row contains the string "Davis D.R. 1991. see Rami Davis R. 1991." It does not contain "?" but neither does it contain ":". So I would not be choosing it. I tried the following:

    I created a new column (B) with the formula: =IF(SEARCH(":",A2),"KEEP","NOKEEP"), but it returned "#VALUE!". Not sure why. Based on a post in this forum, I then changed my formula to =IF(ISNUMBER(SEARCH(":",A2)),"KEEP","NOKEEP"), and that worked (it returned "NOKEEP" correctly, because the string does not contain ":").

    I then created a second column (C) with the formula =IF(ISNUMBER(SEARCH("~?",A2)),"NOKEEP","OTHER") to determine if the string contains "?". And that worked as well.

    And finally a third column (D) with the formula =AND(B2="KEEP", C2="OTHER"), and it returned TRUE or FALSE for each row (for example, it correctly returned FALSE for the first row). I then sorted all the rows to get only those that returned TRUE in this column.

    My questions are:

    1. I don't see the relevance of "ISNUMBER" in the formula in column D. The formula is searching a string in Column A.
    2. There must be an easier way of accomplishing the above in one formula. Is there?

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,937

    Re: A couple of questions regarding SEARCH

    Hi serge57. Welcome tot the forum.

    SEARCH will return either a number or a #VALUE! error. ISNUMBER returns TRUE/FALSE. Since IF is expecting something in its first argument that can be evaluated as TRUE/FALSE you need to wrap ISNUMBER around the SEARCH function.

    Does that help?
    Dave

  3. #3
    Registered User
    Join Date
    12-03-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    19

    Re: A couple of questions regarding SEARCH

    Thank you, Dave. That was indeed my first question to the forum. Thanks also for the quick reply. Appreciate it. So if I understand your answer, since IF expects a TRUE/FALSE reply but SEARCH only returns
    a number or an error (#VALUE!), one needs to add ISNUMBER so that the result is TRUE or FALSE. In this context, of course the result will always be FALSE because the column only contains strings. Will it
    make a difference if the result happened to be TRUE? Also, if that is the reason for using ISNUMBER, perhaps one can use any function that results in a TRUE/FALSE answer here?

    Also, I wonder if you have any response to my second question - is there a shorter (smarter?) way of achieving what I need rather than the three-step process I used?

    Thanks,

    Serge

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,937

    Re: A couple of questions regarding SEARCH

    In this context, of course the result will always be FALSE because the column only contains strings.
    No. The fact that the column only contains strings is not relevant. What is relevant is whether or not the search term is present in the strings. If the string in question contains a ":" or "?" then it will return the character position of that search term ie a number. If the search term does not exist then of course ISNUMBER returns FALSE. Given what you describe the actual position is also not an issue in this case ... just the fact that a number return indicates the search term is present in the string.

    Will it make a difference if the result happened to be TRUE?
    Well yes. According to your formula IF would return "KEEP".

    Also, if that is the reason for using ISNUMBER, perhaps one can use any function that results in a TRUE/FALSE answer here?
    Well yes again. But what ever is used must be a logical choice ... ie the right tool for the job. But given what you have so far another way does not spring to mind ... certainly not a shorter or simpler one.

    Also, I wonder if you have any response to my second question - is there a shorter (smarter?) way of achieving what I need rather than the three-step process I used?
    Without a representative Excel workbook upload I am not willing to commit beyond saying "Yes ... maybe." I would need something more specific to work with.

    If you are not familiar with how to upload a file to the forum follow these instructions:

    To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

    be sure to desensitize the data
    • click “Go Advanced” (next to Post Quick Reply – bottom right),
    • scroll down until you see “Manage Attachments”, click that,
    • click “Browse”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”
    The file name will appear at the bottom of your reply.

  5. #5
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    4,795

    Re: A couple of questions regarding SEARCH

    Try this:

    =AND(ISNUMBER(SEARCH(":",A2)),ISERROR(SEARCH("~?",A2)))

    or:

    =AND(COUNTIF(A2,"*:*"),NOT(COUNTIF(A2,"*~?*")))

  6. #6
    Registered User
    Join Date
    12-03-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    19

    Re: A couple of questions regarding SEARCH

    Thank you, Dave, for the point-by-point answers. Really appreciate your help.

    And thank you, Phuocam. Both your solutions worked! They seem very clever, but I still need to parse them to understand exactly what they are doing. But that's terrific! Thanks again.

    Now, I'd like to acknowledge both of you by giving you reputation points. I need to figure out how to do that.

    Thanks again.

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,937

    Re: A couple of questions regarding SEARCH

    You are most welcome. Glad to help. Thank you for the feedback and rep.

    One last thing:

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  8. #8
    Registered User
    Join Date
    12-03-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    19

    Re: A couple of questions regarding SEARCH

    Just marked it as Solved. Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. A couple of questions
    By kingdt in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-26-2007, 07:41 AM
  2. a couple of questions
    By moshmoshon in forum Excel General
    Replies: 2
    Last Post: 08-31-2007, 05:31 PM
  3. Just a couple questions....
    By Punk0Rama in forum Excel General
    Replies: 1
    Last Post: 09-10-2006, 11:35 AM
  4. A couple of questions...
    By Punk0Rama in forum Excel - New Users/Basics
    Replies: 0
    Last Post: 09-08-2006, 07:36 PM
  5. Couple of Questions
    By Toysforfids in forum Excel General
    Replies: 3
    Last Post: 08-21-2006, 11:45 AM
  6. A couple of questions...
    By littlegreenmen1 in forum Excel General
    Replies: 0
    Last Post: 06-10-2005, 04:40 PM
  7. couple of newbie questions
    By alichis in forum Excel General
    Replies: 12
    Last Post: 05-04-2005, 08:55 AM
  8. Couple more questions...
    By Poor microsoft user in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 04-27-2005, 02:47 PM

Tags for this Thread

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