Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    characters in a string variable (2003)

    I need to access and replace an individual character held in a string variable.

    The statement goes something like

    For each character in the string 1 to string length
    If the character = '/' then
    Replace it with '_'
    end if
    next character

    Can somebody please help me with the relevant syntax.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: characters in a string variable (2003)

    Just use Substitute function (as a formula in Excel)
    <pre>=substitute(a1, "/","_")</pre>


    assuming A1 contains the striing to convert

    or in VB
    <pre>dim sString as String

    sString = "This is a Test / it will be different /"
    sString = application.worksheetfunction.substitute(sString, "/", "_")</pre>


    Where sString holds the text to convert (and afterwards the converted text)

    After the sub line sString will contain:
    "This is a Test _ it will be different _"

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: characters in a string variable (2003)

    Thanks, this works, but for information, can you als advise if the string[numeric value] functions which are available within C++ are also available within VBA?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: characters in a string variable (2003)

    I fear that not all of us are familiar with C++ (I don't know anything about it). Could you indicate what functionality you are interested in?

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: characters in a string variable (2003)

    As far as I recall, it worked as follows:

    chr ACharacter;
    string AString;

    ACharacter = AString[10]; 'made Acharacter equal to the 10th character in the string, and you always had to be careful that the integer was not greater than the string length, otherwise you could be accessing all sorts of data.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: characters in a string variable (2003)

    VB / VBA has the Left, Mid and Right functions. Type one of these in the Visual Basic Editor and press F1 to get help. These functions are much more forgiving than C++ functions.

    Examples:

    Mid("Woody", 4, 1) will result in the 4th character of "Woody", i.e. "d"
    Mid("Woody", 4, 6) will result in "dy" - since there are only 2 characters available starting at position 4, instead of the requested 6, only 2 are returned.
    Mid("Woody", 10, 1) will result in an empty string "", since there is no character at position 10.

    Another useful function is InStr - it searches for text in a string and returns the first position it is found, or 0 if not found:

    InStr("Woody's Dodo", "od") returns 3, since the first occurrence of "od" is found in "Woody's Dodo" starting at the 3rd character.
    InStr("Woody's Dodo", "dr") returns 0, since "dr" is not found.

    InStrRev is similar, but searches from the end of the string backwards.

Posting Permissions

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