How to compress interstitial spaces?
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. |
How to compress interstitial spaces?
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 : ... 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. |
How to compress interstitial spaces?
"Charabeuh" wrote:
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 ----- "Charabeuh" wrote in 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 : ... 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. |
How to compress interstitial spaces?
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 "Joe User" <joeu2004 wrote in message ... "Charabeuh" wrote: 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 ----- "Charabeuh" wrote in 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 : ... 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. |
How to compress interstitial spaces?
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" <joeu2004 a écrit dans le message de groupe de discussion : ... "Charabeuh" wrote: 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 ----- "Charabeuh" wrote in 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 : ... 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. |
How to compress interstitial spaces?
"Charabeuh" wrote:
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 ----- "Charabeuh" wrote in 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) "Joe User" <joeu2004 a écrit dans le message de groupe de discussion : ... "Charabeuh" wrote: 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 ----- "Charabeuh" wrote in 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 : ... 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. |
How to compress interstitial spaces?
Some discussions about the subject...
http://www.dailydoseofexcel.com/arch...nction-method/ The behaviour for the function VLOOKUP() when the lookup value doesn't exist in the lookup range is different between the two forms. "Joe User" <joeu2004 a écrit dans le message de groupe de discussion : ... "Charabeuh" wrote: 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 ----- "Charabeuh" wrote in 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) "Joe User" <joeu2004 a écrit dans le message de groupe de discussion : ... "Charabeuh" wrote: 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 ----- "Charabeuh" wrote in 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 : ... 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. |
All times are GMT +1. The time now is 09:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com