3

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.

Improve this question
3
  • You probably have some Unicode characters that the CODE() worksheet function is mis-handling. Commented Jun 15, 2016 at 14:38
  • As mentioned in your other string, you need to replace CODE with UNICODE in the function. I believe the UNICODE function became available in Excel 2013. Commented Jun 16, 2016 at 13:46
  • unfortunately, I use excel 2010 (company provided, so I can not update this myself) I guess I am stuck using the formula with a predetermined list with allowed characters. Commented Jun 17, 2016 at 6:52

1 Answer 1

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.