Replace non printable characters

[TonyL]

I have a column that has a series of non printable characters, mainly the
sqaure character. I want to replace them with a space. I tried the CLEAN
function but it replaces the characters with nothing & therefore joins words
together. TIA

 

[OssieMac]

Hi Tony,

You can use substitute function to replace characters with other characters.

Example where CHAR(2) is the code for the non printable character being
replaced with a space:

=SUBSTITUTE(E2,CHAR(2)," ")

You indicated that most of the characters are the square. I am not sure what
the code is for that but assuming that you know that it is the 6th character
in a string then to find the code use the following formula which will return
the numerical code for the 6th character in E2:

=CODE(MID(E2,6,1))

I think that you would need a macro to find and replace all the non
printable characters where the actual codes are random but if you only have a
limited number of codes that can be identified then the SUBSTITUTE function
will do it for you.

Regards,

OssieMac

 

[David McRitchie]

The squares you see are most likely line breaks, see if turning
on cell wrap changes things. But it would be important to you
find out exactly what character. You can specifically find out what
the character is with =CODE(A1) if you reduce A1 to that
single character. More information in these two topics.
Determine if a cell is Number or Text and why is it seen that way
http://www.mvps.org/dmcritchie/excel/join.htm#debugformat
TrimALL macro http://www.mvps.org/dmcritchie/excel/join.htm#trimall

If you are not familiar with codes see (decimal table is at bottom)
ASCII (American Standard Code for Information Interchange) Code
http://www.december.com/html/spec/ascii.html

To do what you asked for, if they are text cells, you could copy
the square into the find of Replace (Ctrl+H) and replace it with
a single space, but I would advise you to find out what you actually
have first.

The use of the CLEAN Worksheet Function to remove unprintable characters
will only remove CHAR(0) through CHAR(31), Char(129), Char(141), Char(143),
and Char(144). It will have no effect on Char(160). The use of CLEAN
removes the character, you might very well have preferred a space or other
character.
Which in fact you did indicate you wanted a space.

to OssieMac, the square represents any unprintable (/undisplayable)
character in the current font, the character could be a control character
and not have a character representation in any font. The required
blank character CHAR(160) is a non-breaking space and therefore it
is printable/displayable though is not the same as a space Char(32).

 

[TonyL]

Hi OssieMac

Many thanks for the reply.

I tried the SUBSTITUTE function & it did not work.

I copied the column into Word & it turns out that the non printable
characters (the square) were manual line breaks which wasn't evident in
Excel. I did a find & replace in Word & copied back into Excel & that fixed
the problem.

Thanks for the tip about the CHAR(2). That is handy to know.

 

[Dave Peterson]

Saved from a previous post.

You may want to try a macro from David McRitchie. Depending on what's in the
cell, it may work for you.
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()"

If that doesn't work...

Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.htm

Then you can either fix it via a helper cell or a macro:

=substitute(a1,char(##),"")
or
=substitute(a1,char(##)," ")

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(##), Chr(##)) '<--What showed up in CellView?

myGoodChars = Array(" ","") '<--what's the new character, "" for nothing?

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, _
MatchCase:=False
Next iCtr

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm