Need Help With A Formula To Identify Cells With Special Characters

[tomhouy]

I have a large list of keyword phrases, and I would like to flag any cells that have any special characters in them such as apostrophes, comma's, plus signs, dashes, question marks and such.

I am trying to write a formula that looks up against an array of special characters that I define, but I'm running into problems with characters such as apostrophes or equal signs, which trigger other functionality in Excel when they are the first character in a cell, instead of being stored as is.

Would something like this even be possible using just formulas, and preferably not having to rely on VBA?

 

[Nebu]

Hi:

Can you upload a sample file please.

Thanks

 

[Asheesh]

Hi tomhouy -

Try below...

Assuming your data is in cell A1

Put this in Cell B1

OR(MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)<"a")

To be acknowledged with CSE

See attached for ref

EDIT:

Result: TRUE indicates that the cell has a special charcter..and FALSE indicates it dosnt have..

 

[tomhouy]

Hi tomhouy -

Try below...

Assuming your data is in cell A1

Put this in Cell B1

OR(MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)<"a")

To be acknowledged with CSE

See attached for ref

EDIT:

Result: TRUE indicates that the cell has a special charcter..and FALSE indicates it dosnt have..

Thank you! That seems to do the trick. I'm not entirely sure how the formula works though, lol. Just out of curiosity, is the <"a" part somehow qualifying if there's a character in the cell that's not a letter?

 

[Asheesh]

Attached has an answer for you...

 

[tomhouy]

Perfect, thanks again!

 

[Asheesh]

You welcome..

 

[MohanBabu1023]

Hi,

You can remove blank from special character,that mean the cell with blank
also want to show "false".

regards,
Mohan