Consulting

Results 1 to 11 of 11

Thread: Finding 1 but not 10 or 100

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location

    Finding 1 but not 10 or 100

    Is there a way (without using VBA) to use either the Find or Search function to find the value, 1, in a cell without the formula returning true the cell actually contains, for example, 10 or 100. For example, the cell being searched may have a string, such as "1,3,5" or "4,6,10" and I'd like to be able to distinguish the 1 from the 10. (Of course, the same would be true if 11 or 12 were present in the string.)

  2. #2
    use Exact match.

  3. #3
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Search for "1,"

  4. #4
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location
    Thanks, snb. That will work if 1 is followed by a comma. Unfortunately, some cells only have a single digit, i.e., "1" or a single number, i.e., "10". Sorry I didn't make that clear in my original post.

  5. #5
    10,11,21,31,

  6. #6
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by Opv View Post
    Is there a way (without using VBA) to use either the Find or Search function to find the value, 1, in a cell without the formula returning true the cell actually contains, for example, 10 or 100. For example, the cell being searched may have a string, such as "1,3,5" or "4,6,10" and I'd like to be able to distinguish the 1 from the 10. (Of course, the same would be true if 11 or 12 were present in the string.)
    This is very difficult - I haven't found a good formula (I haven't explored fully the new O365 functions yet; what version of Excel are you using?)
    Normally, SEARCH and FIND return the position in a cell of the string sought rather than true/false.
    Would a user-defined function do (this is a function you use on a sheet, but in the background there's a macro (vba) function, but the user wouldn't necessarily know)?
    For example, I put together one (FINDNew) used like this:

    2021-10-22_135925.jpg
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location
    Quote Originally Posted by p45cal View Post
    This is very difficult - I haven't found a good formula (I haven't explored fully the new O365 functions yet; what version of Excel are you using?)
    Normally, SEARCH and FIND return the position in a cell of the string sought rather than true/false.
    Would a user-defined function do (this is a function you use on a sheet, but in the background there's a macro (vba) function, but the user wouldn't necessarily know)?
    For example, I put together one (FINDNew) used like this:

    2021-10-22_135925.jpg
    Thanks. I apologize for not providing the version of Excel I'm running. I'm running Office 2007. You are correct about the UDF option. I can go that route if it's not possible via a regular formula. EDIT: I forgot to mention that the reason the formula is returning TRUE or FALSE rather than the position of the digit is that I've constructed the test formula to do that.
    Last edited by Opv; 10-22-2021 at 07:19 AM. Reason: To make the response more complete.

  8. #8
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    The UDF:
    Function FINDNew(FindWhat, WithinCell) As Boolean
    With CreateObject("vbscript.regexp")
      .Global = True
      .IgnoreCase = False
      .MultiLine = True
      .Pattern = "\b" & FindWhat & "\b"
      FINDNew = .test(WithinCell.Value)
    End With
    End Function
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  9. #9
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location
    Quote Originally Posted by p45cal View Post
    The UDF:
    Function FINDNew(FindWhat, WithinCell) As Boolean
    With CreateObject("vbscript.regexp")
      .Global = True
      .IgnoreCase = False
      .MultiLine = True
      .Pattern = "\b" & FindWhat & "\b"
      FINDNew = .test(WithinCell.Value)
    End With
    End Function
    Thanks. It appears there is indeed no non-VBA option. Guess I'll have to go that route.

  10. #10
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    If the values in column A
    then in column B:

    PHP Code:
    =NOT(ISERR(SEARCH(",1,";","&A1&","))) 

  11. #11
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location
    Quote Originally Posted by snb View Post
    If the values in column A then in column B:
    PHP Code:
    =NOT(ISERR(SEARCH(",1,";","&A1&","))) 
    Thanks, snb!

Posting Permissions

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