Replace non-printing characters.


Chris Watts

I have output some data from a large non-Excel database as a CSV file. I
can import it into Excel just fine but some of the cells include, as well as
text, a few characters that appear as as small square boxes - presumably
special characters of some form. I wish to replace these. I have tried
Find using Alt 010 and Alt 013 but they don't seem to be CR or LF. How can
I determine what characters these are?

I could, of course, just write a macro to replace all non-alphabetic etc
characters. But I wish to describe to a public audience how to do the task
themselves and cannot assume that they can write macros or have access to
one that I have written.

any thoughts?




Dave Peterson

Saved from a previous post.

Chip Pearson has a very nice addin that will help determine what that
character(s) is:

Depending on what that character is, you may be able to use alt-#### (from the
number keypad) to enter the character into the Other box in the text to columns
wizard dialog.

In fact, you may be able to select the character (in the formula bar), and copy
it. Then use ctrl-v to paste into that text to columns Other box.

You may be able to use Edit|Replace to change the character--Some characters can
be entered by holding the alt-key and typing the hex number on the numeric
keypad. For example, alt-0010 (or ctrl-j) can be used for linefeeds. But I've
never been able to get alt-0013 to work for carriage returns.

Another alternative is to fix it via a formula:


Replace ## with the ASCII value you see in Chip's addin.

Or you could use a macro (after using Chip's CellView addin):

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(##)) '<--What showed up in CellView?

myGoodChars = Array("")

If UBound(myGoodChars) <> UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
Next iCtr

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:

David McRitchie has an intro to macros:

Ron de Bruin's intro to macros:

(General, Regular and Standard modules all describe the same thing.)

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question