Finding 1 but not 10 or 100

[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.)

[arnelgp]

use Exact match.

[snb]

Search for "1,"

[arnelgp]

10,11,21,31,

[p45cal]

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:

29078

[Opv]

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.

[Opv]

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:

29078

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.

[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

[Opv]

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.

[snb]

If the values in column A
then in column B:


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

[Opv]

If the values in column A then in column B:
=NOT(ISERR(SEARCH(",1,";","&A1&","))) Thanks, snb!