Here's a function from my standard library that I use to trim and
single space a string. It is pure VBA, with no reliance on Excel, so
can be used in any VBA application.

Function SingleSpace(S As String) As String
Dim T As String
Dim N As Long
T = Trim(S)
N = InStr(1, T, Space(2), vbBinaryCompare)
Do Until N = 0
T = Replace(T, Space(2), Space(1))
N = InStr(1, T, Space(2), vbBinaryCompare)
Loop
SingleSpace = T
End Function

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com


On Mon, 24 May 2010 00:14:12 -0400, "Rick Rothstein"
wrote:

When I run your code, I get two spaces left between words, not one.

I'm guessing you didn't see my first posting in this thread, did you? Here
is what I posted modified to fit your function header...

Function StripExtraSpaces(ByVal InputString As String) As String
StripExtraSpaces = WorksheetFunction.Trim(InputString)
End Function

This code, which is much shorter than yours, outputs what I think you
intended your function to output.