Trim does not work

[Guest]

I'm trying to create a macro to format a lot of data for a person who's not
very sophisticated with Excel.
I've followed one of the suggestions on this post using msgbox
asc(left(A5,1)) to find out that what appears to be "spaces" are not actually
spaces but is chr(65). The earlier post also said that chr(160) is common.
My question is:
How do I detect what chr it is so that I can remove it. The Replace
functions works to remove the chr only if I can identify what the characters
are.
Thanks in advance

 

[Zack Barresse]

Hi there Samantha,

Character 65 is "A". Is that not acceptable? If not, what *is* acceptable
then? You can always check each cell value like so ...

Sub StripChars()
Dim rng As Range, i As Long
For Each rng In Selection
For i = Len(rng.Value) To 1 Step -1
Select Case Asc(Mid(rng.Value, i, 1))
Case 48 To 57, 65 To 90, 97 To 122
'numbers, upper case, lower case
Case Else
rng.Value = Replace(rng.Value, Mid(rng.Value, i, 1), "")
End Select
Next i
Next rng
End Sub

This will take out everything except 0-9, A-Z and a-z.

HTH

Regards,
Zack Barresse