Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1239
  • Last Modified:

VBA, Remove Non AlphaNumeric Characters from A string

Hi people. Is it there any better way to write the below Function?


Function AlphaNumerize(anyString As String) As String

AlphaNumerize = Replace(anyString, "-", "")
AlphaNumerize = Replace(AlphaNumerize, "(", "")
AlphaNumerize = Replace(AlphaNumerize, ")", "")
AlphaNumerize = Replace(AlphaNumerize, "_", "")
AlphaNumerize = Replace(AlphaNumerize, "\", "")
AlphaNumerize = Replace(AlphaNumerize, "/", "")
AlphaNumerize = Replace(AlphaNumerize, "<", "")
AlphaNumerize = Replace(AlphaNumerize, ">", "")
AlphaNumerize = Replace(AlphaNumerize, "=", "")


End Function

Open in new window


Thanks
0
New_Alex
Asked:
New_Alex
  • 2
1 Solution
 
Patrick MatthewsCommented:
To get rid of every character that is not a digit or letter, I would use Regular Expressions.

1) Add this function to a regular VBA module:

Function RegExpReplace(LookIn As String, PatternStr As String, Optional ReplaceWith As String = "", _
    Optional ReplaceAll As Boolean = True, Optional MatchCase As Boolean = True, _
    Optional MultiLine As Boolean = False)
    
    ' Function written by Patrick G. Matthews.  You may use and distribute this code freely,
    ' as long as you properly credit and attribute authorship and the URL of where you
    ' found the code
    
    ' For more info, please see:
    ' http://www.experts-exchange.com/articles/Programming/Languages/Visual_Basic/Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html
    
    ' This function relies on the VBScript version of Regular Expressions, and thus some of
    ' the functionality available in Perl and/or .Net may not be available.  The full extent
    ' of what functionality will be available on any given computer is based on which version
    ' of the VBScript runtime is installed on that computer
    
    ' This function uses Regular Expressions to parse a string, and replace parts of the string
    ' matching the specified pattern with another string.  The optional argument ReplaceAll
    ' controls whether all instances of the matched string are replaced (True) or just the first
    ' instance (False)
    
    ' If you need to replace the Nth match, or a range of matches, then use RegExpReplaceRange
    ' instead
    
    ' By default, RegExp is case-sensitive in pattern-matching.  To keep this, omit MatchCase or
    ' set it to True
    
    ' If you use this function from Excel, you may substitute range references for all the arguments
    
    ' Normally as an object variable I would set the RegX variable to Nothing; however, in cases
    ' where a large number of calls to this function are made, making RegX a static variable that
    ' preserves its state in between calls significantly improves performance
    
    Static RegX As Object
    
    If RegX Is Nothing Then Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = PatternStr
        .Global = ReplaceAll
        .IgnoreCase = Not MatchCase
        .MultiLine = MultiLine
    End With
    
    RegExpReplace = RegX.Replace(LookIn, ReplaceWith)
    
End Function

Open in new window


2) Use a formula like this:

=RegExpReplace(A2,"[^A-Za-z0-9]")

That will remove all non-letters and non-digits.

For more about Regular Expressions, please see:

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/A_1336-Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html
0
 
New_AlexAuthor Commented:
Thank you SIR !
0
 
Patrick MatthewsCommented:
New_Alex,

Glad to help!  If you have not already done so, I would really appreciate it if you could please return to my article
http://www.experts-exchange.com/Programming/Languages/Visual_Basic/A_1336-Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html
and click 'Yes' for the 'Was this helpful?' voting.

Patrick
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now