Return to Snippet

Revision: 68991
at April 1, 2015 09:14 by BDOGG32


Initial Code
Function RegexReplace(ByVal text As String, _
                      ByVal replace_what As String, _
                      ByVal replace_with As String) As String

   Dim RE As Object
   On Error GoTo RegexReplace_Error
   Set RE = CreateObject("vbscript.regexp")
   RE.Pattern = replace_what
   RE.Global = True
   RegexReplace = RE.Replace(text, replace_with)
   On Error GoTo 0
   Exit Function
RegexReplace_Error:
   RegexReplace = err.Number

End Function

Initial URL


Initial Description
Uses regular expressions to do string manipulation.  Enter a regular expression into the required parameter and what you want the manipulation to do and it will process the string manipulation.  For example if A1 contains John Doe1, Jane Doe2, John Wayne3 and you wanted to strip all the numbers out of it you could enter this formula:  =RegexReplace(I22,"[^a-zA-Z, ]", "").  This strips all characters that are not a through z or a comma or space.  Since all numbers are not in there then it removes them.  If you wanted to strip out all alpha characters and leave only the numbers it would look like:  =RegexReplace(I22,"[^1-9, ]", "")

Initial Title
StripCharacters

Initial Tags
regex, code, function, excel

Initial Language
Visual Basic