Extract and return only alpha, numeric and also specified characters from a parameter str

Happy New Year Everyone!

I am monkeying around with some code written by Peter Albert.

I wish to extend the ability to not only extract specified categories (alpha, numeric, and alpha-numeric)(This is presently accomplished by passing the optional parameter "strLimit"), but also to be able to be fed additional special characters in any given call.

The first part was easy by simply implementing an additional parameter and If/End structure such as:

Function AlphaNumericOnly(strSource As String, Optional strLimit As String) As String
'Based on original code by Peter Albert at http://stackoverflow.com/questions/15723672/how-to-remove-all-non-alphanumeric-characters-from-a-string-except-period-and-sp
' If Optional strLimit is missing or is "an" then returns both alpha and numeric
' If Optional strLimit is an "a" then returns strictly alpha
' If Optional strLimit is an "n" then returns strictly numeric
' If Optional strLimit is an "d" then returns numeric and a slash for dates
    Dim i As Integer
    Dim strResult As String
    
    If bPubSkip Then 'To allow for not processing when called for the last time by a CHANGE event after an EXIT event:
        AlphaNumericOnly = strSource
    Else
        If strLimit = "an" Or IsMissing(strLimit) Or strLimit = "" Then    ' Alpha and Numeric
            For i = 1 To Len(strSource)
                Select Case Asc(Mid(strSource, i, 1))
                    Case 48 To 57, 65 To 90, 97 To 122:
                        strResult = strResult & Mid(strSource, i, 1)
                End Select
            Next
        ElseIf strLimit = "a" Then    ' Alpha
            For i = 1 To Len(strSource)
                Select Case Asc(Mid(strSource, i, 1))
                    Case 65 To 90, 97 To 122:
                        strResult = strResult & Mid(strSource, i, 1)
                End Select
            Next
        ElseIf strLimit = "n" Then    'Numeric
            For i = 1 To Len(strSource)
                Select Case Asc(Mid(strSource, i, 1))
                    Case 48 To 57, 44, 46:
                        strResult = strResult & Mid(strSource, i, 1)
                End Select
            Next
        ElseIf strLimit = "N" Then    'Numeric & hyphen ONLY
            For i = 1 To Len(strSource)
                Select Case Asc(Mid(strSource, i, 1))
                    Case 48 To 57:
                        strResult = strResult & Mid(strSource, i, 1)
                End Select
            Next
            AlphaNumericOnly = strResult
        ElseIf strLimit = "d" Then    'Numeric & slash ONLY
            For i = 1 To Len(strSource)
                Select Case Asc(Mid(strSource, i, 1))
                    Case 47 To 57:
                        strResult = strResult & Mid(strSource, i, 1)
                End Select
            Next
        End If
        AlphaNumericOnly = strResult
    End If
End Function

In an effort to convert it to the feed of specific characters, my first step was to try to feed a string variable to the Case, first based upon the major category I wanted to extract (strLimit). Since Case takes literal parameters, I attempted to use Evaluate, but that failed with a "Type Mismatch" error:

Function TestOnly(strSource As String, Optional strLimit As String, Optional strExceptions As String) As String
' If Optional strLimit is missing or is "an" then returns both alpha and numeric
' If Optional strLimit is an "a" then returns strictly alpha
' If Optional strLimit is an "n" then returns strictly numeric
    Dim i As Integer
    Dim strCase As String
    Dim strResult As String
    
    If strLimit = "an" Then
        strCase = "48 To 57, 65 To 90, 97 To 122:"
    ElseIf strLimit = "a" Then
        strCase = "65 To 90, 97 To 122:"
    ElseIf strLimit + "n" Then
        strCase = "48 To 57, 44, 46:"
    End If
    
    
    For i = 1 To Len(strSource)
        Select Case Asc(Mid(strSource, i, 1))
            Case [strCase]
                strResult = strResult & Mid(strSource, i, 1)
        End Select
    Next
    TestOnly = strResult

End Function

The train of thought was that if I can feed a string that I can build upon to CASE, then I could concatenate in to strCase the additional ASCII codes which I would build off of a third parameter sent to this function, which would be parsed into codes (strExceptions).

Is there a way that my attack can be accomplished, or need I work on another direction? I do have an ugly solution that calls for an If/End control and provides for a different literal string for every combination of Major Categories plus other characters I might wish to include (the start of which you see in the first example above), but as the variations grew I wanted to make the code adapt instead of adding another "ElseIf" each time I wanted to address yet another character combination!

I regret that my ability to conceive of variations to experiment with are limited by my understanding when faced with the Select Case structure taking literal parameters and not variables. I have researched the internet for a bit but could not yet find a solution.

Thank-you for considering my problem!

Interesting! From my initial testing I find that the Like statement can be fed a string variable in the form of

Like "[" & strTest & "]"

So in the structure you presented above, if all works well, I should be able to achieve what I wish.

Thank-you very much!

Hi Bruce,

Thanks for the kind words and the rep points. Since you are doing the calculations in a loop and the VBA Interpreter (Compiler) does not optimize, the following change will make the routine more efficient. The time savings will not be noticeable unless you call this routine often using large data strings:

  strCase  = "[" & strCase & "]"

  For i = 1 To Len(strSource)
    c = Mid(strSource, i, 1)
    If c Like strCase  Then
      strResult = strResult & Mid(strSource, i, 1)
    End If
  Next i

Lewis

I see; so the program does not have to re-evaluate the concatenation over and over. Thanks!

Also I was wondering about something from the start - you used

Const strSource = "abc123456def7890xyz"

I would have written something like:
Dim strSource as String
strSource = "abc123456def7890xyz"

Can you explain the difference?

In this case they are equivalent, and it took less keystrokes.

A constant (Const) is a variable whose value can not be changed. Down at the bits and bytes level (which I used to care about in the old days when where an item was stored really mattered), they are also probably stored by VBA in different places.

The discussion about 'Global Variables' near the end of the following link may help with the the following discussion: http://www.functionx.com/vbaexcel/Lesson03.htm

Example code:

Option Explicit

Public Const sControlSheetNAME = "Sheet1"

Private Const xSecondsPerDAY As Double = 86400#

Private Const xPi = 3.1416

Sub abc()

  Const xPi As Double = 3.14

  '... other code
  
  MsgBox xPi
  
End Sub

Sub def()

  MsgBox xPi
  
End Sub

When I need a variable whose value will not change, I declare it as a constant. My personal convention is to CAPITALIZE the last few characters in a constant name, so I know it is a constant just by looking at the variable name. A compiler error will occur if you try to assign a value to a constant. Regular variables (and constants) have 3 basic types of scope (availability):

a. Global scope accessible by all code in any module in the Workbook. The keyword Public is used, and the constant is declared before the first routine and after 'Option Explicit' (if 'Option Explicit' is used - highly recommended).

b. Global scope accessible by all code in any routine in the same module. The keyword Private is used, and the constant is declared before the first routine and after 'Option Explicit'.

c. Local scope accessible only by code in the same routine.

It is very important to note that the same 'Constant Name' can be declared in more than one place and can have different values such as xPi above. Lower level variable (and constant) declarations ('Local Scope') take precedence over 'Module Scope' (Private), and 'File Scope' (Public).

'Module Scope' (Private) takes precedence over 'File Scope' (Public). When I have 'Public' variables (and constants) in a project, I usually put them all in their own code Module which I call ModConstantsAndGlobals.

I hope this helps.

Lewis

Thank-You!!!