TrimAll ; $nbsp; alt+0160; Cell View Add-In

[LiCal]

I pasted into Excel 2003 from the web page that has a lot of &nbsp
around the data that I need to trim off.

1) I ran F. David McRitchie's Trimall(), but it could not do it.
(http://www.mvps.org/dmcritchie/excel/join.htm#trimall)

2) Chip Pearson 's The Cell View Add-In found all those $B!u(Bnbsp as
Chr(063) which is actually "?"
(http://www.cpearson.com/excel/CellView.aspx)

3) Using Ctrl+F (Find) and keying "alt+0160"; was able to find all
those "&nbsp"
similarly, Ctrl+H (Replace) would also be able to replace all
those "alt+0160" with designated replacements.

I used "alt+032" as replacement; however, trimall() would not trim
the extra spaces off;
yet worksheet function =trim() would do it.


What maybe the problems/solutions ?

==========================

How to code "alt+0160" into TrimAll() VBA for find/replace?

 

[Peter T]

Sub sampledata()
s = "ab" & Chr(160) & "cd" & Chr(160) & "ef"

Range("A1:A10") = s

End Sub

Sub test()
ActiveSheet.Cells.Replace What:=Chr(160), _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End Sub

 

[LiCal]

My Excel2003 worked differently, please see my feedbacks below

Sub sampledata()
s = "ab" & Chr(160) & "cd" & Chr(160) & "ef"

only got "ab" in the cells; len=2

I tried the below to have "ab cd ef" visible in the cells; Len=8
s = "= ""ab"" & CHAR(0160) & ""cd"" & CHAR(0160) & ""ef"""


=== Cell View Cell Contents as ==========
Char a b
Dec 097 098 000 099 100 000 101 102
===== ==============================

Not recoginizing 160; nor showing "cd" "ef"?


In addition,
1) the Test Sub (below) does no see Chr(160)
2) Ctrl + F/H does not see Alt+0160 in the Cells