Tab char in text data

[glen.e.mettler]

I receive data dump from Crystal Reports into an Excel format. The
text data in some cells contains special characters - mostly the Tab
and leading/trailing spaces. I can trim the data and eliminate the
leading/trailing spaces but I can't seem to eliminate the Tabs.

Is there a way to find and replace/delete the Tab charcter?

Glen

 

[Dave Peterson]

If you're not sure what that character is, you may want to use Chip Pearson's
addin "CellView".
http://www.cpearson.com/excel/CellView.htm

If you know that it's a tab character (char(9)), you could use

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

Replace 9 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(9)) '<--What showed up in CellView?

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

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

 

[aresen]

I receive data dump from Crystal Reports into an Excel format. The
text data in some cells contains special characters - mostly the Tab
and leading/trailing spaces. I can trim the data and eliminate the
leading/trailing spaces but I can't seem to eliminate the Tabs.

Is there a way to find and replace/delete the Tab charcter?

Glen

Use the CLEAN function