By using this function, you can remove/strip/clean some character groups (letters, digits, non-words etc.) from cell value via Regular Expression.
The function has two parameters, mandatory one is -of course- cell value which we use. Second and optional one is Character_Group.
At figure below you can see usage and results.
Depending on your needs, you may add extra Character_Group by using Regular Expression.
Here is the code:
Function CleanCell(Value As String, Optional Character_Group As Integer) Dim objRegex Set objRegex = CreateObject("vbscript.regexp") Select Case Character_Group 'If Character_Group is 0 or missing, it means default, do not strip any character, keep original value. Case Is = 0 Or IsMissing(Character_Group) strPattern = "" 'If Character_Group is 1, Clean all digits. Case Is = 1 strPattern = "[\d]+" 'If Character_Group is 2, Remove non-digits. Case Is = 2 strPattern = "[\D]+" 'If Character_Group is 3, Remove all letters, digits and underscores. Case Is = 3 strPattern = "[\w]+" 'If Character_Group is 4, Remove all non-words. Case Is = 4 strPattern = "[\W]+" 'If Character_Group is 5, Remove all whitespaces (spaces, tabs, line breaks). Case Is = 5 strPattern = "[\s]+" Case Else strPattern = "" End Select With objRegex .Global = True .pattern = strPattern CleanCell = .Replace(Value, vbNullString) End With End Function