Excel - validate text in the list (" abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789")

Hi Folks -

I am looking for a way to validate text strings against just this list of values: " abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789". Any characters outside of this list would indicate unwanted characters, either by raising an error or somehow indicating unwanted characters such as : (&,%,$,-,!) and so on.

I seek to create a method for end users to replicate the validation in Excel that I have created in Oracle and MS-Access.

Any thoughts about how to approach this challenge?

Thanking all of you in advance,

Mike
cincimikeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Would you be fine with space or you want to consider that as invalid too??

Saurabh...
cincimikeAuthor Commented:
Wow! Such a quick response - thank you!

Yes - in this instance, a space is a valid character.

I am essentially checking column names against a naming standard that only allows that list of characters - anything outside would be considered a violation.
NorieAnalyst Assistant Commented:
How/when do you want the validation to take place?
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

cincimikeAuthor Commented:
I was hoping the user could interact with it as they do with any other formula, and received immediate feedback.

Another approach I'm looking at is seeing if I can import my VBA code from Access as a function in Excel. VBA has that InStr function that would quite well when I loop through each character in the column.
FlysterCommented:
You could use Data Validation. Under the Data tab, select Data Validation. Under Settings - Allowed, select Custom. Under Formula, enter this:

=ISNUMBER(SUMPRODUCT(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789")))

Under the Error Alert tab, set the error message you want to appear. You can define this in one cell and paste it to the rest that you need. Do this by copying the defined cell, highlight all cells that you want validated, right-click one of those cells and select Paste Special-Validation

Flyster
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
You'll probably have to do exactly that - import and use the VBA function.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Excel amusantCommented:
Hi,

Here is one way to do it.

attached please find an example. if you write characters which is not in your list in range (A1:G100)  it will pop ups invalid message.

Note: you can change the range to much your range. or change the range to ("A:A") if you want to block the whole sheet.

Best,
Invalid-Char.xlsb
FlysterCommented:
See attached. Cell A1 has Data Validation
Validation.xlsx
Excel amusantCommented:
Hi,

I attached a revised file as you wanted the space to be valid character.
Invalid-Char.xlsb
Rory ArchibaldCommented:
FWIW a UDF would be pretty simple:

Function IsValid(sText As String) As Boolean
    IsValid = Not VBA.UCase$(sText) Like "*[! A-Z0-9]*"
End Function

Open in new window

Professor JMicrosoft Excel ExpertCommented:
1+ Flyster

your solution is robust.  i added the space into the formula to allow any possible space.
Data-Validation.xlsx
cincimikeAuthor Commented:
Thank you very much for the assurance. I found a link that details the steps required to add a VBA Procedure as a function:

https://support.office.com/en-in/article/Create-Custom-Functions-in-Excel-2007-2f06c10b-3622-40d6-a1b2-b6748ae8231f

There are a few tweaks I need to do - but I think I have the core requirement satisfied.

Thanks to all for your great suggestion!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.