Special Characters

[Guest]

I'm having problems with Special Characters. When I cut and paste a list
from an email that has been spaced with tabbing, the tabbed spaces show up as
special characters called non-breaking spaces in Microsoft Word, and I don't
know what they show up as in Excel after cut and paste. The Trim code I have
in VBA doesn't recognize these spaces. How do I get excel or VBA to
recognize the characters?

 

[Dave Peterson]

If they were tabs, then I don't think they'd be non-breaking spaces.

The tab character is =chr(9).
The non-breaking space in HTML is =chr(160).

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

If you do see a box, then you can either fix it via a helper cell or a macro:

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

or as 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), Chr(160)) '<--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