CODEWITHSUNDEEP
excelrangeasciispecial-characters
Weathus
Weathus

Reputation: 43

recognizing special characters outside a certain ASCII range in excel does not always work

I recently asked a question (Check if a text string contains special characters in excel) on how to check if cells contain ASCII codes outside a certain range.

Since, I use the following formula:

=IF(SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>=32)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<=126))=LEN(A1),"OK","Not OK")

where I check if cell A1 contains any special characters outside the 32-126 ASCII range.

And I thought this was working fine. However I noticed that this formula doesn't pick up all special characters. E.g. ≥, β,... are not getting recognized. The formula returns 'ok' even when it shouldn't...

Does anybody now what causes this? for other special characters, this formula works fine.

Upvotes: 3

Views: 3935

Answers (1)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96773

The beta is a UniCode character. I put part of your post in C1

In D1, I enter:

=CODE(MID(C$1,ROWS($1:1),1))

and in E1:

=CHAR(D1)

after copying down:

enter image description here

As you see, CODE() returns ASCII 63 for the beta.

Upvotes: 1

Related Questions