Removing Specific Characters From End Of Cells

[indi visual]

I am having trouble removing fragments left behind from a previous code.


I am familiar with Len, Trim, and the basic search and replace of course.


The problem is I only want them removed from the end, and also

some of these characters repeat consecutively.


This seems like a simple enough issue, but nothing I've tried so far has successfully removed all consecutive occurrences from the end (it might knock out a few chunks but not all of them).


I could use a loop to keep going and knocking out chunks at a time, but the problem with that is I don't know how to exit the loop once started, and also I would prefer to avoid loop if at all possible.


Any help on this is extremely appreciated.


The 7 Specific Fragments are (which could be consecutive in any order):

-

(

[

{

<

_

}

 

[Luke M]

The function you need is SUBSTITUTE. Add/Remove as many characters to the string "BadCharacters" as you desired. I'm assuming you know how to loop through different cells, if needed.

Sub xClean()

Dim xWord As String

Dim BadCharacters As String

Dim xKill As String


xWord = ActiveCell.Value

BadCharacters = "-([{<_)"


For i = 1 To Len(BadCharacters)

xKill = Mid(BadCharacters, i, 1)

xWord = WorksheetFunction.Substitute(xWord, xKill, "")

Next i


ActiveCell.Value = xWord

End Sub

 

[indi visual]

I figured out how to loop through each cell. But not quite sure how to only remove from the end (I placed some extra spaces at the front to see if this would kill those too and it did unfortunately). I'm not close you got me all the way there, just a need a little more help on how to restructure this to clip off the ends only.


Sub xClean()

Dim xWord As String

Dim BadCharacters As String

Dim xKill As String


For Each Cell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)


xWord = Cell.Value

BadCharacters = "-([{<_)"


For i = 1 To Len(BadCharacters)

xKill = Mid(BadCharacters, i, 1)

xWord = WorksheetFunction.Substitute(xWord, xKill, "")

Next i


Cell.Value = xWord


Next Cell

End Sub

 

[indi visual]

Resolved.


Thanks for the help as always Luke.