Excel Data Cleaning

I am having problems with cleaning my data.  Currently I am importing data into excel and run a macro that resizes columns, deletes other columns I don't need, and arranges the data in the format I need.  I then copy the results and dump it into another spreadsheet that I then used to perform various calculations.  However, recent I have noticed that several of my formulas aren't calculating.  After some review I noticed that there are extra spaces in the data, so for example I will have IN123456789__, where the under scores are actually spaces.  I want to remove all leading and trailing spaces or hidden characters from this data as it causes problems with my formulas.

I have tired the trim formula, which works until I delete the original data column, I can leave the column and then rewrite the macro to only copy the new trimmed data columns, but that seems like wasted effort.  Is there another way to accomplish this automatically via VB?  

I have attached a copy of the source data so you can see the extra spaces.
Who is Participating?
Rgonzo1971Connect With a Mentor Commented:

you could try

Sub Macro1()
' Macro1 Macro
For Each c In ActiveSheet.UsedRange
    If Not c.Value = "" And c.HasFormula = False Then
        With c
            .Value = Replace(.Value, Chr(160), "")
            .Value = Application.WorksheetFunction.Clean(.Value)
            .Value = Trim(.Value)
        End With
    End If
Next c

End Sub

Open in new window

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
1. Do the trim formula.
2. Select the trimmed formula.
3. Copy
4. Paste - Values Only.
5. You can now delete the original data column.
Rrave26Author Commented:
Phil, thanks for the response, and your solution will work however I would have to do that almost 20 times.  I was trying to do this all at one time so I could just copy and paste all 20 columns of data at one time.  Unless I have missed something here.
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Then it sounds like you need a VBA solution, and Rgonzo1971 has provided one.
Rrave26Author Commented:
Rgonzo,  Thanks for the answer, just to be sure I understand the code, VB newb here, Basically yu are looking in each cell, c, in the active sheet and the cells that have data in it.  Then you are looking at all of the cell that are null and don't have formulas.  This is where I get a bit confused  Not sure what .Value = Replace(.Value, Chr(160), "") does.  And why do the clean step if you have the trim function as well?  

Sorry to be a pain, but just trying to understand so I can support this moving forward.
Char 160 is the non-breaking space which is not handled with trim and is replace with a null-length string (replaced with nothing)

clean removes all non-printable characters
Rrave26Author Commented:
The solution works perfectly.
All Courses

From novice to tech pro — start learning today.