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

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

Saurabh...

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.

How/when do you want the validation to take place?

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

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

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

Select all Open in new window

1+ Flyster

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

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!