Searching (and replacing) tabs

[Phil Hibbs]

I have spreadsheets (provided by clients) that contain tab characters.
How can I search for and replace these?

Phil Hibbs.

 

[Don Guillett]

try
Sub DeleteTabCharacters()
Cells.Replace What:="Chr(9)", Replacement:="_", LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

 

[Dave Peterson]

Typo alert. (Too many "'s.)

Sub DeleteTabCharacters()
Cells.Replace What:=Chr(9), Replacement:="_", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
End Sub

Chr(9) could be replaced with vbTab, too.

 

[Phil Hibbs]

Thanks. The mistake I made when trying that approach was assuming that
I could test it by pasting a TAB character into Excel from Notepad,
but it gets pasted in as a space, but I assumed it was the Find() that
was not working. The actual spreadsheets are very large and on a
colleague's PC.

Phil Hibbs.