Macro to remove invisible characters

VegaOne16

New Member
Joined
Sep 6, 2009
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,922
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Ok, a better method...
VBA Code:
Sub MM1()
For Each c In ActiveSheet.UsedRange
c.Replace Chr(160), "", xlPart
Next c
End Sub
BTW if you received a #Value error using the code formula, it means the cell as blank
=CODE(AI65623)
Di you try it on a cell within the data set ??
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Forum statistics

Threads
1,141,818
Messages
5,708,766
Members
421,588
Latest member
Wawie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top