Can detect if cell have special character

[shacol03]

Hi All,

Please help me how to detect if cell contains special character ( please see below sample)

SKN St. Pölten II	True
Guaros De Lara	False
Detroit Pistons	False
Uni Györ	True
Charlotte Hornets	False

<tbody>
</tbody>

Thanks & Regards,
Roy

 

[Rick Rothstein]

Hi All,

Please help me how to detect if cell contains special character ( please see below sample)

SKN St. Pölten II	True
Guaros De Lara	False
Detroit Pistons	False
Uni Györ	True
Charlotte Hornets	False

<tbody>
</tbody>

What is your definition of "special character"... any non-letter, non-digit?

Also, are you looking for a formula or VBA solution.

 

[shacol03]

What is your definition of "special character"... any non-letter, non-digit?

Also, are you looking for a formula or VBA solution.

Hi Sir,

yes sir, any non-letter and non-digit

I just want a formula sir.

thanks

 

[Rick Rothstein]

yes sir, any non-letter and non-digit

I just want a formula sir.

Give this formula a try...

=SUMPRODUCT(0+(MID(UPPER(A1),ROW(INDIRECT("A1:A"&LEN(A1))),1)>="A")*(MID(UPPER(A1),ROW(INDIRECT("A1:A"&LEN(A1))),1)<="Z")+ISNUMBER(-MID(UPPER(A1),ROW(INDIRECT("A1:A"&LEN(A1))),1))+(MID(UPPER(A1),ROW(INDIRECT("A1:A"&LEN(A1))),1)=" "))=LEN(A1)

 

[admiral100]

Another option :

=NOT(ISERR(SUMPRODUCT(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"abcdefghijklmnopqrstuvwxyz0123456789"))))

 

[shacol03]

Give this formula a try...

=SUMPRODUCT(0+(MID(UPPER(A1),ROW(INDIRECT("A1:A"&LEN(A1))),1)>="A")*(MID(UPPER(A1),ROW(INDIRECT("A1:A"&LEN(A1))),1)<="Z")+ISNUMBER(-MID(UPPER(A1),ROW(INDIRECT("A1:A"&LEN(A1))),1))+(MID(UPPER(A1),ROW(INDIRECT("A1:A"&LEN(A1))),1)=" "))=LEN(A1)

Hi Sir,

Thank you so much !! this is worked !!

im happy now

 

[Rick Rothstein]

Another option :

=NOT(ISERR(SUMPRODUCT(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"abcdefghijklmnopqrstuvwxyz0123456789"))))

@admiral100,

I like this solution much better than mine, however, you need to make one change to it. I know the OP said letters and digits, but based on his example, spaces are also allowed, so you have to add a space character somewhere (probably at the end) to the quoted string of letters and digits. Since I expect the OP will be copying the formula down through a lot of cells, I made one other change (which I should have made to my formula as well, but didn't)... I removed the Volatile INDIRECT function call and replaced it with a non-Volatile equivalent (see what is highlighted in red above and below)...

=NOT(ISERR(SUMPRODUCT(SEARCH(MID(A2,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2))),1),"abcdefghijklmnopqrstuvwxyz0123456789 "))))

 

[admiral100]

Rick Rothstein

Thanks for the feedback and the explanation