Posted By

BDOGG32 on 04/01/15


Tagged

regex strip code function excel characters vba


Versions (?)

Who likes this?

2 people have marked this snippet as a favorite

BDOGG32
steveulven


StripCharacters


 / Published in: Visual Basic
 

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, ]", "")

  1. Function RegexReplace(ByVal text As String, _
  2. ByVal replace_what As String, _
  3. ByVal replace_with As String) As String
  4.  
  5. Dim RE As Object
  6. On Error GoTo RegexReplace_Error
  7. Set RE = CreateObject("vbscript.regexp")
  8. RE.Pattern = replace_what
  9. RE.Global = True
  10. RegexReplace = RE.Replace(text, replace_with)
  11. On Error GoTo 0
  12. Exit Function
  13. RegexReplace_Error:
  14. RegexReplace = err.Number
  15.  
  16. End Function

Report this snippet  

You need to login to post a comment.