VBA: Remove last character from a string

strorg

Board Regular
Joined
Mar 27, 2002
Messages
112
How do I remove the last character from a string in VBA?

Thanks...Tom
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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
 
Upvote 0
Here's your answer...

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Test()
    <SPAN style="color:#00007F">Dim</SPAN> NEWSTRING <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    
    <SPAN style="color:#00007F">Const</SPAN> OLDSTRING <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "TommyGun"
    
    NEWSTRING = Left(OLDSTRING, Len(OLDSTRING) - 1)
    
    MsgBox NEWSTRING
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

And here's my question...

Why?
 
Upvote 0
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.
 
Upvote 0
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. :devilish:
 
Upvote 0
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
 
Upvote 0
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 :wink:
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top