Removing special characters and extra white space

[dougmcc1]

How do you remove the white space between words in a macro? I want t
remove any spaces greater than 1 and replace them with 1 space. I als
would like to know how to remove question marks and asterisks (I'
removing all special characters but those two in peticular ar
tricky).

I came across the code() function and realized that the only codes
need are 65-90 (A-Z), 97-122 (a-z), 48-57 (0-9), and 32 (space). So I'
like to remove any codes that arent equal to any of those numbers.

Thanks

 

[keepITcool]

a bit simpler:

Function stripped$(s$)
Const ok = "[#A-z ]"
Dim i%, t$, r$
For i = 1 To Len(s)
t = Mid(s, i, 1)
If t Like ok Then r = r & t
Next
stripped = r
End Function


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

A function like this will do it.

msgbox textonly("Hello, World?!")

Public Function TextOnly(strIn As String) As String
Dim nLoop As Integer
Dim rVal As String
Dim sChar As Integer
Dim PrevSpace As Boolean
For nLoop = 1 To Len(strIn)
sChar = Asc(Mid(strIn, nLoop, 1))
Select Case sChar
Case 65 To 90, 97 To 122, 48 To 57:
rVal = rVal + Chr(sChar)
PrevSpace = False
Case 32:
If Not PrevSpace Then
rVal = rVal + Chr(32)
End If
PrevSpace = True
Case Else:
PrevSpace = False
End Select
Next nLoop
TextOnly = rVal
End Function

 

[dougmcc1]

Thanks, I tried both examples and only got the second one to work.
should have mentioned in my first post that I dont want to just remov
the special characters, but replace them with spaces.

I played around with & " " but no luck. Any ideas?

Thanks again

 

[dougmcc1]

I was able to get the macro to replace certain special characters with
space, but now I get some words with many spaces in between them. I us
trim to remove the whitespace from the ends of the final output strin
but how do I remove the whitespace between the words