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

"Joe User" <joeu2004 wrote in message
...
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 :
...
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" 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.

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

"Joe User" <joeu2004 wrote in message
...
"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.

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 :
...
"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.

"Charabeuh" 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" wrote in message
...
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 :
...
"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.

Some discussions about the subject...

http://www.dailydoseofexcel.com/arch...nction-method/

The behaviour for the function VLOOKUP() when the lookup value doesn't exist in the lookup range is different between the two forms.




"Joe User" <joeu2004 a écrit dans le message de groupe de discussion : ...
"Charabeuh" 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" wrote in message
...
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 :
...
"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.