Checking for characters in cell giving weird results

[rizzo93]

I have users who are using my file to generate a name in an approved format. This includes checking for the following characters that are NOT to be included in the name:

| / \ : - [ ] { } * ? ' " ^ #

I thought this was going to be a simple SEARCH function, but I'm getting results I wasn't expecting.

=IFERROR(IF(SEARCH(L11,$K$3),TRUE),FALSE)

L11 = (the character I'm checking for; L12 is the next character and so on)
$K$3 = (the text that I am checking for the existence of these "bad" characters)

To be clear, these are characters I do not want in K3, so if they do exist, I need to make the user aware of this and remove them.

Here's an example of what K3 can look like:

Development/Implementation [45] MME (PERC)
​

The () is fine, but my formula should identify the / and []. However, the results all say FALSE.

And to make things even more confusing, I'm getting TRUE for characters that are not in K3 (these characters are * and ?).

What am I overlooking, please?

 

[JoeMo]

What exactly does the entry for / look like in col L?
Note that Excel uses ? and * as wildcards so you will always get TRUE on those except when K3 is empty.

 

[rizzo93]

Thanks, JoeMo.

It's just the character itself, /.


All the cells have one character. Good point about the ? and *. Is there a work-around for those?

 

[JoeMo]

Thanks, JoeMo.

It's just the character itself, /.


All the cells have one character. Good point about the ? and *. Is there a work-around for those?

Apart from your *? issue, the rest are working for me. For the *? issue, use ~* and ~?.
The ~ tells Excel you want the * and ? treated as literals, not as wildcards.

 

[rizzo93]

Awesome, thanks again, JoeMo!

 

[JoeMo]

Awesome, thanks again, JoeMo!

You are welcome - thanks for the reply.