Are All Characters Approved Characters?
Sometimes we have items such as telephone numbers or serial numbers, that should only accept certain characters. For example you may want telephone numbers to only contain the digits 0 to 9, i.e. you don’t want to have entries that contain (, ), _, or -. This function will allow you to check their validity before committing them.
To check the above the scenario we would use;
IsNumberValid = CharsOnlyIn("012-3456-7890", "0123456789")
The “012-3456-7890” is the string we want to check, and “0123456789” is the list of allowed characters. In the above example, IsNumberValid would be FALSE because the “-” symbol is not in the allowed characters strings.
It could also be used as a rough check to see if a string contains hidden characters.
IsItSafe = CharsOnlyIn(UCase("String to check"), "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789")
If we had both lower case letters (a-z) and upper case letters (A-Z) in the allowed characters string, it would almost double the computing time. If you are checking thousands of entries this would soon add up.
So in the example, where we are checking for hidden characters, and don’t care about upper and lower case characters, I have converted the “String to check” to all upper case with the UCase() function. This means we now only need to check against the upper case characters (A-Z).
Function CharsOnlyIn(first_str, second_str) As Boolean ' Purpose --> Checks all the characters used in the first string are in the ' second string. This is to ensure only valid characters are used. ' Returns TRUE if valid, FALSE if not. Process is case sensitive ' Arguments --> first_str (String) ' - Contains the string of characters to be checked ' second_str (String) ' - Contains the string of characters allowed ' Returns --> (Boolean) ' - Returns FALSE by default (including for errors) ' - Returns TRUE if all the characters in the first string are ' found in the second string. ' Created by --> Carl Nixon, Excel-Expert.co.uk ' Set up variables ' ================ ' Holds the calculated result to be returned at the end Dim result As Boolean ' Default the result to false result = False ' Check its safe to proceed ' ========================= ' If either string is empty then jump to finish as a result cant be calculated If first_str = "" Or second_str = "" Then GoTo Finished ' Check the strings ' ================= ' Iterate over first string characters For f = 1 To Len(first_str) ' Iterate over second string characters For s = 1 To Len(second_str) ' If the f char in the first string matches chars of the ' second string move to the next char in the first string If Mid(first_str, f, 1) = Mid(second_str, s, 1) Then GoTo Next_First Next s ' If we reach this point char f of the first string ' wasn't found in the second string so break out GoTo NotFound Next_First: Next f ' If we reach point no missing matches were found ' and we can change the result to TRUE result = True NotFound: ' Finish up ' ========= Finished: ' Pass the result back CharsOnlyIn = result End Function
All of the code featured on this site is available to download on my GitGub page. Click here to visit it.