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.

One way to replace the extra internal spaces is to delete all and put back
one between each letter. This won't work for aa b ccc d

Option Explicit
Sub trimextraspaces()
Dim i As Double
Dim ms As String
With ActiveCell
..Replace " ", ""
For i = 1 To Len(.Value)
ms = ms & Mid(.Value, i, 1) & " "
Next i
..Value = ms
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Joe User" <joeu2004> wrote in message
news:%(E-Mail Removed)...
> 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.

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 :
#(E-Mail Removed)...
> 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.

"Charabeuh" <(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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 :
> #(E-Mail Removed)...
>> 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.
>

It's what I should have suggested. Look in the help index vba for trim and
then look in the excel help index for trim to see the diff.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Joe User" <joeu2004> wrote in message
news:(E-Mail Removed)...
> "Charabeuh" <(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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 :
>> #(E-Mail Removed)...
>>> 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.
>>
>

I think your form is better than mine since TRIM is a member of the
WorksheetFunction object

The complete syntax in the object model should be:
mylist = Application.WorksheetFunction.Trim(mylist)



"Joe User" <joeu2004> a écrit dans le message de groupe de discussion :
(E-Mail Removed)...
> "Charabeuh" <(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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 :
>> #(E-Mail Removed)...
>>> 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.
>>
>

"Charabeuh" <(E-Mail Removed)> wrote:
> I think your form is better than mine since TRIM
> is a member of the WorksheetFunction object

But Application.Trim does work. My question was: is there any difference?

To reiterate: I know they seem to behave the same. I am asking if there is
any reason to use one instead of the other. For example, performance?

Odd.... I do not see Trim listed as an Application property or method. Why
does Application.Trim (that syntax) work in the first place?


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

"Charabeuh" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>I think your form is better than mine since TRIM is a member of the
>WorksheetFunction object
>
> The complete syntax in the object model should be:
> mylist = Application.WorksheetFunction.Trim(mylist)
>
>
>
> "Joe User" <joeu2004> a écrit dans le message de groupe de discussion :
> (E-Mail Removed)...
>> "Charabeuh" <(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> 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 :
>>> #(E-Mail Removed)...
>>>> 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.
>>>
>>