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

cincimike
cincimike used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2015

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

Saurabh...

Author

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.

Author

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.
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
"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
You'll probably have to do exactly that - import and use the VBA function.
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
See attached. Cell A1 has Data Validation
Validation.xlsx
Hi,

I attached a revised file as you wanted the space to be valid character.
Invalid-Char.xlsb
Most Valuable Expert 2011
Top Expert 2011

Commented:
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 Expert
Top Expert 2014

Commented:
1+ Flyster

your solution is robust.  i added the space into the formula to allow any possible space.
Data-Validation.xlsx

Author

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!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial