Remove the right most 3 characters from a string

[Jonathan Brown]

I'm using a loop to build a string of characters but I'm getting extra
characters at the end that I don't want. Is there a function like trimend or
regexp where I can take the string and remove the right most 3 characters?

Here's an example:

arraystring = "someword", "someword2", "someword3", "someword4", "

I'd like it to change my arraystring to show just:

"someword", "someword2", "someword3", "someword4"

This doesn't seem to work for me:
ArrayString = Trim(ArrayString, Right(Len(ArrayString) - 3))

I'm getting an error under the right() function.

 

[Tom Hutchins]

You are getting an error on the RIGHT function because you are not passing it
the first argument it needs, which is the string to examine. However, your
approach wouldn't work anyway, because the TRIM function only removes leading
& trailing spaces from a string. Try:

ArrayString = Left(ArrayString, Len(ArrayString) - 3)

Hope this helps,

Hutch

 

[Jonathan Brown]

I'm getting the same error on the Left() function now as I was with the
Right() function. It says, "'Public Property Left() as Integer' has no
parameters and its return type cannot be indexed."

I found this website:
http://www.entisoft.com/estools/stirngmanipulations.html and it has an
example using a function called StripRightNCharacters() that looks like it'll
do what I want but it doesn't appear to be valid or available function that I
can use.

any other ideas?

 

[Tom Hutchins]

That error sounds like VBA doesn't know what the Left() or Right() functions
are. In the Visual Basic Editor, if you select Tools >> References, are there
checkmarks by "Visual Basic for Applications" and "Microsoft Excel 11.0
Object Library" (you might have a different version of the second one)?

Hutch

 

[Jonathan Brown]

well...to be completely honest, which I know I should have been from the
beginning, I'm not using VBA, per se. I'm using Visual Studio Tools for
Office, so it's really just VB...for the most part. I don't have a
references option under the tools menu in Visual Studio. But I have imported
the excel.interop namespace to my project. This also means that this post
should go in the MSDN VSTO forum. But I would think that the Left() or
Right() functions would exist in either VBA, VSTO, or just plain old VB. But
I could easily be mistaken. I'll go ahead and repost this in the Visual
Studio Tools for Office forum and see if they can help me there. I
appreciate your help nonetheless.

 

[Ron Rosenfeld]

I'm using a loop to build a string of characters but I'm getting extra
characters at the end that I don't want. Is there a function like trimend or
regexp where I can take the string and remove the right most 3 characters?

Here's an example:

arraystring = "someword", "someword2", "someword3", "someword4", "

I'd like it to change my arraystring to show just:

"someword", "someword2", "someword3", "someword4"

This doesn't seem to work for me:
ArrayString = Trim(ArrayString, Right(Len(ArrayString) - 3))

I'm getting an error under the right() function.

arraystring = "someword", "someword2", "someword3", "someword4", "

is not a valid VBA statement. You will get an error message if you enter that
line.

To have arraystring equal the value you have above, your statement should be:

arraystring = """someword"", ""someword2"", ""someword3"", ""someword4"", "

You can then strip off everything from the last comma by using:

Left(arraystring, InStrRev(arraystring, ",") - 1)

--ron

 

[Ron Rosenfeld]

arraystring = "someword", "someword2", "someword3", "someword4", "

is not a valid VBA statement. You will get an error message if you enter that
line.

To have arraystring equal the value you have above, your statement should be:

arraystring = """someword"", ""someword2"", ""someword3"", ""someword4"", "

You can then strip off everything from the last comma by using:

Left(arraystring, InStrRev(arraystring, ",") - 1)

--ron

Actually, it should be:

arraystring = """someword"", ""someword2"", ""someword3"", ""someword4"", """


but the result of the function above will be the same, as can be seen in this
demo program:

=============================
Option Explicit
Sub foo()
Dim arraystring
arraystring = """someword"", ""someword2"", ""someword3"", ""someword4"", """
Debug.Print arraystring
Debug.Print Left(arraystring, InStrRev(arraystring, ",") - 1)
End Sub
================================
--ron

 

[Jonathan Brown]

What about the problem I'm having with the Left() function? Do i want to
reassign that value to a new variable? like:

newarraystring = Left(arraystring, InStrRev(arraystring, ",")-1)

anyway, I haven't had a chance to give your example a test yet since I
didn't work today, but I'll give it a try and see how it goes.

 

[Ron Rosenfeld]

What about the problem I'm having with the Left() function? Do i want to
reassign that value to a new variable? like:

newarraystring = Left(arraystring, InStrRev(arraystring, ",")-1)

anyway, I haven't had a chance to give your example a test yet since I
didn't work today, but I'll give it a try and see how it goes.

You can assign the result to either the same variable (arraystring) or a
different one.

So far as the problem you are having with the Left() function, my understanding
from what you wrote is that you were using Visual Studio Tools for Office when
you received that error. I have no experience with that program, so can't
really comment.

What I wrote should work in VBA.
--ron

 

[Jonathan Brown]

Okay, Thanks, Ron, I appreciate it.

You can assign the result to either the same variable (arraystring) or a
different one.

So far as the problem you are having with the Left() function, my understanding
from what you wrote is that you were using Visual Studio Tools for Office when
you received that error. I have no experience with that program, so can't
really comment.

What I wrote should work in VBA.
--ron

 

[Dom Kershaw]

I had to the same thing as I was adding 001 002 etc.. to duplicate
filenames in VB.NET
If the file existed I added 001, if that existed I added 002, but
removing the 001 etc.. sometimes removed char from the middle of the
name or the first instance, so I deleted the last three chars as
below...

I used :

templength = filename.Length
filename = filename.Substring(0, templength - 3)