Code to Remove Specific Character at End of String

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a string which is ALWAYS in the format of, for example:

Atlanta. GA; Orlando. FL; Montego Bay. Jamaica; ....and so on.

The string may have only one entry but could have several. A new "entry"
added to the string will always end in a ";"

Using VBA how can I remove the last ";" from the string?
 
Public Function StripTrailingSemiColon(ByVal InputString As String) As
String

If Right$(InputString, 1) = ";" Then
StripTrailingSemiColon = Left$(InputString, Len(InputString) - 1)
Else
StripTrailingSemiColon = InputString
End If

End Function

We could make this more generic and re-usable by passing the character to be
removed as a parameter ...

Public Function StripTrailingChar(ByVal InputString As String, _
ByVal CharToRemove As String) As String

If Right$(InputString, 1) = CharToRemove Then
StripTrailingChar = Left$(InputString, Len(InputString) - 1)
Else
StripTrailingChar = InputString
End If

End Function
 
I tried your code, and it doesn't work for the desired effect.

I debuged the code and it skips past the If statement and goes to the End If.

My code is:

Private Sub clickFinished_Click()

Dim strtripwhere As String
Dim strnosemicolon As String

strtripwhere = Me.AirportItinerary 'works fine to here

If Right$(strtripwhere, 1) = ";" Then
strnosemicolon = Left(strtripwhere, Len(strtripwhere) - 1)
Else
strnosemicolon = strtripwhere
End If

<do other stuff.....afterwards...>

Using debug, the strtripwhere = "Atlanta. GA; Orlando. FL; Atlanta. GA;"
And this is shown, but it passes right by the If statement. I tried the
Left$ statement by itself, as mentioned in another user's reply...same effect.

Any ideas?
 
I missed typed the strnosemicolon = Left$(strtripwhere, Len(strtripwhere)-1)

(forgot the $ in the reply post...but it's in my code.)
 
Aaargh!

Disregard. I finally figured out that my string always had a space added to
the end. I RTrim the string and the code works fine.

Thanks.
 
Back
Top