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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,638
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub chk()
   ActiveSheet.UsedRange.Replace Chr(160), "", xlPart, , , , False, False
End Sub
 

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,875
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,921
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Sep 6, 2009
Messages
25
Office Version
  1. 365
Platform
  1. Windows
=CODE() returns a result of #VALUE! when looking at a cell.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,921
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

you are using a cell reference in the formula, ie =CODE(A1). ??
Another way to try is
Rich (BB code):
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
VBA Code:
Sub MM1()
For Each xCell In activesheet.usedrange
xCell.Value = [B]CDec(xCell.Value)[/B]
Next xCell
End Sub
 

VegaOne16

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

Well-known Member
Joined
Oct 27, 2005
Messages
20,921
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Did you try the code OR the method I provided
VBA Code:
Sub MM1()
For Each xCell In activesheet.usedrange
xCell.Value = CDec(xCell.Value)
Next xCell
End Sub
 

VegaOne16

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

Forum statistics

Threads
1,141,816
Messages
5,708,747
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