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)

[TABLE="width: 200"]
<tbody>[TR]
[TD]SKN St. Pölten II[/TD]
[TD]True[/TD]
[/TR]
[TR]
[TD]Guaros De Lara[/TD]
[TD]False[/TD]
[/TR]
[TR]
[TD]Detroit Pistons[/TD]
[TD]False[/TD]
[/TR]
[TR]
[TD]Uni Györ [/TD]
[TD]True[/TD]
[/TR]
[TR]
[TD]Charlotte Hornets[/TD]
[TD]False[/TD]
[/TR]
</tbody>[/TABLE]

Thanks & Regards,
Roy

 

[Rick Rothstein]

Hi All,

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

[TABLE="width: 200"]
<tbody>[TR]
[TD]SKN St. Pölten II[/TD]
[TD]True[/TD]
[/TR]
[TR]
[TD]Guaros De Lara[/TD]
[TD]False[/TD]
[/TR]
[TR]
[TD]Detroit Pistons[/TD]
[TD]False[/TD]
[/TR]
[TR]
[TD]Uni Györ [/TD]
[TD]True[/TD]
[/TR]
[TR]
[TD]Charlotte Hornets[/TD]
[TD]False[/TD]
[/TR]
</tbody>[/TABLE]

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