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.

Carl Nixon

Carl Nixon is a VBA developer with 20 years experience, 10 years as a freelancer. He has worked for clients around the world, ranging in size from micro businesses to global brands. In his 20 years developing VBA projects he has helped businesses to automate and simplify processes, resulting in massive time and money savings. He is also the author of 3 top selling books on Excel. Visit my LinkedIn profile or my GitHub page

Leave a Reply

Your email address will not be published. Required fields are marked *