Macro 55: Clean Up Non-Printing Characters
Sometimes you have non-printing characters in your data such as line feeds, carriage returns, and
non-breaking spaces. These characters often need to be removed before you can use the data for
serious analysis.
Now, anyone who has worked with Excel for more than a month knows about the Find and Replace
functionality. You may have even recorded a macro while performing a Find and Replace (a recorded
macro is an excellent way to automate your find and replace procedures). So your initial reaction may
be to simply find and replace these characters. The problem is that these non-printing characters are
for the most part invisible and thus difficult to clean up with the normal Find and Replace routines.
The easiest way to clean them up is through VBA.
If you find yourself struggling with those pesky invisible characters, use this general purpose macro to
find and remove all the non-printing characters.
How it works
This macro is a relatively simple Find and Replace routine. We are using the Replace method, tell- ing
Excel what to find and what to replace it with. This is similar to the syntax you would see when recording
a macro while manually performing a Find and Replace.
The difference is that instead of hard-coding the text to find, this macro uses character codes to spec-
ify our search text. Every character has an underlying ASCII code, similar to a serial number. For
instance, the lowercase letter a has an ASCII code of 97. The lower case letter c has an ASCII code
of 99. Likewise, invisible characters also have a code:
The line feed character code is 10.
The carriage return character code is 13.
The non-breaking space character code is 160.
This macro utilizes the Replace method, passing each character’s ASCII code as the search item.
Each character code is then replaced with an empty string:
Sub Macro55()
‘Step 1: Remove Line Feeds
ActiveSheet.UsedRange.Replace What:=Chr(10), Replacement:=””
‘Step 2: Remove Carriage Returns
ActiveSheet.UsedRange.Replace What:=Chr(13), Replacement:=””
‘Step 3: Remove Non-Breaking Spaces
ActiveSheet.UsedRange.Replace What:=Chr(160),
Replacement:=””
End Sub
1. Step 1 looks for and removes the Line Feed character. The code for this character is 10. We
can identify the code 10 character by passing id through the Chr function. After Chr(10)
is identified as the search item, this step then passes an empty string to the Replacement
argument.
Note the use of ActiveSheet.UsedRange. This essentially tells Excel to look in all the
cells that have had data entered into them. You can replace the UsedRange object with an
actual range if needed.
2. Step 2 finds and removes the carriage return character.
3. Step 3 finds and removes the non-breaking spaces character.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11 on your keyboard.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code.
