Solved

Identify non-alpha characters in cell text

Posted on 2013-10-23
3
442 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 40

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article will show you how to use shortcut menus in the Access run-time environment.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

830 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