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

[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

 

[Max]

Try : =IF(ISERROR(yourfunction),"",yourfunction)

 

[Frank Kabel]

Hi
try
=IF(ISERROR(FIND(.....)),"",FIND(....))

Frank

 

[Shiperton Henethe]

OK Thanks.

Mind you it looks pretty clunky to me though!

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



Ship

 

[Frank Kabel]

OK Thanks.

Mind you it looks pretty clunky to me though!

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

agreed, but that's the way MS has implemented it
Frank

 

[Bill Plenge]

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

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