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

use Exact match.

Search for "1,"

10,11,21,31,

Quote:

---

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:

Attachment 29078

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.

Quote:

---

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:

Attachment 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.

The UDF:

Code:

---

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

---

Quote:

---

Originally Posted by p45cal

The UDF:

Code:

---

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.

If the values in column A
then in column B:

PHP Code:

---

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


---

Quote:

---

Originally Posted by snb

If the values in column A then in column B:

PHP Code:

---

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


---

---

Thanks, snb!