This course will introduce you to C++ 11 and teach you about syntax fundamentals.
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: " abcdefghijklmnopqrstuvwxyz
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
I am looking for a way to validate text strings against just this list of values: " abcdefghijklmnopqrstuvwxyz
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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.
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.
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(SEARC
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
=ISNUMBER(SUMPRODUCT(SEARC
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
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
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
1+ Flyster
your solution is robust. i added the space into the formula to allow any possible space.
Data-Validation.xlsx
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!
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!
Premium Content
You need an Expert Office subscription to comment.Start Free Trial