How do I strip punctuation from end of text?

Hi All. I have a macro that should strip the punctuation from the end of text in a range. When I run the macro it eliminates the text as well as the punctuation. Can someone please look at this code and tell me what I;m doing wrong? Thank you so much. I am including a spreadsheet with sample data. The code is below.

Sub StripPunctuation()
'/======================================================================================================
'/ Procedure : StripPunctuation
'/ Purpose   : This procedure strips the punctuation from text in the range
'/======================================================================================================

Dim wbTest          As Workbook
Dim wsTest          As Worksheet
Dim rngClean        As Range
Dim nRowOffset      As Integer
Dim vs              As Variant

nRowOffset = 1

Set wbTest = Workbooks("MyCleaningText")
Set wsTest = wbTest.Worksheets("CleanTest")
Set rngClean = wsTest.Range("B1")

    Do Until rngClean.Offset(nRowOffset).Text = ""
        With rngClean.Offset(nRowOffset)
            For Each vs In Array(".", "?", "!", "")
            .Replace What:=vs, _
            Replacement:="", _
            lookat:=xlPart, _
            searchorder:=xlByRows, _
            searchformat:=False, _
            ReplaceFormat:=False
            
            Next vs
        End With
        
       Application.Wait Now() + #12:00:01 AM#
        
    nRowOffset = nRowOffset + 1

    Loop

Set wbTest = Nothing
Set wsTest = Nothing
Set rngClean = Nothing

End Sub

A question mark replaces any text. To replace an actual question mark, precede it with a tilde, viz: "~?". Even that is not going to remove punctuation from the end of your text though, but from anywhere in the text.

I have attached your test workbook with some modifications to Sub StripPunctuation that will remove punctuation only from the end of your text as you had asked. The code you have written will remove punctuation from anywhere in the text (which may be your intention but not what you asked).

There are two methods. Obviously, you only need to code one of them. Method 1 removes anything that is not alphanumeric, so it will get all punctuation. Method 2 removes only the specific punctuation that you set. That also answers your question about whether it will work on other punctuation marks as well.

Sorry for my shorthand. I've uploaded the same code using longer variable names.

Basically: s (now sValue) is a string to hold the value of the cell for processing; i (now iIndex) is an index into the value string to extract characters one by one from the right hand side; and c (now sChar) is the character that has been extracted from sValue according to the value of iIndex. VBA does not have a single Character type so we are forced to use a String type and limit it to one character at a time.

The constant serves two purposes. Firstly, if it is set to an empty string then the code does not search for specific punctuation marks but assumes that anything which is neither alphabetic nor numeric will need to be removed. Note that it takes the upper case of the character so that if it is alphabetic then it only needs to check the range "A" to "Z"; it will never be "a" to "z".

The second purpose of the constant is to hold particular punctuation marks if those characters alone need to be removed. It takes one character at a time from the right hand side of the value and checks that character's position within the constant. If a value greater than zero is returned then it knows the character is among those which are disallowed. However, if a zero is returned then the character was not found in the constant and therefore must be allowed; hence the strip back has ended.

When it exits the For loop, whatever index it has counted down to is the length of the allowed string, so it takes everything up to that point as the new value.

Anyhow, take a look at the new upload. I hope it is clearer now.

Based on the example you posted:

 
sub snb()
 with sheets(1).columns(2)
  .replace "." "", xlpart
  .replace "?","", xlpart
  .replace "!","",xlpart
 end with
end sub

or

Sub snb2()
  [B1:B200] = [if(B1:B200="","",if(iserror(search(right(B1:B200,1),".!?")),B1:B200,left(B1:B200,len(B1:B200)-1)))]
End Sub

It's impossible that all text will be removed when using:

 
sub snb()
 with sheets(1).columns(2)
  .replace "." ,"", xlpart
  .replace "?","", xlpart
  .replace "!","",xlpart
 end with
end sub

Some code is easier to adapt to other uses than other code.