Macro to remove invisible characters

[VegaOne16]

Hello,

The issue I am having is when I export data from SAP as an excel workbook, cells that are blank are showing up with invisible characters of some sorts. I know this is happening because the exported workbooks are about 80mb in size when they should be about 1mb. If I use the ISBLANK() function and reference a cell that should be blank, it's returning an answer of "FALSE". If I use F2 on the Keyboard and then hit ENTER, the result of the ISBLANK will then be true. I can also clear the cell contents using the keyboard shortcut "ALT-E-A-A" and ISBLANK will also show up as true. If I 'clear' one column of characters, my workbook size decreases by 1mb or so.

I was hoping someone could help me with a macro that would remove the invisible characters. I don't know how to research what they are... but my suggestion would be a loop macro that does something like this:

Step 1 - Go to Cell A1
Step 2 - If ISBLANK(A1)=TRUE, to go Cell A2, if not then go to step 3.
Step 3 - If LEN(A1)=0, perform "Alt-E-A-A" to clear the cell contents, then go to Cell A2 and reperform.
Step 4 - Loop until all cells in sheet are analyzed

For additional ideas, under an Oracle platform this issue existed and the following macro eliminated those characters, however it doesn't seem to work on data exported from SAP:

Sub RemoveInvisibleCharacters()

Cells.Replace What:=ChrW(&H202D), Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
MsgBox "Invisible Buggers Gone!"

End Sub

I'm using Windows 10 with Office 365.

Thank you for your help.

 

[Fluff]

How about

Sub chk()
   ActiveSheet.UsedRange.Replace Chr(160), "", xlPart, , , , False, False
End Sub

 

[VegaOne16]

How about

Sub chk()
   ActiveSheet.UsedRange.Replace Chr(160), "", xlPart, , , , False, False
End Sub

This one did not work unfortunately.

 

[yky]

I was hoping someone could help me with a macro that would remove the invisible characters. I don't know how to research what they are...

Perhaps you could post few rows of your data using XL2BB. Someone may be able to figure out what they are.

 

[Michael M]

Did you try using the =CODE() function to determine what the ascii character is.
So, if the cell is A1, try using =CODE(A1). what does that return

 

[VegaOne16]

=CODE() returns a result of #VALUE! when looking at a cell.

 

[Michael M]

you are using a cell reference in the formula, ie =CODE(A1). ??
Another way to try is

Select affected cells.
CTRL+H to display Find/Replace dialog
Find: ALT+160 (Hold the ALT key and enter numbers via numeric keypad)
Replace: <leave blank>

OR
a macro

Sub MM1()
For Each xCell In activesheet.usedrange
xCell.Value = [B]CDec(xCell.Value)[/B]
Next xCell
End Sub

 

[VegaOne16]

I found this macro will complete the task, however it's only really usable with a small selection of data (If I select 65K cells to analyze, it'll take a few minutes to run). Any suggestions on how the code can be tweaked to run more efficiently? Thank you again for your time.


Sub RemCharGood()
With Selection
Set c = .Find("", LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = ""
Set c = .FindNext(c)
If c Is Nothing Then Exit Do
Loop While c.Address <> firstAddress
End If
End With
End Sub

 

[Michael M]

Did you try the code OR the method I provided

Sub MM1()
For Each xCell In activesheet.usedrange
xCell.Value = CDec(xCell.Value)
Next xCell
End Sub

 

[VegaOne16]

RE: Michael

Yes I put a cell reference in the =CODE() formula to test, cell =CODE(AI65623) specifically.

The CTRL+H method did not work unfortunately.

I am receiving a "Run-time error '13' type mismatch on your VBA code on the xCell.Value = CDec(xCell.Value) line