Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove the right most 3 characters from a string
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove the right most 3 characters from a string
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" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove the right most 3 characters from a string
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/stir...pulations.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" wrote: 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" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove the right most 3 characters from a string
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" wrote: 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/stir...pulations.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" wrote: 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" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove the right most 3 characters from a string
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. "Tom Hutchins" wrote: 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" wrote: 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/stir...pulations.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" wrote: 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" wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove the right most 3 characters from a string
On Wed, 1 Oct 2008 10:15:01 -0700, Jonathan Brown
wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove the right most 3 characters from a string
On Thu, 02 Oct 2008 07:13:37 -0400, Ron Rosenfeld
wrote: On Wed, 1 Oct 2008 10:15:01 -0700, Jonathan Brown wrote: 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 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove the right most 3 characters from a string
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" wrote: On Thu, 02 Oct 2008 07:13:37 -0400, Ron Rosenfeld wrote: On Wed, 1 Oct 2008 10:15:01 -0700, Jonathan Brown wrote: 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 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove the right most 3 characters from a string
On Thu, 2 Oct 2008 20:28:11 -0700, Jonathan Brown
wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove the right most 3 characters from a string
Okay, Thanks, Ron, I appreciate it.
"Ron Rosenfeld" wrote: On Thu, 2 Oct 2008 20:28:11 -0700, Jonathan Brown wrote: 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove the right most 3 characters from a string
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) *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Remove characters from string | Excel Programming | |||
Remove characters from string | Excel Programming | |||
Remove characters from string | Excel Programming | |||
Remove characters from string | Excel Programming | |||
Remove all characters following the first character in a string | Excel Discussion (Misc queries) |