Thread: Finding 1 but not 10 or 100

1. 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.)  Reply With Quote

2. use Exact match.  Reply With Quote

3. Search for "1,"  Reply With Quote

4. 10,11,21,31,  Reply With Quote

5. Originally Posted by Opv 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  Reply With Quote

6. 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.  Reply With Quote

7. Originally Posted by p45cal 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.  Reply With Quote

8. 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  Reply With Quote

9. Originally Posted by p45cal 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.  Reply With Quote

10. If the values in column A
then in column B:

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

11. Originally Posted by snb If the values in column A then in column B:
PHP Code:
=NOT(ISERR(SEARCH(",1,";","&A1&",")))
Thanks, snb!  Reply With Quote Posting Permissions

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