Behaviour of "Replace"

[Mike NG]

What do you think the expected result would be of the following

Function Squeeze(sString As String) As String

Squeeze = Trim(sString)
Squeeze = Replace(Squeeze, " ", " ") 'two spaces and one space

End Function

Dim X as String

X = "Two pints" '10 spaces
X = Squeeze(X)
Msgbox X
Msgbox Len(X)

Returns a string in which a specified substring has been replaced with
another substring a specified number of times.

Syntax

Replace(expression, find, replace[, start[, count[, compare]]])

The Replace function syntax has these named arguments:

Part Description
expression Required. String expression containing substring to replace.
find Required. Substring being searched for.
replace Required. Replacement substring.
start Optional. Position within expression where substring search is to
begin. If omitted, 1 is assumed.
count Optional. Number of substring substitutions to perform. If
omitted, the default value is –1, which means make all possible
substitutions.


I'd expect all of the 10 spaces to be compressed into one. What do you
think

NB I only think this function works with XL2000 +


For my solution, I am going to have to call Squeeze recursively or
Replace repeatedly.

 

[Orlando Magalhães Filho]

Hi Mike NG,

I don't know if I understood you issue. But if you want some changes on your
code to clean multiple space try this:

Function Squeeze(sString As String) As String
Squeeze = sString
Do
Squeeze = Replace(Squeeze, " ", " ") 'two spaces and one space
Loop While InStr(1, Squeeze, " ") > 0
End Function

Sub Teste()
Dim X As String
X = "Two pints" '10 spaces
X = Squeeze(X)
MsgBox X
MsgBox Len(X)
End Sub


HTH

 

[Tom Ogilvy]

Function Squeeze(sString as String) as String
Squeeze = WorksheetFunction.Trim(sString)
End Function

should do what you want.

Regards,
Tom Ogilvy

 

[J.E. McGimpsey]

Screwed up my example (though it still illustrates the point). Lest
someone object that you shouldn't replace a space with a space in
any case, this slightly more meaningful example would also result in
an infinite loop (or rather, an overflow when the string hit 32768
characters):

Replace("Two Pints", " ", "more ") 'one space each

 

[Mike NG]

Replace() isn't recursive - it will replace each pair of spaces with
a single space as it "travels" the string left to right. Once it's
replaced the two spaces in positions 4&5, it continues with position
6. Any other way lies madness... Imagine

Replace("Two Pints", " ", " ") 'one space each

if the method started at the character it replaced with, it would
get stuck in an infinite loop.

Well that would be silly thing to do wouldn't it