Solved

Identify non-alpha characters in cell text

Posted on 2013-10-23
3
431 Views
Last Modified: 2013-10-23
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
Comment
Question by:BEBaldauf
3 Comments
 
LVL 39

Accepted Solution

by:
als315 earned 350 total points
ID: 39595137
Look at sample. Space was also allowed
NonAlpha.xls
0
 

Author Comment

by:BEBaldauf
ID: 39595223
Is there any way to use just a formula instead of using the function?
0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 39595608
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question