Main Page ┬╗ MS Excel Functions ┬╗ Clean Cell


Clean Cell


By using this function, you can remove/strip/clean some character groups (letters, digits, non-words etc.) from cell value via Regular Expression.

=CleanCell(Value, Character_Group)

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.
using of CleanCell function
Here is the code:

sample.txt
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

See also: Extract Number From Text
You can download running sample from below link.
Download example file


´╗┐