How to compress interstitial spaces?

[Joe User]

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.

 

[Don Guillett]

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

 

[Charabeuh]

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]

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 -----

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

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.

 

[Don Guillett]

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 address removed)

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 -----

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

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]

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)

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 -----

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

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.

 

[Joe User]

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 -----

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)

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 -----

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 :
#[email protected]...
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]

Some discussions about the subject...

http://www.dailydoseofexcel.com/archives/2004/09/24/the-worksheetfunction-method/

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

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 -----

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)

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 -----

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 :
#[email protected]...
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.