Cleaning up cells

[Andy]

I am receiving data from a foriegn source. Sometimes there are "spaces" in
front of the data and I want to clean them up. I say "spaces" because the
trim function doesn't clean them up and when I write:
LEFT(A3) = " "
it comes back false.

The only way I have to clean these up is to write this code for each column:
Sheets("SummaryForAccess").Cells(rowNum, colNum).Value = "'" &
Right(Sheets("SummaryForAccess").Cells(rowNum, colNum).Value ,
Len(Sheets("SummaryForAccess").Cells(rowNum, colNum).Value ) - 1)

But the columns with bad data change often. So, I was wondering if there is
a different way to check for this kind of data and/or clean it.

 

[Tom Ogilvy]

=code(left(a3,1))

will tell you what the ascii code of the character is.

Then, if it is something unique, you can use the replace function

assume that, as an example, it returned 160 (non breaking space often
found in web pages)

sub CleanUpData()
Columns(1).Replace What:=chr(160), _
Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub

Make columns whatever range you want to process Activesheet.UsedRange,
Range("A:F") as other examples.

 

[Andy]

It was Char(160)

=code(left(a3,1))

will tell you what the ascii code of the character is.

Then, if it is something unique, you can use the replace function

assume that, as an example, it returned 160 (non breaking space often
found in web pages)

sub CleanUpData()
Columns(1).Replace What:=chr(160), _
Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub

Make columns whatever range you want to process Activesheet.UsedRange,
Range("A:F") as other examples.