"Charabeuh" wrote:
mylist = Application.Trim(mylist)

Good. Thanks.

What's the difference, if any, between that and:

mylist = WorksheetFunction.Trim(mylist)

I know they are the same functionally. My question is: is there any reason
to use one form instead of the other?


----- original message -----

"Charabeuh" wrote in message
...
Hello,

Try this in your VBA code:

mylist = Application.Trim(mylist)

' in this syntax Trim is the Excel function TRIM
' and not the VBA function TRIM.
' The excel function TRIM should do what you aim to do

Bye



"Joe User" <joeu2004 a écrit dans le message de groupe de discussion :
...
In VBA, I want to replace multiple interstitial spaces with one space in
a string variable. For example, " a b c d e f g "
should become "a b c d e f g".

How can I do this using just standard VBA features? (I want to avoid
adding References.)

The following is close to what I want:

Dim mylist as String
[....]
mylist = Replace(Trim(mylist)," "," ")

The problem is: apparently Replace starts scanning again after the
replacement text. Consequently, for example, 4 spaces are reduced to 2
spaces instead of 1.

I 'spose I could do something like:

mylist = Trim(mylist)
Do
mylist0 = mylist
mylist = Replace(mylist," "," ")
Loop until mylist = mylist0

That works. But is there something better?

I would like an answer to the question above, for my edification.

But my purpose is to compress spaces before using Split(mylist) to
separate the "words".

Is there a way to do the Split without having to compress multiple spaces
first?

I am using Excel 2003 SP3 with VBA 6.5.1024.