Special characters validation

[wasntme]

Hi again ,

I am looking now for solution to highlight cells with conditional formatting if they consist some special symbol (such as , . / "" : &). On the internet i found this as a possible solution:

SUMPRODUCT((CODE(MID(BB8,ROW(INDIRECT("1:"&LEN(BB8))),1))<48)*(CODE(MID(BB8,ROW(INDIRECT("1:"&LEN(BB8))),1))<>45))+SUMPRODUCT((CODE(MID(BB8,ROW(INDIRECT("1:"&LEN(BB8))),1))>57)*(CODE(MID(BB8,ROW(INDIRECT("1:"&LEN(BB8))),1))<65))+SUMPRODUCT((CODE(MID(BB8,ROW(INDIRECT("1:"&LEN(BB8))),1))>90)*(CODE(MID(BB8,ROW(INDIRECT("1:"&LEN(BB8))),1))<97)*(CODE(MID(BB8,ROW(INDIRECT("1:"&LEN(BB8))),1))<>95))+SUMPRODUCT((CODE(MID(BB8,ROW(INDIRECT("1:"&LEN(BB8))),1))>122)*1)

but it counts also the empty space as special character. As i can not understand the formula is there a more simple way to get the desirable result or how to rework the formula to not take into account the empty spaces.

Thanks in advance

[FDibbins]

We would need to see some sample data that you are using this on, as well as ALL special characters you will test for.

[wasntme]

Hi FDibbins,

This conditional formatting should work in columns (O, P, BB and BC, conditional formatting only present in BB and BC right now). The idea is this file will concatenate data and prepare .csv file for upload. That's why i need to highlight special characters that would potentially break the upload after. Special characters i'd like to remove are , . / "" : & (that i know for sure will break my file), might not be all tho, but nice for a starter.

Thanks.

[JeteMc]

Perhaps the following will help.
1) Type the symbols that you want to use in separate cells (XFD1:XFD6 in attached file),
Note that in order to type the / symbol by itself you will need to change Excel options as described in this article.
2) Use the following formula as a conditional formatting rule (applied to BB16:BC25):

Please Login or Register  to view this content.

Let us know if you have any questions.

[FlameRetired]

@ JeteMc

Note that in order to type the / symbol by itself you will need to change Excel options as described in this article.

You can also just go into edit mode and type in the /.