String Trimming (Office 2000 SR-1, Excel2000)

Topic

New Reply

I noticed a post for a cell formula for parsing a column with two space-seperated names, but the solution doesn’t appear to work for what I am trying to do. In VB for excel, there doesn’t seem to be a way to trim (that is, delete) from the left 11 characters from a string, leaving whatever is left as the value needed.

Here’s what I’m doing: programmatically creating a new page with a reference number inputted by the user, with the string “Recipients ” in front of the new sheet name. Now, I want to be able to programmically return to the page that spawns this new page. The parent page is just the reference number, so it would be nice to do the reverse of what was done to spawn the Recipient page by deleting the fixed string to get the original sheet name. This is for a rather heavily automated Excel project so all sheet references should be relative.

The VB to spawn the new page has this pertinent line: Sheets(“Primary Recipients (2)”).Name = “Recipients ” & PrimaryRecipientsName. In the VB for returning to the sheet that created this sheet I’d love to just subtract the “Recipients ” string from the Activesheet.Name so it goes unfailingly to the parent sheet. Is this possible? I can’t get it to work.

I don’t want to assume the inputted reference number, string, whathaveyou has no blanks so the solution I referred to earlier is not ideal.

TIA

Reply | Quote

Replies

nevermind. got it.

Reply | Quote

Try ::
Right(ActiveSheet.Name, Len(ActiveSheet.Name) – 11)

Andrew C

Reply | Quote

that’s precisely what i did.

thnkx

Reply | Quote

This is minor note, but it’d be slightly simpler to use Mid function instead of Right & Len functions. If you omit the optional Length (3rd) argument, Mid function returns all characters from Start position (2nd argument) to end of string. Example: these two user-defined functions will return same result, providing the number of characters specified as 2nd argument does not exceed length of text string being evaluated:

Public Function TrimText1(ByVal strTxt As String, ByVal intChars As Integer) As String
‘ strTxt = text to be parsed
‘ intChars = number of characters to trim (from left)
TrimText1 = Mid(strTxt, intChars + 1)

End Function

Public Function TrimText2(ByVal strTxt As String, ByVal intChars As Integer) As String

TrimText2 = Right(strTxt, Len(strTxt) – intChars)

End Function

Example of use:

? TrimText1(“Recipients ABC123”,11)
ABC123
? TrimText2(“Recipients ABC123”,11)
ABC123

However, if number of characters exceeds length of string, the second function will result in error, while first will merely return a zero length string. Example:

? TrimText1(“ABCDEF”,7)
‘ Returns zero-length string

? Len(TrimText1(“ABCDEF”,7))
0

? TrimText2(“ABCDEF”,7)
‘ Results in run-time error 5, Invalid procedure call or argument

So using Mid function may be simpler and more reliable.

HTH

Reply | Quote