Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Getting Rid of Non-Printing Characters Intelligently

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Getting Rid of Non-Printing Characters Intelligently.

If you work with files that originate from a non-Excel source, you can sometimes end up with characters in your cells that Excel doesn't know how to display properly. For instance, you may have a comma-delimited text file generated by your company's accounting software, and you load the file into Excel. In some cells you may notice that there are small boxes. These represent non-printing characters. Excel displays the small boxes so that you know the character is there, even though it cannot be displayed or printed.

To get rid of these characters you an try to use the Find and Replace feature of Excel. Try these steps:

  1. Within the cell that contains one of the small boxes, highlight the box and press Ctrl+C. This copies the character to the Clipboard.
  2. Choose Replace from the Edit menu or press Ctrl+H. Excel displays the Replace tab of the Find and Replace dialog box. (See Figure 1.)
  3. Figure 1. The Replace tab of the Find and Replace dialog box.

  4. With the insertion point in the Find What box, press Ctrl+V. This pastes the contents of the Clipboard (the offending character) into the Find What box. The character will most likely not look like the small box you selected and copied in step 1.
  5. If nothing was pasted in step 3, then close the dialog box and try the steps again. If nothing is still pasted, then you won't be able to use Find and Replace to get rid of the non-printing characters, and you can skip the rest of these steps.
  6. If you want to just delete the characters, make sure there is nothing in the Replace With box. If you want to replace the characters with spaces, put a single space in the Replace With box.
  7. Click on Replace All.

This approach may or may not work, depending mostly on Excel and whether it let you accurately copy the offending character in step 1. If it does work, then you have learned a valuable technique for getting rid of the bad characters. If it doesn't work, then you should try a different approach.

One thing to try is to use Word in your "clean up" operations. Copy the data from Excel to a Word document (paste it as regular text), and then replace the offending characters. You can then paste the data back into Excel. Some people report that they get exactly the results they want by using this round-trip approach to working with the data.

You can, of course, use a macro to get rid of the offending characters. It isn't too difficult to create your own version of the CLEAN worksheet function that, instead of simply removing non-printing characters, replaces them with spaces. Consider the following example macro:

Function ReplaceClean1(sText As String, Optional sSubText As String = " ")
    Dim J As Integer
    Dim vAddText

    vAddText = Array(Chr(129), Chr(141), Chr(143), Chr(144), Chr(157))
    For J = 1 To 31
        sText = Replace(sText, Chr(J), sSubText)
    Next
    For J = 0 To UBound(vAddText)
        sText = Replace(sText, vAddText(J), sSubText)
    Next
    ReplaceClean = sText
End Function

You use this function in the following manner within your worksheet:

=ReplaceClean1(B14)

In this case, all non-printing characters in cell B14 are replaced with a space. If you want the characters replaced with something else, just provide the text to replace with. The following example replaces the non-printing characters with a dash:

=ReplaceClean1(A1,"-")

The following usage simply removes the non-printing characters, the same as the CLEAN function:

=ReplaceClean1(A1,"")

If you look back at the ReplaceClean1 macro presented earlier, you see that it uses the Replace function. This VBA function is not available in all the versions of VBA used with the different versions of Excel. If you try the macro and you get an error on one of the lines that use the Replace function, then use this version of the ReplaceClean macro instead:

Function ReplaceClean2(sText As String, Optional sSubText As String = " ")
    Dim J As Integer
    Dim vAddText
    Dim aWF As WorksheetFunction
    Set aWF = Application.WorksheetFunction

    vAddText = Array(Chr(129), Chr(141), Chr(143), Chr(144), Chr(157))
    For J = 1 To 31
        sText = aWF.Substitute(sText, Chr(J), sSubText)
    Next
    For J = 0 To UBound(vAddText)
        sText = aWF.Substitute(sText, vAddText(J), sSubText)
    Next
    ReplaceClean = sText
    Set aWF = Nothing
End Function

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2947) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Getting Rid of Non-Printing Characters Intelligently.

Related Tips:

Change Formatting Based On Your Data! Conditional formatting provides a way for you to adjust the appearance of your data based on the data itself. Discover how to put this amazingly powerful feature to work for you, today. This comprehensive volume is available in two editions. Check out Excel Conditional Formatting today!

 

Comments for this tip:

Excellent :)    01 Apr 2013, 10:09
If you can't seem to copy the unprintable character into the Replace dialog (e.g., the unprintable text is at the end of the line) try this:
- Use F2 to open the cell for editing.
- Press the cursor keys as needed until you reach the end of the line (depending on the unprintable character, it may put you onto a second line inside the cell).
- Insert a printable dummy character at the end (after the unprintable character).
- Now move your cursor back to the printable character just before the unprintable character.
- Hold down Shift and select the first printable character, the unprintable item, and the dummy character you added at the end.
- Use Cntl+C to copy it.
- Go to the Replace dialog.
- Use Ctrl+V to paste in the string.
- Delete the two printable characters bracketing the unprintable one(s).
Now you can replace the unprintable character(s).
Rick C.    20 Jan 2012, 11:11
Excel 2007 has a =clean("string") function. In Word 2007 it is application.cleanstring("string").
Gray B.    01 Nov 2011, 19:52
Thanks for the help, but note that my Excel 97 macro / function didn't work until I changed the line:
"ReplaceClean = sText"
with
"ReplaceClean1 = sText"

Typo?

Anyhow, works a treat after that very minor mod - thanks!

Cheers, Gray.

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 3+4? (To prevent automated submissions and spam.)
 
          Commenting Terms
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2013)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2013)

Our Products

Premium Newsletters

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2014 Sharon Parq Associates, Inc.