Help with Formula to find specific characters in a string

[Jat999]

Hi all

I have the following formula that I cannot seem to make work.

Function ContainsSpecialCharacters(str As String) As Boolean
For I = 1 To Len(str)
 ch = Mid(str, I, 1)
 Select Case ch
  Case "&", ",", ";", ":", "(", ")", "%", "?", "!", "*"
   ContainsSpecialCharacters = False
  Case Else
   ContainsSpecialCharacters = True
  Exit For
 End Select
Next
End Function

The return of the formula is "TRUE" even though I deliberately post an illegal character.

Can someone please correct me as to the problem.

Many thanks in advance

 

[Fluff]

You need to exit the function once it finds an illegal character.
That said, judging by the name of your function, shouldn't you be returning True if an illegal character is found?

 

[Jat999]

You need to exit the function once it finds an illegal character.
That said, judging by the name of your function, shouldn't you be returning True if an illegal character is found? See below

Hi Fluff

Thanks for your reply. So I added an "Exit For" statement after the "False" statement, but it still does not recognise the illegal characters. However what is interesting, if I examine a cell that the formula is active and referring to a cell with the illegal characters, I do not see it in the formula evaluation box - The & being the illegal character.

 

[Fluff]

Didn't notice the Exit For, you need to get rid of that.

 

[Jat999]

Hi Fluff

Revised formula reads as follows and works

Function ContainsSpecialCharacters(str As String) As Boolean
For I = 1 To Len(str)
 ch = Mid(str, I, 1)
 Select Case ch
  Case "&", ",", ";", ":", "(", ")", "%", "?", "!", "*"
   ContainsSpecialCharacters = False
   Exit For
  Case Else
   ContainsSpecialCharacters = True
 End Select
Next
End Function

 

[Fluff]

You're welcome & thanks for the feedback.