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

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

---
Orlando Magalhães Filho

(So that you get best and rapid solution and all may benefit from the
discussion, please reply within the newsgroup, not in email)



"Mike NG" escreveu na mensagem
...
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.
--
Mike

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

should do what you want.

Regards,
Tom Ogilvy


Mike NG wrote in message
...
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.
--
Mike