Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    String Trimming (Office 2000 SR-1, Excel2000)

    I noticed a post for a cell formula for parsing a column with two space-seperated names, but the solution doesn't appear to work for what I am trying to do. In VB for excel, there doesn't seem to be a way to trim (that is, delete) from the left 11 characters from a string, leaving whatever is left as the value needed.

    Here's what I'm doing: programmatically creating a new page with a reference number inputted by the user, with the string "Recipients " in front of the new sheet name. Now, I want to be able to programmically return to the page that spawns this new page. The parent page is just the reference number, so it would be nice to do the reverse of what was done to spawn the Recipient page by deleting the fixed string to get the original sheet name. This is for a rather heavily automated Excel project so all sheet references should be relative.

    The VB to spawn the new page has this pertinent line: <font color=448800>Sheets("Primary Recipients (2)").Name = "Recipients " & PrimaryRecipientsName</font color=448800>. In the VB for returning to the sheet that created this sheet I'd love to just subtract the "Recipients " string from the <font color=blue>Activesheet.Name</font color=blue> so it goes unfailingly to the parent sheet. Is this possible? I can't get it to work.

    I don't want to assume the inputted reference number, string, whathaveyou has no blanks so the solution I referred to earlier is not ideal.

    TIA <img src=/S/thinks.gif border=0 alt=thinks width=15 height=15>

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: String Trimming (Office 2000 SR-1, Excel2000)

    nevermind. got it.

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: String Trimming (Office 2000 SR-1, Excel2000)

    Try ::
    Right(ActiveSheet.Name, Len(ActiveSheet.Name) - 11)


    Andrew C

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: String Trimming (Office 2000 SR-1, Excel2000)

    that's precisely what i did.

    thnkx

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: String Trimming (Office 2000 SR-1, Excel2000)

    This is minor note, but it'd be slightly simpler to use Mid function instead of Right & Len functions. If you omit the optional Length (3rd) argument, Mid function returns all characters from Start position (2nd argument) to end of string. Example: these two user-defined functions will return same result, providing the number of characters specified as 2nd argument does not exceed length of text string being evaluated:

    Public Function TrimText1(ByVal strTxt As String, ByVal intChars As Integer) As String
    ' strTxt = text to be parsed
    ' intChars = number of characters to trim (from left)
    TrimText1 = Mid(strTxt, intChars + 1)

    End Function

    Public Function TrimText2(ByVal strTxt As String, ByVal intChars As Integer) As String

    TrimText2 = Right(strTxt, Len(strTxt) - intChars)

    End Function

    Example of use:

    ? TrimText1("Recipients ABC123",11)
    ABC123
    ? TrimText2("Recipients ABC123",11)
    ABC123

    However, if number of characters exceeds length of string, the second function will result in error, while first will merely return a zero length string. Example:

    ? TrimText1("ABCDEF",7)
    ' Returns zero-length string

    ? Len(TrimText1("ABCDEF",7))
    0

    ? TrimText2("ABCDEF",7)
    ' Results in run-time error 5, Invalid procedure call or argument

    So using Mid function may be simpler and more reliable.

    HTH

Posting Permissions

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