Improve Data Quality Using VBA Code – Clean and Trim Cell Values

Improve Data Quality Using VBA Code – Clean and Trim Cell Values

This code allows trimming (removing leading and ending spaces) and cleaning (removing unprintable characters) process for your Excel data. It’s an excellent way to clean up your data which is coming from an outside database.

Sub Cleanse_Data()
'Quickly Clean/Trim selected cell values
'By ExcelJunction.com
Dim rng As Range
Dim Area As Range
'Remove any formulas from selection
If Selection.Cells.Count = 1 Then
Set rng = Selection
Else
Set rng = Selection.SpecialCells(xlCellTypeConstants)
End If
'Trim and Clean cell values
For Each Area In rng.Areas
Area.Value = Evaluate("IF(ROW(" & Area.Address & "),CLEAN(TRIM(" & Area.Address & ")))")
Next Area
End Sub