Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cleaning up cells
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cleaning up cells
=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. -- Regards, Tom Ogilvy "Andy" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cleaning up cells
It was Char(160)
"Tom Ogilvy" wrote: =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. -- Regards, Tom Ogilvy "Andy" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cleaning Up | Excel Programming | |||
Cleaning Up | Excel Programming | |||
Cleaning Up Data | Excel Discussion (Misc queries) | |||
Spreadsheet cleaning | Excel Discussion (Misc queries) | |||
Cleaning up some VB | Excel Programming |