How do I test if a cell has any of the special characters e.g. *,?,/
Thanks for any help.

Here's the formatted text:

You can use the following formula to check if a cell contains any of the special characters you mentioned:

Here, A1 is the cell you want to check for special characters. You can change this to the cell reference of your choice.

Let me explain how this formula works:

1. SEARCH function is used to find the position of each special character in the cell. If the character is not found, it returns an error value #VALUE!.
2. ISNUMBER function is used to convert the error values to FALSE and the position values to TRUE.
3. The double negative (--) coerces the TRUE/FALSE values to 1/0.
4. SUMPRODUCT function adds up the 1s and 0s.
5. If the sum is greater than 0, it means at least one special character was found in the cell, so the formula returns TRUE. Otherwise, it returns FALSE.

Try:

Function CellHasSpecChar(c As Range) As Boolean
CellHasSpecChar = c.Value Like "*[?/*]*"
End Function

Sub TestIt()
MsgBox CellHasSpecChar(ActiveCell)
End Sub

Regards,
Greg


"linglc" wrote:

How do I test if a cell has any of the special characters e.g. *,?,/
Thanks for any help.

For ? and / you can use

=ISNUMBER(FIND("/",A2)) etc..

For *, best to use

=ISNUMBER(FIND(CHAR(42),A2))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"linglc" wrote in message
...
How do I test if a cell has any of the special characters e.g. *,?,/
Thanks for any help.

If you want the test all in one, try

=SUMPRODUCT(--(ISNUMBER(FIND({"*","/","?"},A1))))0

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"linglc" wrote in message
...
How do I test if a cell has any of the special characters e.g. *,?,/
Thanks for any help.