Replacing multiple spaces

[bobfin]

We get data from a vendor where one field has multiple spaces scattered throughout the value. We want to replace all occurences of multiple spaces with just a single space. Running the Replace function once does not get rid of all occurences. We tried using a function to repeatedly replace all double spaces with single spaces and it blanked the entire value. What's a better way to code the following function?

Function ReduceSpaces(MyStr As Variant) As Variant
On Error GoTo ReduceSpacesError

Do Until InStr(MyStr, " ") = 0
MyStr = Replace(MyStr, " ", " ")
Loop

ReduceSpacesEnd:
Exit Function

ReduceSpacesError:
MsgBox Error$
Resume ReduceSpacesEnd

End Function

 

[tkpstock]

All you need to do is return the value...!

Add this just after the loop:

ReduceSpaces = MyStr

And make sure you are replacing " " with " " (double-spaces with single-spaces). Your code (as posted) had single spaces replacing single spaces...

Note Added: After posting this, I noticed that vBulletin automatically replaces the double-spaces with single spaces...

 

[bobfin]

That did it! Another instance of a cranial blindspot. Thanks.