x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 475

# 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 :      :-
0
BEBaldauf
1 Solution

Commented:
Look at sample. Space was also allowed
NonAlpha.xls
0

Author Commented:
Is there any way to use just a formula instead of using the function?
0

Commented:
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")
``````

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)))
``````

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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.