Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Behaviour of "Replace"
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Behaviour of "Replace"
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Behaviour of "Replace"
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Behaviour of "Replace"
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 In article , "J.E. McGimpsey" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Behaviour of "Replace"
On Sat, 12 Jul 2003 at 15:56:15, J.E. McGimpsey (J.E. McGimpsey
) wrote: 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 -- Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Find" a wildcard as a place marker and "replace" with original va | Excel Discussion (Misc queries) | |||
"Abnormal behaviour" data entry form | Excel Discussion (Misc queries) | |||
How to replace "#N/A" w "0"when vlookup couldn't find the match? | Excel Discussion (Misc queries) | |||
Replace dialog should put focus on "Find What" not "Replace With" | Excel Discussion (Misc queries) | |||
Strange "FormatCells" behaviour | Excel Worksheet Functions |