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

--
Message posted from http://www.ExcelForum.com

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


?B?bWVkaWFsaW50?= wrote:

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 " wrote:

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

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

Thanks.


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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


dougmcc1 wrote:

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


---
Message posted from http://www.ExcelForum.com/

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

On Wed, 23 Jun 2004 14:54:48 -0500, dougmcc1
wrote:

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


---
Message posted from http://www.ExcelForum.com/