How do I find replace special characters?

Z

zzapper

Hi
How can I find/replace all TAB characters (decimal 9) in an excel file?

I mean how do you specify a TAB in the search box?
 
D

Dave Peterson

Sometimes hitting and holding the alt-key while typing the character number will
work (like alt-0010 for alt-enters).

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
 
Top