How do I search for a string, without risking the "#value!" error?

  • Thread starter Thread starter Shiperton Henethe
  • Start date Start date
S

Shiperton Henethe

Hi

How do I test out whether a cell has any given string of
characters in it, *without* returning the "#value!" error?!

I've tried using =SEARCH() and =FIND() functions but
both produce the "#value!" error if the cell in question
doesnt contain the string being searched for.

(And the problem with the #value! error is that you then
cant count or total that column without getting more
#value! errors!)

Any suggestions?



Ship
Shiperton Henethe
 
OK Thanks.

Mind you it looks pretty clunky to me though!

Why cant the stupid function return something sensible like zero ?!



Ship
 
Shiperton said:
OK Thanks.

Mind you it looks pretty clunky to me though!

Why cant the stupid function return something sensible like zero ?!
Click to expand...

agreed, but that's the way MS has implemented it
Frank
 
Shiperton said:
OK Thanks.

Mind you it looks pretty clunky to me though!

Why cant the stupid function return something sensible like zero ?!



Ship

news:[email protected]...
Click to expand...


You could always add one as a VBA macro ...

Public Function TextSearch(Src$, Find$, Optional IgnoreCase As Long = 0) As
Long

If Len(Src$) Then
If Len(Find$) Then
If IgnoreCase Then
TextSearch = InStr(UCase$(Src$), UCase$(Find$))
Else
TextSearch = InStr(Src$, Find$)
End If
End If
End If

End Function

Syntax is:

TextSearch( StringToSearchIn, StringToSearchFor)
or with the optional parameter to ignore the upper/lower case
TextSearch( StringToSearchIn, StringToSearchFor, NonZeroNumber)

Hope this helps.

Best,
Bill
 
Back
Top