Remove comma from the end of string

[keith05281967]

Greetings VBA wizards,

I'm trying to remove a comma if found at the end of a string. I think i'm really close but I don't have the syntax exactly right. Can you offer some advice?

So an example would be - HOUSTON, to HOUSTON

pretty simple right?

Code:

For i = 2 To lastrow
s = Cells(i, ColumnNumber).Value
s = Trim(s)
If (Right(s, 1)) = "," Then
left(s,len(s)-1),s) <---this is the offending line

End If
Next i


thanks, excel 07. looking for a vba solution

 

[Pauljj]

How about

s = Left(s, Len(s) - 1) ?

 

[VoG]

Try

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

 

[mikerickson]

If (Right(s, 1)) = "," Then
    s = Left(s, Len(s)-1)
End If

or

s = Left(s, Len(s) + (Right(s, 1)= ","))

 

[keith05281967]

From a syntax perspective I believe you are both right. thank you. The code runs thru the code w/out error but it isn't removing the commas. I ran the code line by line and I can't see why it isn't doing it. I've copied in the code below. Wondering if you see what's missing:


Set Found4 = Cells.Find(What:="City", after:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)

If Found4 Is Nothing Then

MsgBox "City field not found", vbExclamation

Else
Found4.Select
ColumnNumber = ActiveCell.Column
Application.GoTo ActiveCell, Scroll:=True
ActiveCell.ColumnWidth = 30
lastrow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

For i = 2 To lastrow
s = Cells(i, ColumnNumber).Value
s = Trim(s)
If (Right(s, 1)) = "," Then
s = Left(s, Len(s) - 1)

End If
Next i

End If

thanks,
Keith

 

[VoG]

Try

For i = 2 To lastrow
    s = Cells(i, ColumnNumber).Value
    s = Trim(s)
    If Right(s, 1) = "," Then
        s = Left(s, Len(s) - 1)
        Cells(i, ColumnNumber).Value = s
End If
Next i

 

[keith05281967]

That was it. Thank you so much.

cheers,
Keith