Results 1 to 1 of 1

Thread: Built in VBA methods and functions to alter the contents of existing character strings

  1. #1
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    Built in VBA methods and functions to alter the contents of existing character strings

    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...place-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 ,
    __________________________CharacterNumberfromLeftOfStringToLookInAndFromWhereToReturn ,
    _____________________________________NumberOfOccurancesLookingFromTheLeftToReplace ,
    __________________________________________________ ___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-pro...ml#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...e-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
    1
    A B
    2
    C D
    3
    a G
    Worksheet: Tabelle1

    After
    Rng.Replace "a", "x"
    Row\Col
    A
    B
    1
    x B
    2
    C D
    3
    x G
    Worksheet: Tabelle1

    _3 WorksheetFunction.Replace Method https://msdn.microsoft.com/de-de/vba...e-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_String , AtThisPositionStartingFromLeft ,
    ____________________________________________ ForThisNumberOfCharacters ,
    ________________________________________________ThisSubStringSubstituteInThatPostitiuon )

    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...e-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_String , TheSubStringHere ,
    _______________________________________________IsToBeReplacedByThisString ,
    __________________________________________________ _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-prog...ml#post4591069
    https://www.mrexcel.com/forum/excel-...-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/lib...(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...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/dot.../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-prog...ml#post4590792
    https://powerspreadsheets.com/excel-...ce-substitute/
    http://www.functionx.com/vbaexcel/topics/strings.htm
    https://usefulgyaan.wordpress.com/20...-function-vba/
    http://www.excelfox.com/forum/showth...-Remove-Spaces


    Intersting uses:
    Robert H. Gascon https://excelribbon.tips.net/T009600...d_Numbers.html



    ( Misc refs for later use
    https://excel.tips.net/T003219_Getti...haracters.html
    https://excel.tips.net/T003303_Wildc...With_Text.html
    http://www.eileenslounge.com/viewtop...=33843#p262151
    https://www.myonlinetraininghub.com/...ind-count-text )



    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=UgxLtKj969oiIu7zNb94AaABAg
    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=jdPeMPT98QU
    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/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 11-20-2023 at 04:01 PM. Reason: adding Refs
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

Similar Threads

  1. Replies: 6
    Last Post: 01-18-2016, 08:49 PM
  2. Replies: 7
    Last Post: 04-04-2014, 03:16 PM
  3. Replies: 1
    Last Post: 12-13-2013, 05:45 AM
  4. Q: VBA - using a range of strings as an argument
    By mwdking in forum Excel Help
    Replies: 1
    Last Post: 11-11-2013, 10:45 PM
  5. Replies: 9
    Last Post: 09-09-2011, 02:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •