Try:

s = Replace(s,   ,   )

Try this. Found it on the web (posted by an unknown user). I have used it and
it works great.

Remove Spaces
This is one of those functions  that, having built it, I wondered how I had ever
managed without! It came about because I wanted to build a list of email
addresses from a list of people's names. It was a big list! The plan was simple
enough... the email address would be firstname-dot-lastname-@-domainname.

This is very fast....

Function fncStripJoin(spS As String) As String

Dim slS() As String

slS = Split(spS," ")
fncStripJoin = Join(slS, "")
' ***********************************************************************
End Function

I also use a "generic" procedure which will strip any single or set of
characters including control characters.

Function fncStripChrs(strpString As String)
' Strip some characters.

Dim intlM As Integer
Dim strlS As String

strlS = ""
For intlM = 1 To Len(strpString)
Select Case Asc(Mid(strpString, intlM, 1))
Case 13, 7, 10, 9, 150, 147 ' List of Ascii codes for stripping.
Case Else
strlS = strlS & Mid(strpString, intlM, 1)
End Select
Next
fncStripChrs = strlS
' ***********************************************************************
End Function

... And finally this one.

Function fncAllTrim(spS As String) _
As String
' Trim all spaces  from a string.

Dim slS As String
Dim ilChr As Integer
Dim slChr As String * 1

slS = ""
For ilChr = 1 To Len(spS)
slChr = Mid(spS, ilChr, 1)
If slChr <> " " Then
slS = slS & slChr
End If
Next ilChr
fncAllTrim = slS
' ***********************************************************************
End Function

I did have a function  where you passed a list of chrs to zap but I couldn't
find it... Sorry.

I like the split/join, and my last email was messed up. I think the simplest
is:



s = Replace(s, " ", "")

I really just threw it in for novelty... but it is fast. I like
SPLIT. I use it a lot in parsing and so on. Replace is fast too. I like the
REPLACE as well but I'd prob put it in a 1 to 5 FOR.. NEXT to get rid of
doubles spaces. The SPLIT/JOIN suffers the same prob.

But I think you're right ... The REPLACE is probably the simplest.

The loop through for ASCII codes is the best I think. In the one where I
passed a string I did an INSTR on the passed list for the ascii code of the
character. It meant I could pass an OPTIONAL string of ASCII codes like
"20,13,10".

I had a big problem  once with "smart" quotes, and that's what I used that
for.

Both Split/Join and Replace both seem to handle the issue of multiple space
without difficulty. Here is some really bad test code that displays the
desired results:



Sub ab()

Dim s As String

Dim a As Variant

Dim b As String

s = "Hello folks, how y'all doing today???"

a = Split(s, " ")

b = Join(a, "")

MsgBox b ' the split/join method

MsgBox Replace(s, " ", "") ' the Replace method

End Sub



I like your function  that has all that added functionality -- could come in
handy someday.

Ok.... I admit I "thought" it would give erroneous results for multiple
spaces. Glad to see I wuz wrong!!!

Yupper... As I said, I use it for parsing a lot so it's great for running
through each of the SPLIT array items and testing for stuff.

At the mo I'm working on a routine for sorting Dims and getting rid of the
ones not referenced. SPLIT works well here... I SPLIT each line and sort the
results on the 1st dimension (Nothing to do with little green women... My
surname being Green.. ), arrays normally being zero based.