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

Getting Rid of Non-Printing Characters Intelligently

by Allen Wyatt
(last updated November 1, 2014)

2

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. 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 ReplaceClean(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:

=ReplaceClean(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:

=ReplaceClean(A1,"-")

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

=ReplaceClean(A1,"")

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6126) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Getting Rid of Non-Printing Characters Intelligently.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Unwanted Styles

Want to get rid of some styles in a document that you don't need any more? It can be a difficult thing to do, unless you try ...

Discover More

Backing Up Label Layouts

Once you create a custom label layout it is a good idea to backup the layout on a different hard drive in the event of a ...

Discover More

Using the Copy or Move Text Keys

Most people use the Clipboard to copy and move text in Word. Before the Clipboard, Word used F2 to move text and Shift+F2 to ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Automatically Moving from Cell to Cell when Entering Data

As you enter data in a worksheet, you may want to have Excel automatically move from cell to cell based on the length of what ...

Discover More

Copying Data without Leaving the Currently Selected Cell

Copying from one cell to another is easy when editing your worksheet. Doing the copying without selecting a cell other than ...

Discover More

Ensuring Standard Units During Data Entry

Need to make sure that information entered in a worksheet is always in a given unit of measurement? It's not as easy of a ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 - 0?

2016-12-28 10:30:47

Dr. Demento

Is there any difference between the lines:
For J = 1 To 31
sText = Replace(sText, Chr(J), sSubText)
Next

and
sText = Application.WorksheetFunction.Clean(sText)??

Wouldn't the latter be faster?


2014-11-10 02:13:19

shas

Effective one


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.