Remove last character from string (VBA)

[strorg]

I am trying to remove the last character of a string variable. I know that the character will be "+" so I figured something like this would work:

MyLen = Len(MyStr)
MyStr = Replace(MyStr, "+", "", MyLen)

But the return value is an empty string. So I tried:

MyLen = Len(MyStr)
MyStr = Replace(MyStr, "+", "", MyLen - 1)

and the return value is "2" which is the second-to-last last character in the original string.

I am missing something here. (Obviously)

Any advice on my approach or on an alternate method?

Regards...Tom

 

[Haluk]

Hi;

You may try this;

MyStr = Mid(MyStr, 1, Len(MyStr) - 1)

Or this;

MyStr = Replace(MyStr, "+", "")
This message was edited by Raider on 2002-11-10 10:06

 

[Legacy 11779]

Or :-

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

 

[Jack in the UK]

Hi Being a lover of UDF this mighyt help you a bit, can edit as well

Dedicate to Tom who died aged just 4

This will make Jacks formul in the userdefind section for you to use

Public Function Jack_Delete_Whatever_U_Like(JacksRange As String)
Dim i As Integer
Const Jacks_Remove_What = "+" 'Add or remove "WHATEVER"
For i = 1 To Len(Jacks_Remove_What)
JacksRange = Application.WorksheetFunction.Substitute(JacksRange, _
Mid(Jacks_Remove_What, i, 1), "")
Next i
Jack_Delete_Whatever_U_Like = JacksRange
End Function

 

[strorg]

Thanks for all the tips. I'll store them in my toolbox.

Regards...Tom

 

[Legacy 98055]

For a bit more flexibility in your toolbox.
A simple function to return the first half of a string or the second half of the same. Pass the string, the "cutoff" character and a 1 or 2, and a true or false to keep the cutoff character in your results or not.
<pre>
Function cs(str As String, char As String, bf As Byte, coff As Boolean) As String
If bf = 1 Then
If coff Then
cs = Left(str, InStr(str, char))
Else
cs = Left(str, InStr(str, char) - 1)
End If
Else
If coff Then
cs = Right(str, Len(str) - InStrRev(str, char) + 1)
Else
cs = Right(str, Len(str) - InStrRev(str, char))
End If
End If
End Function

Sub SomeSub()
Dim A_String As String
A_String = cs("123456+78910", "+", 1, False)
'A_String = "123456"
A_String = cs("12+78910", "+", 2, False)
'A_String = "78910"
End Sub

</pre>
tom