• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 472
  • Last Modified:

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
Asked:
BEBaldauf
1 Solution
 
als315Commented:
Look at sample. Space was also allowed
NonAlpha.xls
0
 
BEBaldaufAuthor Commented:
Is there any way to use just a formula instead of using the function?
0
 
Ejgil HedegaardCommented:
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.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now