asked on

Identify non-alpha characters in cell text

I need a formula to look at a cell and determine if any non-alpha or number characters are used, and if possible return which "invalid" characters have been found. (A through Z and 0 through 9 would be the only acceptable characters.)

Example:
Cell value: History: American 1900-2000
Formula Result: True
or : :-

ASKER CERTIFIED SOLUTION

als315

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BEBaldauf

ASKER

Is there any way to use just a formula instead of using the function?

Ejgil Hedegaard

To get a True/False result, formula for text in A2.
Array formula, insert with Ctrl+Shift+Enter.

=IF(SUM(IF(CODE(MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1))>=65,1,0)*IF(CODE(MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1))<=90,1,0))+SUM(IF(CODE(MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1))>=48,1,0)*IF(CODE(MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1))<=57,1,0))+SUM(IF(CODE(MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1))=32,1,0))=LEN(A2),"True","False")

Open in new window

To get the number of non-alpha characters

=LEN(A2)-(SUM(IF(CODE(MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1))>=65,1,0)*IF(CODE(MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1))<=90,1,0))+SUM(IF(CODE(MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1))>=48,1,0)*IF(CODE(MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1))<=57,1,0))+SUM(IF(CODE(MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1))=32,1,0)))

Open in new window

I don't think it is possible to get a list of the characters with a formula.
Use the UDF by als315, it does the trick.