DocAElstein
02-14-2018, 03:09 AM
Built in VBA methods and functions to alter the contents of existing character strings.
Hi
I recently got quite confused by the different ways in VBA to alter the contents of existing character strings using built in Methods and functions. ( One reason being that the words Replace and Substitute are frequently used somewhat imprecisely, that is to say they might in some situations be used interchangeably for no particular reason).
I made myself some notes, mainly to clarify the differences in the Methods and Functions. I thought it might be useful to share them.
I am primarily interested here in a short comparison rather than a detailed description of each. I have not detailed all argument options and possibilities, partly because I have not figured them all out yet. I might extend the explanations therefore at a later date.
So I may go into each way in more detail separately later.
Or if anyone else would like to add any comments then I would be very interested.
For this simple comparison I will not use named arguments. So the position of all arguments must be used in the order that I use them.
I currently am aware of 7 ways to consider, or 5 if you group the last 3 together.
I have put them in an order of what I estimate is their order of “popularity”
_1 Replace( ) _ Function
_2 Range.Replace _ Method
_3 WorksheetFunction.Replace _ Method
_4 WorksheetFunction.Substitute _ Method
_5(i)(ii)(iii) LSet _ Mid _ RSet _ Statements.
_1) Replace Function https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/replace-function
This works on a string or string variable. It returns the same or modified string
Broadly speaking this does two things
_ It returns part of a string. The returned portion will include the right hand side of original string. In other words the part removed will be part of the left hand side. ( This removed portion can also be nothing, or rather nothing is removed so that the original string is returned,_…
_..but) / and
_ the returned string has 1 or more occurrences counting from the left of a character or characters replaced by a character or characters. This replaces by substitution of a string part with another string part
ReturnedString = Replace(OriginalString , StringToLookFor , ReplacementString ,
__________________________CharacterNumberfromLeftO fStringToLookInAndFromWhereToReturn ,
_____________________________________NumberOfOccur ancesLookingFromTheLeftToReplace ,
__________________________________________________ ___CompareMode)
ReturnedString = Replace("1A3a5A7a9", "a", "x", 3, 2, vbBinaryCompare)
_ ' In ReturnedString is "3x5A7x9"
ReturnedString = Replace("1A3a5A7a9", "a", "x", 3, 2, vbTextCompare)
_ ' In ReturnedString is "3x5x7a9"
In the first of the examples an “exact computer” type comparison is made. In the second a “text” type comparison is made which is non case sensitive. ( Possibly this makes the first potentially faster https://www.excelforum.com/excel-programming-vba-macros/1098104-vba-application-match-not-working.html#post4156897 )
Most commonly just the first three arguments are used, which by default results in all occurrences being replaced and the entire string with all alterations is returned. The compare mode is usually the exact one by default:
____ReturnedString = Replace("1A3a5A7a9", "a", "x")
' In ReturnedString usually "1A3x5A7x9"
_2 Range.Replace Method https://msdn.microsoft.com/de-de/vba/excel-vba/articles/range-replace-method-excel
As its name implies, this is applied to a spreadsheet range.
This allows to replace all occurrences of a character or substring in the contents of cells in a range with a character or substring. You cannot specify how many occurrences: all are changed. The argument syntax has some similarities to the Range.Find Method and there are many options
Pseudo Like:
Dim Rng As Range: Set Rng=Worksheets("Tabelle1").Range("A1:B3")
In this _ Rng.Replace WhatTo Replace , WithWhatToBeReplaced , , , , , ,
Before:
Row\Col
A
B
1AB
2CD
3aG
Worksheet: Tabelle1
After
Rng.Replace "a", "x"
Row\Col
A
B
1xB
2CD
3xG
Worksheet: Tabelle1
_3 WorksheetFunction.Replace Method https://msdn.microsoft.com/de-de/vba/excel-vba/articles/worksheetfunction-replace-method-excel
This works on a string or string variable. It returns the same or modified string
This replaces a string part by position:
For a single string a part specified by position in the string can be replaced by a character or string of characters.
Pseudo Like:
ReturnedString = Application.WorksheetFunction.Replace(InThis_Strin g , AtThisPositionStartingFromLeft ,
____________________________________________ ForThisNumberOfCharacters ,
________________________________________________Th isSubStringSubstituteInThatPostitiuon )
The Returned string can have a different length to the main This_String:
ReturnedString = Application.WorksheetFunction.Replace("123456789", 3, 2, "xxxxxx")
____________________ ' In ReturnedString is “12xxxxxx56789”
_4 WorksheetFunction.Substitute Method https://msdn.microsoft.com/en-us/vba/excel-vba/articles/worksheetfunction-substitute-method-excel
This works on a string or string variable. It returns the same or modified string
This replaces by substitution of a string part by another string part.
For a single string a part of the string can be replaced by another string. For multiple occurrences ( instances ) of a string part, the instance counting from the left can be specified. ( If this option is omitted then all occurrences are substituted )
Pseudo Like:
ReturnedString = Application.WorksheetFunction.Substitute(InThis_St ring , TheSubStringHere ,
_______________________________________________IsT oBeReplacedByThisString ,
__________________________________________________ _ThisOccurrenceCountingFromTheLeftToBeSubstituted )
The Returned string can have a different length to main This_String:
ReturnedString = Application.WorksheetFunction.Substitute("1A3a5A7a9", "a", "yyyyy", 2):
_______________________ ' In ReturnedString is "1A3a5A7yyyyy9"
_5) Mid Function (used pseudo as method ) / …… Statements
It has sometimes been noticed that “MID function can also be used to replace a section of the string.”
https://www.excelforum.com/word-programming-vba-macros/1175184-vba-word-repeat-character-in-string-a-number-of-times.html#post4591069
https://www.mrexcel.com/forum/excel-questions/329627-curious-use-vba-mid-function.html
Possibly this is one of three “Statements” from earlier Visual basic.
_5b) LSet Mid RSet Statements.
Working on a string variable.
Impotent characteristic is that the string length cannot be changed: The final string has the same length as the original string
_5b)(i) LSet : https://msdn.microsoft.com/en-us/library/aa266163(v=vs.60).aspx
Works on a string variable **
All of the string is overwritten, starting at the left and up to the original length. Any remaining characters are left blank
MeString = “123456789”
LSet MeString = “987”
' Result is in MeString “987xxxxxx”
** Some other possibilities to do with replacing variable types that I have not figured out yet.
_5b)(ii) RSet : https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/rset-statement
Works only on a string variable
All of the string is overwritten, starting at the right and down/ back to the original length. Any remaining characters are left blank
MeString =“123456789”
RSet MeString = “987”
' Result in MeString is "xxxxxx987”
_5b)(iii) Mid statement : https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/mid-statement
Works only on a string variable
A part, specified by position within, of a string is replaced by all or part of a given string
Mid(string, StartPositionInString, LengthFromStartPosition) = GivenString
MeString = “123456789”
Mid( MeString, 3, 2) = "cdef"
' Result in MeString is “12cd56789”
MeString = “123456789”
Mid( MeString, 3) = "cdef"
' Result in MeString is “12cdef789”
Ref
https://www.excelforum.com/word-programming-vba-macros/1175184-vba-word-repeat-character-in-string-a-number-of-times.html#post4590792
https://powerspreadsheets.com/excel-vba-replace-substitute/
http://www.functionx.com/vbaexcel/topics/strings.htm
https://usefulgyaan.wordpress.com/2013/05/10/ltrim-function-vba/
http://www.excelfox.com/forum/showthread.php/994-LTRIM()-Function-VBA-To-Remove-Spaces
Intersting uses:
Robert H. Gascon https://excelribbon.tips.net/T009600_Sorting_Letters_and_Numbers.html
( Misc refs for later use
https://excel.tips.net/T003219_Getting_Rid_of_Alphabetic_Characters.html
https://excel.tips.net/T003303_Wildcards_in_Replace_With_Text.html
http://www.eileenslounge.com/viewtopic.php?f=30&t=33843#p262151
https://www.myonlinetraininghub.com/using-substitute-function-find-count-text )
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxUbeYSvsBH2Gianox4AaABAg.9VYH-07VTyW9gJV5fDAZNe (https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxUbeYSvsBH2Gianox4AaABAg.9VYH-07VTyW9gJV5fDAZNe)
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg (https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg)
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgyhQ73u0C3V4bEPhYB4AaABAg (https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgyhQ73u0C3V4bEPhYB4AaABAg)
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgzIElpI5OFExnUyrk14AaABAg.9fsvd9zwZii9gMUka-NbIZ (https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgzIElpI5OFExnUyrk14AaABAg.9fsvd9zwZii9gMUka-NbIZ)
https://www.youtube.com/watch?v=jdPeMPT98QU (https://www.youtube.com/watch?v=jdPeMPT98QU)
https://www.youtube.com/watch?v=QdwDnUz96W0&lc=Ugx3syV3Bw6bxddVyBx4AaABAg (https://www.youtube.com/watch?v=QdwDnUz96W0&lc=Ugx3syV3Bw6bxddVyBx4AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1 (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
Hi
I recently got quite confused by the different ways in VBA to alter the contents of existing character strings using built in Methods and functions. ( One reason being that the words Replace and Substitute are frequently used somewhat imprecisely, that is to say they might in some situations be used interchangeably for no particular reason).
I made myself some notes, mainly to clarify the differences in the Methods and Functions. I thought it might be useful to share them.
I am primarily interested here in a short comparison rather than a detailed description of each. I have not detailed all argument options and possibilities, partly because I have not figured them all out yet. I might extend the explanations therefore at a later date.
So I may go into each way in more detail separately later.
Or if anyone else would like to add any comments then I would be very interested.
For this simple comparison I will not use named arguments. So the position of all arguments must be used in the order that I use them.
I currently am aware of 7 ways to consider, or 5 if you group the last 3 together.
I have put them in an order of what I estimate is their order of “popularity”
_1 Replace( ) _ Function
_2 Range.Replace _ Method
_3 WorksheetFunction.Replace _ Method
_4 WorksheetFunction.Substitute _ Method
_5(i)(ii)(iii) LSet _ Mid _ RSet _ Statements.
_1) Replace Function https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/replace-function
This works on a string or string variable. It returns the same or modified string
Broadly speaking this does two things
_ It returns part of a string. The returned portion will include the right hand side of original string. In other words the part removed will be part of the left hand side. ( This removed portion can also be nothing, or rather nothing is removed so that the original string is returned,_…
_..but) / and
_ the returned string has 1 or more occurrences counting from the left of a character or characters replaced by a character or characters. This replaces by substitution of a string part with another string part
ReturnedString = Replace(OriginalString , StringToLookFor , ReplacementString ,
__________________________CharacterNumberfromLeftO fStringToLookInAndFromWhereToReturn ,
_____________________________________NumberOfOccur ancesLookingFromTheLeftToReplace ,
__________________________________________________ ___CompareMode)
ReturnedString = Replace("1A3a5A7a9", "a", "x", 3, 2, vbBinaryCompare)
_ ' In ReturnedString is "3x5A7x9"
ReturnedString = Replace("1A3a5A7a9", "a", "x", 3, 2, vbTextCompare)
_ ' In ReturnedString is "3x5x7a9"
In the first of the examples an “exact computer” type comparison is made. In the second a “text” type comparison is made which is non case sensitive. ( Possibly this makes the first potentially faster https://www.excelforum.com/excel-programming-vba-macros/1098104-vba-application-match-not-working.html#post4156897 )
Most commonly just the first three arguments are used, which by default results in all occurrences being replaced and the entire string with all alterations is returned. The compare mode is usually the exact one by default:
____ReturnedString = Replace("1A3a5A7a9", "a", "x")
' In ReturnedString usually "1A3x5A7x9"
_2 Range.Replace Method https://msdn.microsoft.com/de-de/vba/excel-vba/articles/range-replace-method-excel
As its name implies, this is applied to a spreadsheet range.
This allows to replace all occurrences of a character or substring in the contents of cells in a range with a character or substring. You cannot specify how many occurrences: all are changed. The argument syntax has some similarities to the Range.Find Method and there are many options
Pseudo Like:
Dim Rng As Range: Set Rng=Worksheets("Tabelle1").Range("A1:B3")
In this _ Rng.Replace WhatTo Replace , WithWhatToBeReplaced , , , , , ,
Before:
Row\Col
A
B
1AB
2CD
3aG
Worksheet: Tabelle1
After
Rng.Replace "a", "x"
Row\Col
A
B
1xB
2CD
3xG
Worksheet: Tabelle1
_3 WorksheetFunction.Replace Method https://msdn.microsoft.com/de-de/vba/excel-vba/articles/worksheetfunction-replace-method-excel
This works on a string or string variable. It returns the same or modified string
This replaces a string part by position:
For a single string a part specified by position in the string can be replaced by a character or string of characters.
Pseudo Like:
ReturnedString = Application.WorksheetFunction.Replace(InThis_Strin g , AtThisPositionStartingFromLeft ,
____________________________________________ ForThisNumberOfCharacters ,
________________________________________________Th isSubStringSubstituteInThatPostitiuon )
The Returned string can have a different length to the main This_String:
ReturnedString = Application.WorksheetFunction.Replace("123456789", 3, 2, "xxxxxx")
____________________ ' In ReturnedString is “12xxxxxx56789”
_4 WorksheetFunction.Substitute Method https://msdn.microsoft.com/en-us/vba/excel-vba/articles/worksheetfunction-substitute-method-excel
This works on a string or string variable. It returns the same or modified string
This replaces by substitution of a string part by another string part.
For a single string a part of the string can be replaced by another string. For multiple occurrences ( instances ) of a string part, the instance counting from the left can be specified. ( If this option is omitted then all occurrences are substituted )
Pseudo Like:
ReturnedString = Application.WorksheetFunction.Substitute(InThis_St ring , TheSubStringHere ,
_______________________________________________IsT oBeReplacedByThisString ,
__________________________________________________ _ThisOccurrenceCountingFromTheLeftToBeSubstituted )
The Returned string can have a different length to main This_String:
ReturnedString = Application.WorksheetFunction.Substitute("1A3a5A7a9", "a", "yyyyy", 2):
_______________________ ' In ReturnedString is "1A3a5A7yyyyy9"
_5) Mid Function (used pseudo as method ) / …… Statements
It has sometimes been noticed that “MID function can also be used to replace a section of the string.”
https://www.excelforum.com/word-programming-vba-macros/1175184-vba-word-repeat-character-in-string-a-number-of-times.html#post4591069
https://www.mrexcel.com/forum/excel-questions/329627-curious-use-vba-mid-function.html
Possibly this is one of three “Statements” from earlier Visual basic.
_5b) LSet Mid RSet Statements.
Working on a string variable.
Impotent characteristic is that the string length cannot be changed: The final string has the same length as the original string
_5b)(i) LSet : https://msdn.microsoft.com/en-us/library/aa266163(v=vs.60).aspx
Works on a string variable **
All of the string is overwritten, starting at the left and up to the original length. Any remaining characters are left blank
MeString = “123456789”
LSet MeString = “987”
' Result is in MeString “987xxxxxx”
** Some other possibilities to do with replacing variable types that I have not figured out yet.
_5b)(ii) RSet : https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/rset-statement
Works only on a string variable
All of the string is overwritten, starting at the right and down/ back to the original length. Any remaining characters are left blank
MeString =“123456789”
RSet MeString = “987”
' Result in MeString is "xxxxxx987”
_5b)(iii) Mid statement : https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/mid-statement
Works only on a string variable
A part, specified by position within, of a string is replaced by all or part of a given string
Mid(string, StartPositionInString, LengthFromStartPosition) = GivenString
MeString = “123456789”
Mid( MeString, 3, 2) = "cdef"
' Result in MeString is “12cd56789”
MeString = “123456789”
Mid( MeString, 3) = "cdef"
' Result in MeString is “12cdef789”
Ref
https://www.excelforum.com/word-programming-vba-macros/1175184-vba-word-repeat-character-in-string-a-number-of-times.html#post4590792
https://powerspreadsheets.com/excel-vba-replace-substitute/
http://www.functionx.com/vbaexcel/topics/strings.htm
https://usefulgyaan.wordpress.com/2013/05/10/ltrim-function-vba/
http://www.excelfox.com/forum/showthread.php/994-LTRIM()-Function-VBA-To-Remove-Spaces
Intersting uses:
Robert H. Gascon https://excelribbon.tips.net/T009600_Sorting_Letters_and_Numbers.html
( Misc refs for later use
https://excel.tips.net/T003219_Getting_Rid_of_Alphabetic_Characters.html
https://excel.tips.net/T003303_Wildcards_in_Replace_With_Text.html
http://www.eileenslounge.com/viewtopic.php?f=30&t=33843#p262151
https://www.myonlinetraininghub.com/using-substitute-function-find-count-text )
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxUbeYSvsBH2Gianox4AaABAg.9VYH-07VTyW9gJV5fDAZNe (https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxUbeYSvsBH2Gianox4AaABAg.9VYH-07VTyW9gJV5fDAZNe)
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg (https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg)
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgyhQ73u0C3V4bEPhYB4AaABAg (https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgyhQ73u0C3V4bEPhYB4AaABAg)
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgzIElpI5OFExnUyrk14AaABAg.9fsvd9zwZii9gMUka-NbIZ (https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgzIElpI5OFExnUyrk14AaABAg.9fsvd9zwZii9gMUka-NbIZ)
https://www.youtube.com/watch?v=jdPeMPT98QU (https://www.youtube.com/watch?v=jdPeMPT98QU)
https://www.youtube.com/watch?v=QdwDnUz96W0&lc=Ugx3syV3Bw6bxddVyBx4AaABAg (https://www.youtube.com/watch?v=QdwDnUz96W0&lc=Ugx3syV3Bw6bxddVyBx4AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1 (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)