Removing special characters and extra white space

D

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
 
K

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 >


=?Utf-8?B?bWVkaWFsaW50?= said:
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
Click to expand...
 
D

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
 
D

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
 
K

keepITcool

Function stripped$(s$)
Const ok = "[#A-z]" '<remove the space here
Dim i%, t$, r$
For i = 1 To Len(s)
t = Mid(s, i, 1)
r = r & iif(t Like ok,t," ")
Next
'optional remove double spaces
'it has to be the worksheetfunction
'vba trim just trims on the outside
r=application.worksheetfunction.trim(r)

stripped = r
End Function

off the cuff.. hope it works :)


keepITcool

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

Gord Dibben

doug

Sub TRIM_EXTRA_SPACES()
Dim cell As Range
For Each cell In Selection
If (Not IsEmpty(cell)) And _
Not IsNumeric(cell.Value) And _
InStr(cell.Formula, "=") = 0 _
Then cell.Value = Application.Trim(cell.Value)
Next
End Sub


Gord Dibben Excel MVP
 
Top