Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Special Characters
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? |
#2
|
|||
|
|||
Special Characters
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 aftamath wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Highlight special (dis-allowed) characters in a worksheet | Excel Discussion (Misc queries) | |||
Special Characters on Labels | Excel Discussion (Misc queries) | |||
excel data label format special number characters (part 2) | Excel Discussion (Misc queries) | |||
REMOVE SPECIAL CHARACTERS FROM TEXT CELLS | Excel Worksheet Functions | |||
Special Characters in Headers and Footers | Excel Discussion (Misc queries) |