How to compress interstitial spaces?

  • Thread starter Thread starter Joe User
  • Start date Start date
J

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.
 
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
 
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
 
Charabeuh said:
mylist = Application.Trim(mylist)
Click to expand...

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 said:
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 said:
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.
Click to expand...
Click to expand...
 
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)
Joe User said:
Charabeuh said:
mylist = Application.Trim(mylist)
Click to expand...

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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
Charabeuh said:
mylist = Application.Trim(mylist)
Click to expand...

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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Charabeuh said:
I think your form is better than mine since TRIM
is a member of the WorksheetFunction object
Click to expand...

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 said:
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 said:
Charabeuh said:
mylist = Application.Trim(mylist)
Click to expand...

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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top