Tuesday, 14 June 2011

Remove ALL spaces from cells in MS Excel

This blog pertains to data cleaning after you download data from surveyshare.com.

One more thing you MAY have to do when you download your data from surveyshare.com is to remove all unnecessary spaces from certain fields, especially if they happen to be your index variable. I my case I had to do it from the email field  which was used to match the responses to other surveys!!! Here is the macro

 I have found:

Sub TrimEText()
' This module will trim extra spaces from BOTH SIDES and excessive spaces from inside the text.
Dim MyCell As Range
On Error Resume Next
For Each MyCell In Selection.Cells
MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), " ", " ")
MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), " ", " ")
MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), " ", " ")
MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), " ", "")
Next
On Error GoTo 0
End Sub


Really grateful to the author of the macro!!

No comments: