edit out ascii char 09

[Guest]

Hi.

I have a sheet created in SAS that has ascii char 09 which displays on the
screen as a small square box (but prints OK),

If I use the clear function, it removes the character all together leaving
no space between the words.

I can do a replace, but since this is a large sheet, and the cells have
anywhere from zero to five char 9's, I have not been able to figure out a way
of fixing this.

Any suggestions

 

[Dave Peterson]

You could use a helper cell with a formula:

=substitute(a1,char(9)," ")

Then copy|paste special|values and toss the bad column.

Or maybe you could run a little macro...

If you want to replace these characters with something else (space or
nothing???), you could use a macro to do the edit|replace's:

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(9))

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), Replacement:=" ", _
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