Page 1 of 2 12 LastLast
Results 1 to 10 of 17

VBA: Remove last character from a string

This is a discussion on VBA: Remove last character from a string within the Excel Questions forums, part of the Question Forums category; How do I remove the last character from a string in VBA ? Thanks...Tom...

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    112

    Default VBA: Remove last character from a string

    How do I remove the last character from a string in VBA?

    Thanks...Tom

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Posts
    112

    Default Re: VBA: Remove last character from a string

    Doh! How about:

    myString = Left(myString, Len(myString) - 1)

  3. #3
    Legend just_jon's Avatar
    Join Date
    Sep 2002
    Location
    Alabama/State of Disarray
    Posts
    10,473

    Default Re: VBA: Remove last character from a string

    Try --

    strx = Left([a1], Len([a1]) - 1)

    where cell A1 is the target, or --

    strx = Left(stry, Len(stry) - 1)

    where stry is the variable target
    just_jon
    Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

  4. #4
    MrExcel MVP TommyGun's Avatar
    Join Date
    Dec 2002
    Location
    Clear Lake, TX
    Posts
    4,202

    Default Re: VBA: Remove last character from a string

    Here's your answer...

    Sub Test()
    ****Dim NEWSTRING As String
    ****
    ****Const OLDSTRING As String = "TommyGun"
    ****
    ****NEWSTRING = Left(OLDSTRING, Len(OLDSTRING) - 1)
    ****
    ****MsgBox NEWSTRING
    End Sub


    And here's my question...

    Why?

  5. #5
    Legend just_jon's Avatar
    Join Date
    Sep 2002
    Location
    Alabama/State of Disarray
    Posts
    10,473

    Default Re: VBA: Remove last character from a string

    Well, I wondered, too, TG -- maybe deleting a traiing ',' or taking off a numeric from a series {joe1, joe2...} -- one 'o life's little mysteries.
    just_jon
    Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

  6. #6
    MrExcel MVP TommyGun's Avatar
    Join Date
    Dec 2002
    Location
    Clear Lake, TX
    Posts
    4,202

    Default Re: VBA: Remove last character from a string

    Yeah, but depending upon what the OP is trying to accomplish, there may be a better way than using VBA. Just trying to get all information or as Joe Friday would've said "Just the facts", so that the OP gets the best solution.

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Posts
    112

    Default

    I needed to clip off that last pesky "+" when I finish the Do Loop. Here's me code for the first foumula...not very graceful but effective. It beats having to manually edit 20 formulas.

    Sub CreateNewFormulas()
    'This routine creates the new formulas and is used when a controller is added to or deleted from the workbook.

    'Range("Incl" & i) is the reference point for the controller.

    Dim NC As Integer, i As Integer, myString As String

    NC = 65 'The number of controllers
    i = 1
    Do Until i > NC
    myString = myString & Range("Incl" & i).Offset(1, 0).Address & "+"
    i = i + 1
    Loop
    Range("UtilFactor").Formula = "=" & Left(myString, Len(myString) - 1)

    End Sub

    Thanks as always for the prompt and effective assistance!!

    Cheers...Tom

  8. #8
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330

    Default Re: VBA: Remove last character from a string

    You could change your loop to:

    Code:
    myString = Range("Incl" & 1).Offset(1, 0).Address
    i = 2
    Do Until i > NC
    myString = myString & "+" & Range("Incl" & i).Offset(1, 0).Address
    i = i + 1
    Loop
    Range("UtilFactor").Formula = "=" & myString
    Just my 2 cents worth
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Posts
    112

    Default Re: VBA: Remove last character from a string

    Much cleaner code...don't mind if I do!

  10. #10

    Join Date
    Sep 2003
    Posts
    994

    Default Re: VBA: Remove last character from a string

    Check whether this works (untested) :-

    Code:
    Sub CreateNewFormulas()
    Dim NC As Integer, i As Integer
    NC = 65
    i = 2
    Range("UtilFactor").Formula = "=sum($" & Incl & "$" & i & ":$" & Incl & NC + i -1 & ")"
    End Sub

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com